Excel макрос для суммирования таблиц
Добрый день. Подскажите, как сделать макрос который сможет суммировать данные с разных книг (разного названия), но таблицы одинаковые. Суть проблемы такова, присылают 21 отчет, и мне нужно суммировать все в один. Заранее благодарен. Книгу с таблицами прикреплю.
[p.s.]
В том то и дело, книги будут называться по разному, листы в каждой книге называются одинаково, размер ячеек и расположение одинаково. А один файл, с точно такой же таблицей просто будет называться "общий отчет" или в том же духе. Нужно, что бы все данные из разных книг, лист "табличная форма", суммировались в книге "общий" в листе "табличная форма"
Добрый день. Подскажите, как сделать макрос который сможет суммировать данные с разных книг (разного названия), но таблицы одинаковые. Суть проблемы такова, присылают 21 отчет, и мне нужно суммировать все в один. Заранее благодарен. Книгу с таблицами прикреплю.
[p.s.]
В том то и дело, книги будут называться по разному, листы в каждой книге называются одинаково, размер ячеек и расположение одинаково. А один файл, с точно такой же таблицей просто будет называться "общий отчет" или в том же духе. Нужно, что бы все данные из разных книг, лист "табличная форма", суммировались в книге "общий" в листе "табличная форма" GSeReGa
Из Вашего примера - не понятно какие ячейки нужно суммировать - выделите эти ячейки каким - то одним цветом
Тогда все будет намного проще.
И еще как будут называться файлы всегда одинаково, или как макросу понять куда "тыкаться"?
- вложите парочку файлов для проверки.
Из Вашего примера - не понятно какие ячейки нужно суммировать - выделите эти ячейки каким - то одним цветом
Тогда все будет намного проще.
И еще как будут называться файлы всегда одинаково, или как макросу понять куда "тыкаться"?
- вложите парочку файлов для проверки. SLAVICK
Переносите все файлы в одну папку, затем циклом проходите по всем книгам в папке
и переносите нужные данные в сводный файл
Переносите все файлы в одну папку, затем циклом проходите по всем книгам в папке
и переносите нужные данные в сводный файл Kuzmich
Смотрите архив - там создал папку с файлами - туда нужно кидать файлы для обработки.
разархивируйте архив в любую папку.
В общем файле - кнопка нажимайте и ждите
С всех файлов данные просуммируются в общий файл
Смотрите архив - там создал папку с файлами - туда нужно кидать файлы для обработки.
разархивируйте архив в любую папку.
В общем файле - кнопка нажимайте и ждите
С всех файлов данные просуммируются в общий файл SLAVICK
Иногда все проще чем кажется с первого взгляда.
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему "в лоб", то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами "Выручка" и "План"), то можно использовать функцию СУММЕСЛИ (SUMIF) :
Первый аргумент этой функции - диапазон проверяемых ячеек, второй - критерий отбора (слово "Выручка"), третий - диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF) , которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й . N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то "Выручка", а где-то "Revenue" и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. "Читать" эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа - штука красивая, но имеют слабое место - быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel "задуматься" на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой - функцией БДСУММ (DSUM) . Перед использованием эта функция требует небольшой доработки, а именно - создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово "Условие" в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) - не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM) :
Способ 4. Суммирование каждой 2-й, 3-й. N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
WorksheetFunction.SumIfs – это метод VBA Excel, который вычисляет сумму числовых значений в диапазоне ячеек с учетом нескольких условий (критериев).
Синтаксис метода WorksheetFunction.SumIfs
- ДСумм – диапазон суммирования, представляющий из себя часть таблицы, в ячейках которого, соответствующих условиям (критериям), суммируются значения.
- ДУсл – диапазон условия, представляющий из себя часть обрабатываемой таблицы, в ячейках которого ищется совпадение с условием (критерием) суммирования.
- Усл – это условие, которое применимо к диапазону, указанному перед ним, и определяющее критерий суммирования.
Первые три аргумента являются обязательными, остальные – необязательные. Всего выражение может содержать один диапазон суммирования (ДСумм) и четырнадцать пар диапазон+условие (ДУсл+Усл).
Диапазоны условий не должны повторяться – повторение приведет к ошибке. Диапазон суммирования можно один раз использовать как диапазон условия.
В параметре «Усл» метода WorksheetFunction.SumIfs можно использовать знаки подстановки:
- вопросительный знак (?) – заменяет один любой символ;
- звездочка (*) – заменяет любую последовательность символов (в том числе ни одного символа);
- тильда (~) – ставится перед вопросительным знаком или звездочкой, чтобы они обозначали сами себя.
Вычисление сумм с несколькими условиями
Таблица, которая использовалась для реализации примеров в коде VBA Excel:
Дата | Магазин | Продавец | Выручка |
---|---|---|---|
01.11.2019 | Липка | Лыкова | 20000 |
01.11.2019 | Берёзка | Серёжкина | 18000 |
01.11.2019 | Дубок | Бочкина | 23000 |
02.11.2019 | Липка | Лаптева | 30000 |
02.11.2019 | Берёзка | Брунькина | 25000 |
02.11.2019 | Дубок | Жёлудева | 17000 |
03.11.2019 | Липка | Лыкова | 24000 |
03.11.2019 | Берёзка | Серёжкина | 19000 |
03.11.2019 | Дубок | Бочкина | 35000 |
04.11.2019 | Липка | Лаптева | 27000 |
04.11.2019 | Берёзка | Брунькина | 31000 |
04.11.2019 | Дубок | Жёлудева | 26000 |
05.11.2019 | Липка | Лыкова | 16000 |
05.11.2019 | Берёзка | Серёжкина | 22000 |
05.11.2019 | Дубок | Бочкина | 33000 |
06.11.2019 | Липка | Лаптева | 16000 |
06.11.2019 | Берёзка | Брунькина | 28000 |
06.11.2019 | Дубок | Жёлудева | 29000 |
Если хотите повторить примеры, скопируйте эту таблицу и вставьте на рабочий лист Excel в ячейку A1. Таблица займет диапазон A1:D19.
Пример 1
Применение двух числовых условий в качестве критериев суммирования и использование диапазона суммирования в качестве диапазона условия:
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Range | Диапазон — диапазон ячеек, который необходимо оценить по критериям. |
Arg2 | Обязательный | Variant | Критерии — критерии в виде числа, выражения или текста, определяя, какие ячейки будут добавлены. Например, критерии могут быть выражены как 32, "32", ">32" или "яблоки". |
Arg3 | Необязательный | Variant | Sum_range — фактические ячейки, которые необходимо добавить, если их соответствующие ячейки в диапазоне соответствуют критериям. Если sum_range опущен, ячейки в диапазоне оцениваются по критериям и добавляются, если они соответствуют критериям. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Sum_range не должен быть того же размера и формы, что и диапазон. Фактические ячейки, которые добавляются, определяются с помощью верхней левой ячейки в sum_range в качестве первой ячейки, а затем в том числе ячеек, которые соответствуют размеру и форме для диапазона. Пример:
Если диапазон | И sum_range это | Фактические ячейки |
---|---|---|
A1:A5 | B1:B5 | B1:B5 |
A1:A5 | B1:B3 | B1:B5 |
A1:B4 | C1:D4 | C1:D4 |
A1:B4 | C1:C2 | C1:D4 |
В критериях используйте символы подпольной карты, знак вопросов (?) и звездочки (*). Знак вопроса совпадает с любым одним персонажем; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический знак вопроса или звездочки, введите тильду (~) перед персонажем.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Читайте также: