Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выполнить просмотр на нескольких листах и ​​суммировать результаты в Excel?

Поиск и подстановка по нескольким условиям

Если вы являетесь опытным пользователем Microsoft Excel, вы должны быть знакомы с функцией поиска и замены VRP или VLOOKUP (если вы еще не знакомы, прочитайте сначала эту статью, чтобы стать таким). Для тех, кто его понимает, нет необходимости его рекламировать 🙂 — Ни один сложный расчет в Excel без него не обходится. Но есть проблема: эта функция может искать данные, соответствующие только одному параметру. Что делать, если их несколько?

Предположим, что у нас есть база данных цен на товары за разные месяцы:

vlookup-2cols1.png

Вам необходимо найти и извлечь цену заданного продукта (Nectarine) за заданный месяц (январь), т.е. получить результат 152, но автоматическим способом, т.е. по формуле. Чистый RVP в этом случае не пригодится, но есть и другие способы решения этой проблемы.

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (если не самый удобный) способ выполнить эту задачу. Поскольку функция VLOOKUP может искать только в одном столбце, а не в нескольких, нам нужно создать один столбец из нескольких!

Мы добавим новый столбец рядом с нашей таблицей, в котором с помощью оператора сцепления (&) создадим уникальный ключевой столбец для поиска:

vlookup-2cols2.png

Теперь вы можете использовать знакомую функцию VRP (VLOOKUP), чтобы найти склеенную пару NectarinJanuary из ячеек H3 и J3 созданного вами ключевого столбца:

vlookup-2cols3.png

Плюсы : Простой способ, привычная функция, работает с любыми данными.

Недостатки: Необходимо сделать дополнительный столбец, а затем, возможно, даже скрыть его от пользователя. Если вы измените количество строк в таблице, вам придется переносить формулу связи на новые строки (хотя это можно упростить, используя смарт-таблицу).

Способ 2. Функция СУММЕСЛИМН

Если вам нужно найти точное число (в нашем случае цена — это просто число), вместо функции БПФ можно использовать функцию SUMIFS, которая доступна с Excel 2007. Эта функция используется для выбора и суммирования числовых значений для многих (до 127!) условий. Однако если в нашем списке нет повторяющихся товаров в одном и том же месяце, то просто отображается значение цены для этого товара и месяца:

Читайте так же:
Как вставить разрыв страницы каждые x строк в Excel?

vlookup-2cols4.png

Преимущества: не нужна дополнительная колонка, раствор можно легко масштабировать для большего количества условий (до 127), подсчитывается быстро.

Недостатки: работает только с выводом числовых данных, не применим для текстового поиска, не работает на старых версиях Excel (2003 и более ранних).

Способ 3. Формула массива

Уже было подробно рассказано, как использовать связку функций INDEX и MATCH в качестве более мощной альтернативы ВПР (с видео). Однако в нашем случае вы можете применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

  1. Выделите пустую зеленую ячейку, где должен быть результат.
  2. Введите в строке формул в нее следующую формулу:

vlookup-2cols6.png

Как это работает в действительности:

Функция INDEX возвращает содержимое N-ой ячейки по порядку из ценового диапазона C2:C161. Поиск порядкового номера нужной ячейки осуществляется с помощью функции ПОИСК. Он последовательно ищет комбинацию названия продукта и месяца (NectarineJanuary) во всех ячейках интервала A2:A161&B2:B161, объединенных из двух столбцов, и выдает порядковый номер ячейки, в которой найдено точное совпадение. В принципе, это первый метод, но ключевой столбец создается почти непосредственно в формуле, а не в ячейках электронной таблицы.

Плюсы : Нет необходимости в отдельном столбце, работает с числами и текстом.

Минусы: Значительно тормозит на больших массивах (впрочем, как и все матричные формулы), особенно если указывать «свободные» диапазоны или сразу целые столбцы (т.е. вместо A2:A161 ввести A:A и т.д.) Многие люди не привыкли к матричным формулам в принципе (так что вот).

ВПР и СУММ в Excel – вычисляем сумму найденных совпадающих значений

При работе с числовыми данными в Excel часто требуется не только извлечь связанные данные из другой таблицы, но и просуммировать несколько столбцов или строк. Это можно сделать, объединив функции SUM и VPR, как показано ниже.

Предположим, у нас есть список товаров с данными о продажах за несколько месяцев, с отдельным столбцом для каждого месяца. Источником данных является ежемесячный лист продаж:

Теперь нам нужно сделать таблицу итогов с суммами продаж по каждому товару.

Решением этой проблемы является использование массива констант в аргументе col_index_num (номер_столбца) функции FFT. Вот пример формулы:

=SUM(VLOOKUP(lookup value, lookup range, <2,3,4>, FALSE))
=СУМ(ВПР(искомое_значение;таблица;<2;3;4>;ЛОЖЬ))

Как видите, мы использовали массив для третьего аргумента, чтобы выполнить поиск несколько раз в одной функции ВПР, и получить сумму значений в столбцах 2, 3 и 4.

Теперь давайте применим эту комбинацию БПФ и SUMM к данным нашей таблицы, чтобы найти общее количество продаж в столбцах с B по M:

Чтобы ввести формулу в массив, обязательно нажмите Ctrl+Shift+Enter, а не просто Enter. Если вы используете Microsoft Excel, ваши формулы будут заключены в фигурные скобки:

Использование ВПР и СУММ в Excel

Если вы просто нажмете клавишу Enter, то расчет будет произведен только для первого значения матрицы, что даст неверный результат.

Почему формула на рисунке выше показывает [@Product] как искомое значение? Мои данные были преобразованы в таблицу с помощью команды Таблица на вкладке Вставка. Вместо того чтобы работать с простыми диапазонами, я предпочитаю работать с полнофункциональными таблицами Excel. Excel, например, автоматически копирует формулы во весь столбец, когда вы вводите одну из них в ячейку.

Как видите, использовать функции FFT и SUM в Excel достаточно просто. Это не самое лучшее решение, особенно если вам приходится работать с большими таблицами. Дело в том, что использование формул массива может замедлить работу приложения, поскольку каждое значение в массиве делает отдельный вызов функции ВПР. При большем количестве значений массива Excel имеет больше формул массива в рабочей книге и работает медленнее.

Эту проблему можно решить, используя комбинацию функций INDEX и MATCH вместо VLOOKUP и SUM. Примеры этих формул вы увидите далее в этой статье.

Распространенные неполадки

Вероятная причина

Длинные строки возвращаются с некорректным значением.

Функция READ возвращает неверные результаты, если она используется для сопоставления строк длиной более 255 символов.

С такими строками можно использовать функциюCELLINE или оператор сцепления &. Вот пример: =CLOTE(A2:A5; «длинная строка»&»другая длинная строка»).

Функция должна вернуть значение, но она этого не делает.

Инвертированные запятые обязательны в аргументе «критерий».

Формуле WITHIN IF присваивается #VALUE!, когда она ссылается на другую таблицу.

Эта ошибка возникает при вычислении ячеек, когда формула содержит функцию, которая ссылается на ячейки или диапазон в закрытой книге. Для того чтобы эта функция работала, другая книга должна быть открыта.

Как выполнить просмотр на нескольких листах и ​​суммировать результаты в Excel?

У меня есть четыре одинаковых листа, и теперь мне нужно количество заказов в столбце «Продукт» каждого листа, как показано на скриншоте ниже. Можно ли использовать Excel для простого и быстрого решения этой проблемы?

doc vlookup sum несколько листов 1
1
doc vlookup sum несколько листов 2

Vlookup на нескольких листах и ​​суммирование результатов с помощью формулы

Чтобы найти конкретные данные и суммировать соответствующее значение, сделайте следующее

1. Сначала введите все имена листов, которые вы хотите обобщить, затем создайте для них имя диапазона, см. скриншот :

doc vlookup sum несколько листов 3

Затем введите формулу ниже:

=SUMPRODUCT(SUMIF(INDIRECT(«‘»&ShtList&»‘!B2:B9»),$F$2,INDIRECT(«‘»&ShtList&»‘!C2:C9»))) в пустую ячейку, где вы хотите найти результат вычисления, затем нажмите Перейти key, вы получите общий порядок конкретных данных на нескольких листах, см. снимок экрана:

doc vlookup sum несколько листов 4

Примечание: В приведенной выше формуле ShtList — это имя диапазона, который вы создали для имен листов в шаге 1, B2: B9 содержит ячейки списка продуктов, которые вы хотите найти на каждом листе, F2 — критерии, на которых основаны критерии, C2: C9 содержат ячейки каждого листа, которые вы хотите суммировать.

Vlookup на нескольких листах и ​​суммирование результатов с помощью Kutools for Excel

С помощью Kutools for Excel и его утилиты Combine вы сможете легко решить эту проблему без использования формул.

После установки Kutools for Excel необходимо выполнить следующие шаги:

1. Cthulus Plus > Комбинировать, Скриншот :

2. в мастере объединения рабочих листов выберите Объединить и рассчитать значения из нескольких книг на одном листе (см. снимок экрана):

doc vlookup sum несколько листов 6

3. Активная книга и рабочие листы перечислены в диалоговом окне; нажмите Далее, чтобы увидеть рабочие листы и диапазоны ячеек для суммирования. См. снимок экрана:

doc vlookup sum несколько листов 7

4. Затем нажмите кнопку Далее. На шаге 3 выберите Сумма из раскрывающегося списка Функция файла, а затем отметьте Верхняя строка и Левый столбец в разделе Использовать ярлыки для. При необходимости можно также установить флажок Создавать ссылки на исходные данные. См. скриншот:

doc vlookup sum несколько листов 8

5. На следующем экране выберите имя файла и местоположение объединенного листа, как показано на скриншоте:

doc vlookup sum несколько листов 9

6. Затем нажмите кнопку Сохранить, все соответствующие значения на нескольких листах будут объединены, см. снимок экрана:

Выборочные вычисления по одному или нескольким критериям

Ниже представлена таблица продаж, например, следующего сорта:

cond_sum1.png

Задание: Сложите все заказы, которые менеджер Григорьев сделал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Наша задача не содержит более одного условия (например, все заказы Петрова или все заказы Копейки), поэтому ее можно легко решить с помощью функции СУММЕСЛИ в Excel в категории Математика и триггер. Выберите пустую ячейку для результата, нажмите кнопку fx в строке формул и выберите из списка функцию СУММЕСЛИ:

cond_sum2.png

Нажмите OK и введите аргументы:

cond_sum3.png

  • Диапазон — это ячейки, которые мы проверяем на соответствие критериям. В нашем случае это диапазон с фамилиями менеджеров по продажам.
  • Критерий — это то, что мы ищем в указанном выше диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) в качестве масок или подстановочных знаков. Звездочка заменяет любое число любого символа, вопросительный знак заменяет любой символ. Так, например, чтобы найти все продажи менеджеров с фамилией из пяти букв, можно использовать критерий . А чтобы найти все продажи менеджеров, чья фамилия начинается на «П» и заканчивается на «В», используйте критерий П*В. Нет разницы между прописными и строчными буквами.
  • Range_amounts — это ячейки, значения которых мы хотим суммировать, т.е. в нашем случае — значения заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

В случаях, когда имеется несколько критериев (например, чтобы найти заказы Григорьева для Копейки), SUMIF не поможет, так как он не может сравнивать несколько критериев. СУММЕСЛИМС (SUMIFS) теперь является функцией в Excel 2007, и ее условия были увеличены до 127! Математические функции похожи на эту функцию, но имеют больше аргументов.

cond_sum4.png

В правой полосе прокрутки можно указать еще третью пару (Range_condition3-condition3), и так далее. — если необходимо.

Если у вас все еще есть старая версия Excel 2003, но вам нужно исправить несколько проблем, сделайте это сложным способом — смотрите методы ниже.

Способ 3. Столбец-индикатор

Добавим в нашу таблицу еще один столбец, который будет служить своего рода индикатором: если заказ достался Копейке и Григорьеву, то ячейка в этом столбце будет иметь значение 1, иначе — 0. Формула, которую нужно ввести в эту колонку, очень проста:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические уравнения в скобках дают значения TRUE или FALSE, которые для Excel равны 1 и 0. Поскольку мы перемножаем эти выражения, мы можем прийти к решению, только если выполняются оба условия. Давайте умножим значения продаж на значения результирующих столбцов и подведем итог в зеленой ячейке:

cond_sum5.png

Способ 4. Волшебная формула массива

Если вы никогда не сталкивались с такой важной функцией Excel, как матричные формулы, советую прочитать дальше. В нашем случае проблема решается одной формулой:

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

cond_sum6.png

После ввода этой формулы нажмите не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel увидит ее как формулу массива и добавит фигурные скобки. Скобки не обязательно вводить с клавиатуры. Как и предыдущий метод, этот метод можно легко масштабировать до трех, четырех и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

Мы также можем решить проблему с помощью функции DSUM, которая находится в категории «База данных». Нюанс заключается в том, что для работы этой функции необходимо создать специальный диапазон критериев — ячейки, содержащие условия отбора, — а затем указать этот диапазон в качестве аргумента функции:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector