Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск количества яблок во всех листах - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск количества яблок во всех листах (Формулы/Formulas)
Поиск количества яблок во всех листах
ComiC Дата: Вторник, 10.10.2017, 01:56 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Имеем около 50 листов.
В некоторых листах столбца А содержатся фрукты, а в соседнем столбце В - количество.

Нужно по всем листам посчитать количество "яблок", "груш" и "т.д."
К сообщению приложен файл: 4373989.xlsx(12Kb)
 
Ответить
СообщениеИмеем около 50 листов.
В некоторых листах столбца А содержатся фрукты, а в соседнем столбце В - количество.

Нужно по всем листам посчитать количество "яблок", "груш" и "т.д."

Автор - ComiC
Дата добавления - 10.10.2017 в 01:56
Shurf Дата: Вторник, 10.10.2017, 02:36 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
ComiC, Здравствуйте, если расположение данных на всех листах одинаково, можно макросом.

[vba]
Код
Sub test()
Dim wSh As Worksheet, KolVo As Long, cl As Object
Application.ScreenUpdating = False
On Error Resume Next
For Each cl In Sheets("Итого").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each wSh In Worksheets
        If wSh.Name <> "Итого" Then
            KolVo = KolVo + wSh.[A1].CurrentRegion.Find(What:=cl, LookAt:=xlWhole).Offset(, 1)
        End If
    Next
cl.Offset(, 1) = KolVo
KolVo = 0
Next
Application.ScreenUpdating = True
End Sub
[/vba]


Сообщение отредактировал Shurf - Вторник, 10.10.2017, 02:38
 
Ответить
СообщениеComiC, Здравствуйте, если расположение данных на всех листах одинаково, можно макросом.

[vba]
Код
Sub test()
Dim wSh As Worksheet, KolVo As Long, cl As Object
Application.ScreenUpdating = False
On Error Resume Next
For Each cl In Sheets("Итого").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each wSh In Worksheets
        If wSh.Name <> "Итого" Then
            KolVo = KolVo + wSh.[A1].CurrentRegion.Find(What:=cl, LookAt:=xlWhole).Offset(, 1)
        End If
    Next
cl.Offset(, 1) = KolVo
KolVo = 0
Next
Application.ScreenUpdating = True
End Sub
[/vba]

Автор - Shurf
Дата добавления - 10.10.2017 в 02:36
Che79 Дата: Вторник, 10.10.2017, 03:31 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 672
Репутация: 114 ±
Замечаний: 0% ±

Excel 2007, 2013
ComiC, и Вам доброго времени суток. Вариантов решения, наверное, немало. Как бы сделал я в четвертом часу ночи (мск)...
- Выделил бы все листы книги (включая лист "Итого") и вставил бы во все листы единый список всех фруктов
Например, такой :D

- Заполнил бы количество по каждому из листов, кроме листа "Итого". Предположим, этим занимаются разные люди, каждый заполняет свой лист, согласно фамилии. Хотелось бы верить, что не вручную :D
- Перешел бы на лист "Итого" и в ячейку B2 формулу
Код
=СУММ('*'!B2)
Формула взята отсюда. В триллионный раз Спасибо Александру (_Boroda_)
Далее формула сама преобразуется в нашем случае в
Код
=СУММ('Иванов:ЕЩЕ 30 ЛИСТОВ'!B2)

- "Даблкличим" по чёрному крестику в ячейке B2
Всё, что получилось в приложенном файле.
Но, не сомневаюсь, что Вам предложат более практичные, быстрые и изящные варианты.
К сообщению приложен файл: 4373989_1.xlsx(40Kb)


Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Вторник, 10.10.2017, 03:37
 
Ответить
СообщениеComiC, и Вам доброго времени суток. Вариантов решения, наверное, немало. Как бы сделал я в четвертом часу ночи (мск)...
- Выделил бы все листы книги (включая лист "Итого") и вставил бы во все листы единый список всех фруктов
Например, такой :D

- Заполнил бы количество по каждому из листов, кроме листа "Итого". Предположим, этим занимаются разные люди, каждый заполняет свой лист, согласно фамилии. Хотелось бы верить, что не вручную :D
- Перешел бы на лист "Итого" и в ячейку B2 формулу
Код
=СУММ('*'!B2)
Формула взята отсюда. В триллионный раз Спасибо Александру (_Boroda_)
Далее формула сама преобразуется в нашем случае в
Код
=СУММ('Иванов:ЕЩЕ 30 ЛИСТОВ'!B2)

- "Даблкличим" по чёрному крестику в ячейке B2
Всё, что получилось в приложенном файле.
Но, не сомневаюсь, что Вам предложат более практичные, быстрые и изящные варианты.

Автор - Che79
Дата добавления - 10.10.2017 в 03:31
ComiC Дата: Вторник, 10.10.2017, 09:35 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте, если расположение данных на всех листах одинаково, можно макросом.

На оборот, фрукты в листах на 90% не будут повторяться. Но в одном листе один и тот же "фрукт" будет повторяться несколько раз точно.
 
Ответить
Сообщение
Здравствуйте, если расположение данных на всех листах одинаково, можно макросом.

На оборот, фрукты в листах на 90% не будут повторяться. Но в одном листе один и тот же "фрукт" будет повторяться несколько раз точно.

Автор - ComiC
Дата добавления - 10.10.2017 в 09:35
_Boroda_ Дата: Вторник, 10.10.2017, 10:04 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11852
Репутация: 4911 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А где это видно в Вашем примере?
Создайте нормальный пример. Как описано в п.3 Правил форума


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА где это видно в Вашем примере?
Создайте нормальный пример. Как описано в п.3 Правил форума

Автор - _Boroda_
Дата добавления - 10.10.2017 в 10:04
Shurf Дата: Вторник, 10.10.2017, 10:38 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
ComiC, Да если нет на листе этого фрукта то ничего страшного, а вот если их много одинаковых на листе это уже другая история.
Тогда такой вариант
[vba]
Код
Sub test()
Dim wSh As Worksheet, KolVo As Long, cl As Object
Application.ScreenUpdating = False

On Error Resume Next
For Each cl In Sheets("Итого").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each wSh In Worksheets
        If wSh.Name <> "Итого" Then
            lrws = wSh.Cells(Rows.Count, 1).End(xlUp).Row
            SummwSh = WorksheetFunction.SumIf(wSh.Range("A1:A" & lrws), cl, wSh.Range("B1:B" & lrws))
            KolVo = KolVo + SummwSh
        End If
    Next
cl.Offset(, 1) = KolVo
KolVo = 0
Next
Application.ScreenUpdating = True
End Sub
[/vba]


Сообщение отредактировал Shurf - Среда, 11.10.2017, 02:18
 
Ответить
СообщениеComiC, Да если нет на листе этого фрукта то ничего страшного, а вот если их много одинаковых на листе это уже другая история.
Тогда такой вариант
[vba]
Код
Sub test()
Dim wSh As Worksheet, KolVo As Long, cl As Object
Application.ScreenUpdating = False

On Error Resume Next
For Each cl In Sheets("Итого").Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For Each wSh In Worksheets
        If wSh.Name <> "Итого" Then
            lrws = wSh.Cells(Rows.Count, 1).End(xlUp).Row
            SummwSh = WorksheetFunction.SumIf(wSh.Range("A1:A" & lrws), cl, wSh.Range("B1:B" & lrws))
            KolVo = KolVo + SummwSh
        End If
    Next
cl.Offset(, 1) = KolVo
KolVo = 0
Next
Application.ScreenUpdating = True
End Sub
[/vba]

Автор - Shurf
Дата добавления - 10.10.2017 в 10:38
_Boroda_ Дата: Вторник, 10.10.2017, 11:20 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11852
Репутация: 4911 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
В диспетчер имен (Контрл F3) пишете имя "Листы" с формулой
Код
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)

Затем на листе Итого переносите столбцы для сбора на один (или несколько) столбец вправо - получаем в столбце В фрукты, а в столбце С Количество
В С2 формулу
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&Листы&"'!A:A");B2;ДВССЫЛ("'"&Листы&"'!B:B")))
и тянем вниз
К сообщению приложен файл: 4373989_1.xlsm(13Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВ диспетчер имен (Контрл F3) пишете имя "Листы" с формулой
Код
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)

Затем на листе Итого переносите столбцы для сбора на один (или несколько) столбец вправо - получаем в столбце В фрукты, а в столбце С Количество
В С2 формулу
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ("'"&Листы&"'!A:A");B2;ДВССЫЛ("'"&Листы&"'!B:B")))
и тянем вниз

Автор - _Boroda_
Дата добавления - 10.10.2017 в 11:20
Vladimir116 Дата: Вторник, 10.10.2017, 11:35 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Прикрепил формулу, вроде работает, но можно более простую формулу придумать.
К сообщению приложен файл: 8976235.xlsx(13Kb)
 
Ответить
СообщениеПрикрепил формулу, вроде работает, но можно более простую формулу придумать.

Автор - Vladimir116
Дата добавления - 10.10.2017 в 11:35
_Boroda_ Дата: Вторник, 10.10.2017, 11:43 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11852
Репутация: 4911 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Vladimir116, в таких случаях пишут не
Прикрепил формулу
, а "взял формулу отсюда"

можно более простую формулу придумать

- это вопрос или утверждение? Если вопрос, то да, можно. Если утверждение, то придумайте


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеVladimir116, в таких случаях пишут не
Прикрепил формулу
, а "взял формулу отсюда"

можно более простую формулу придумать

- это вопрос или утверждение? Если вопрос, то да, можно. Если утверждение, то придумайте

Автор - _Boroda_
Дата добавления - 10.10.2017 в 11:43
Vladimir116 Дата: Вторник, 10.10.2017, 11:52 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
а "взял формулу отсюда"

что в этом плохого, если сам недавно с подобным разбирался
 
Ответить
Сообщение
а "взял формулу отсюда"

что в этом плохого, если сам недавно с подобным разбирался

Автор - Vladimir116
Дата добавления - 10.10.2017 в 11:52
Shurf Дата: Вторник, 10.10.2017, 12:11 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 7 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, Красиво, спасибо за вариант, мне до такого как до китая... :) ,как то проще UDF или макрос написать )


Сообщение отредактировал Shurf - Вторник, 10.10.2017, 12:14
 
Ответить
Сообщение_Boroda_, Красиво, спасибо за вариант, мне до такого как до китая... :) ,как то проще UDF или макрос написать )

Автор - Shurf
Дата добавления - 10.10.2017 в 12:11
and_evg Дата: Вторник, 10.10.2017, 13:20 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 233
Репутация: 41 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, Вопрос, а почему ваш файл из сообщения 7 требует включения макросов?
 
Ответить
Сообщение_Boroda_, Вопрос, а почему ваш файл из сообщения 7 требует включения макросов?

Автор - and_evg
Дата добавления - 10.10.2017 в 13:20
buchlotnik Дата: Вторник, 10.10.2017, 13:25 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3437
Репутация: 922 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
почему ваш файл из сообщения 7 требует включения макросов?
Потому что
Код
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
- это макрофункция


"заступлюсь за Юрия" - публичный плевок в душу!

Сообщение отредактировал buchlotnik - Вторник, 10.10.2017, 13:26
 
Ответить
Сообщение
Цитата
почему ваш файл из сообщения 7 требует включения макросов?
Потому что
Код
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
- это макрофункция

Автор - buchlotnik
Дата добавления - 10.10.2017 в 13:25
and_evg Дата: Вторник, 10.10.2017, 13:27 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 233
Репутация: 41 ±
Замечаний: 0% ±

Excel 2007
- это макрофункция

Точно! Совсем забыл что макрофункции можно и в диспечере писать :)
 
Ответить
Сообщение
- это макрофункция

Точно! Совсем забыл что макрофункции можно и в диспечере писать :)

Автор - and_evg
Дата добавления - 10.10.2017 в 13:27
ComiC Дата: Среда, 11.10.2017, 01:41 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А где это видно в Вашем примере?

Как пример у "Сидорова" присутствует Апельсин только у него. Но другими листами показал пример повторяющихся ячеек.

Но файл был как пример, сейчас понял, что в примерном файле не указал повторяющиеся "фрукты" на одном листе :(

Но решение подходящее все же вроде как найдено!


Сообщение отредактировал ComiC - Среда, 11.10.2017, 01:57
 
Ответить
Сообщение
А где это видно в Вашем примере?

Как пример у "Сидорова" присутствует Апельсин только у него. Но другими листами показал пример повторяющихся ячеек.

Но файл был как пример, сейчас понял, что в примерном файле не указал повторяющиеся "фрукты" на одном листе :(

Но решение подходящее все же вроде как найдено!

Автор - ComiC
Дата добавления - 11.10.2017 в 01:41
ComiC Дата: Среда, 11.10.2017, 01:59 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Да если нет на листе этого фрукта то ничего страшного, а вот если их много одинаковых на листе это уже другая история.

Спасибо, данный вариант подошел. Макрос ищет одинаковые "фрукты" на всех листах и считает общее их количество. Вроде то, что надо!
 
Ответить
Сообщение
Да если нет на листе этого фрукта то ничего страшного, а вот если их много одинаковых на листе это уже другая история.

Спасибо, данный вариант подошел. Макрос ищет одинаковые "фрукты" на всех листах и считает общее их количество. Вроде то, что надо!

Автор - ComiC
Дата добавления - 11.10.2017 в 01:59
ComiC Дата: Среда, 11.10.2017, 02:45 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 47
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
В диспетчер имен (Контрл F3) пишете имя "Листы" с формулой

Спасибо! Ваше решение также подошло!
 
Ответить
Сообщение
В диспетчер имен (Контрл F3) пишете имя "Листы" с формулой

Спасибо! Ваше решение также подошло!

Автор - ComiC
Дата добавления - 11.10.2017 в 02:45
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск количества яблок во всех листах (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!