Как суммировать данные из разных файлов в excel
Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .
Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.
Для создания такой формулы выполните приведенные ниже действия.
- Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
- Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
- Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
- Нажмите Enter, и формула будет введена в ячейку.
В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .
Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ('*'!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .
Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ('Регион*"!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .
Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.
На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы.
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Модель агрегации файлов.zip (53,5 KiB, 1 206 скачиваний)
Видеоинструкция:
Для ведения бюджета применяется таблица такого вида:
Сама таблица преобразована заранее в так называемую "умную" таблицу: выделяем таблицу -вкладка Вставка (Insert) и выбрать Таблица (Table) :
Для каждого филиала отдельный файл только с одним этим листом. После заполнения филиалы присылают эти файлы в головной офис, где их необходимо объединить в одну такую же таблицу, но суммировать данные по каждой статье и каждому месяцу, чтобы получить единый файл бюджета с суммированием по каждой статье от всех филиалов.
Все действия будут производиться при помощи Power Query и лишь в самом конце на лист будет выгружена итоговая таблица, которую потом надо будет только обновлять(пара кликов мыши), если данные изменятся или будут присланы файла от за другие месяцы или от других филиалов. Никаких макросов использовать не надо.
Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) -Получить данные -Из файла -Из папки:
В появившемся окне указываем путь к папке, в которую были помещены файлы бюджетов, присланные филиалами
Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца: Content и Name :
Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши -Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:
=Excel.Workbook([Content])
Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» - получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:
Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:
Теперь столбцы Content , Name и Name.1 можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений NULL :
А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков:
Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 - имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:
Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец Статьи вкладка Преобразование -Таблица -Группировать по:
В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:
Группировка – оставляем поле Статьи . Ниже создаем 13 столбцов группировки – по одному на каждый месяц и один для Итого. Для каждого столбца указываем имя(лучше такое же как и имя исходного столбца – название месяца, т.к. именно они будут использоваться в итоговой таблице), Операция – Сумма .
Останется перейти на вкладку Главная -Закрыть и загрузить. Готовая таблица будет выгружена на новый лист текущей книги.
Теперь, если в папку будут помещены другие файлы или имеющиеся будут заменены другими и результирующую таблицу бюджета потребуется обновить – все, что необходимо будет сделать, это на созданной PowerQuery таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:
Все файлы в папке будут просмотрены, преобразованы и просуммированы.
Хочется в общей таблице прописать формулу, чтобы в ячейке были суммы по дням, неделям и месяцам, но:
при ручном указании откуда брать данные - срабатывает, получаем формулу вида
='[менеджер 1.xlsx]Лист1'!$C$19+'[менеджер 2.xlsx]Лист1'!$C$19+'[менеджер 3.xlsx]Лист1'!$A$19:$C$19
а при сдвигании ячейки - просто копируется ячейка в точности.
Вручную ставить оставшиеся пол-года не вариант.
И, конечно, хочется чтобы было автоматическое изменение в таблице при изменении файлов. Видимо это будет уже макрос. Тогда создам тему в соответствующей ветке.
Могу по английски переименовать файл, если это проблема.
Возможно, это простая тема и я сильно туплю.
Поиск по "сводные таблицы" и/или "из разных файлов" дал мне 47 результатов, по которым я терпеливо и кропотливо прошелся и все просмотрел. Того, чего мне надо - не нашел. Может быть я не правильно сформулировал.
Прошу прощения, если таки задублировал похожую тему, не ругайтесь и не баньте.
Спасибо заранее.
- 1.Есть несколько менеджеров, совершающих звонки, заполняющих в конце рабочего дня в таблице количество звонков и результаты, ежедневно. Файл менеджеров так и назовем -менеджер 1, менеджер 2 и т.д.
- 2.Есть руководитель этих менеджеров, который также заполняет такую же в точности таблицу. Файл - руководитель.
- 3.И есть общая таблица, в точности повторяющая две вышесказанных, назовем его - отчет.
Хочется в общей таблице прописать формулу, чтобы в ячейке были суммы по дням, неделям и месяцам, но:
при ручном указании откуда брать данные - срабатывает, получаем формулу вида
='[менеджер 1.xlsx]Лист1'!$C$19+'[менеджер 2.xlsx]Лист1'!$C$19+'[менеджер 3.xlsx]Лист1'!$A$19:$C$19
а при сдвигании ячейки - просто копируется ячейка в точности.
Вручную ставить оставшиеся пол-года не вариант.
И, конечно, хочется чтобы было автоматическое изменение в таблице при изменении файлов. Видимо это будет уже макрос. Тогда создам тему в соответствующей ветке.
Могу по английски переименовать файл, если это проблема.
Возможно, это простая тема и я сильно туплю.
Поиск по "сводные таблицы" и/или "из разных файлов" дал мне 47 результатов, по которым я терпеливо и кропотливо прошелся и все просмотрел. Того, чего мне надо - не нашел. Может быть я не правильно сформулировал.
Прошу прощения, если таки задублировал похожую тему, не ругайтесь и не баньте.
Спасибо заранее. svsrabbit
- 1.Есть несколько менеджеров, совершающих звонки, заполняющих в конце рабочего дня в таблице количество звонков и результаты, ежедневно. Файл менеджеров так и назовем -менеджер 1, менеджер 2 и т.д.
- 2.Есть руководитель этих менеджеров, который также заполняет такую же в точности таблицу. Файл - руководитель.
- 3.И есть общая таблица, в точности повторяющая две вышесказанных, назовем его - отчет.
Хочется в общей таблице прописать формулу, чтобы в ячейке были суммы по дням, неделям и месяцам, но:
при ручном указании откуда брать данные - срабатывает, получаем формулу вида
='[менеджер 1.xlsx]Лист1'!$C$19+'[менеджер 2.xlsx]Лист1'!$C$19+'[менеджер 3.xlsx]Лист1'!$A$19:$C$19
а при сдвигании ячейки - просто копируется ячейка в точности.
Вручную ставить оставшиеся пол-года не вариант.
И, конечно, хочется чтобы было автоматическое изменение в таблице при изменении файлов. Видимо это будет уже макрос. Тогда создам тему в соответствующей ветке.
Могу по английски переименовать файл, если это проблема.
Возможно, это простая тема и я сильно туплю.
Поиск по "сводные таблицы" и/или "из разных файлов" дал мне 47 результатов, по которым я терпеливо и кропотливо прошелся и все просмотрел. Того, чего мне надо - не нашел. Может быть я не правильно сформулировал.
Прошу прощения, если таки задублировал похожую тему, не ругайтесь и не баньте.
Спасибо заранее. Автор - svsrabbit
Дата добавления - 10.06.2014 в 17:26
Нужна сводная таблица по нескольким (порядка 17) книгам.
Для примера выкладываю 2 книги. Каждая - отдельная книга с таблицей. В итоге необходимо получить сумму значений по каждой расчетной ячейке (ячейки с номерами пропускаем). вот как-то так. Буду рад помощи.
Нужна сводная таблица по нескольким (порядка 17) книгам.
Для примера выкладываю 2 книги. Каждая - отдельная книга с таблицей. В итоге необходимо получить сумму значений по каждой расчетной ячейке (ячейки с номерами пропускаем). вот как-то так. Буду рад помощи. Vasill
Несколько вопросов. Я просто такого еще не видел.
Для чего так много объединенных ячеек, как по столбцам, так и по строкам? Название столбцов прочесть невозможно.
Заголовок таблицы можно не множить на каждую страницу, а сделать закрепленную область и печать на каждой странице.
Книги немного отличаются. В одной вверху две лишних строки.
Где делать суммирование, создать книгу с именем Сумма или как-то иначе.
Расположение книг в ПК, в одной папке с книгой Сумма или иначе?
Решение только формулами, только макросом или все равно?
Несколько вопросов. Я просто такого еще не видел.
Для чего так много объединенных ячеек, как по столбцам, так и по строкам? Название столбцов прочесть невозможно.
Заголовок таблицы можно не множить на каждую страницу, а сделать закрепленную область и печать на каждой странице.
Книги немного отличаются. В одной вверху две лишних строки.
Где делать суммирование, создать книгу с именем Сумма или как-то иначе.
Расположение книг в ПК, в одной папке с книгой Сумма или иначе?
Решение только формулами, только макросом или все равно? AlexM
Такую форму скинули. не я ее делал. Пытаюсь жене помочь. Ей все это предстоит руками пересчитать - бесчеловечно просто.
Объясню немного поподробнее - есть несколько контор, Они предоставляют данные вот в таких таблицах. Необходимо получить общее кол-во по каждой расчетной ячейке.
Форма не имеет значение, способ решения тоже - главное что б были цифры. Я готов все листы переместить в одну книгу. Насчет верхних двух строчек - убирал название - у одного файла просто текст убрал, а во втором - вырезал ячейки. вот и разница. в файле 1 - форма правильнее.
Такую форму скинули. не я ее делал. Пытаюсь жене помочь. Ей все это предстоит руками пересчитать - бесчеловечно просто.
Объясню немного поподробнее - есть несколько контор, Они предоставляют данные вот в таких таблицах. Необходимо получить общее кол-во по каждой расчетной ячейке.
Форма не имеет значение, способ решения тоже - главное что б были цифры. Я готов все листы переместить в одну книгу. Насчет верхних двух строчек - убирал название - у одного файла просто текст убрал, а во втором - вырезал ячейки. вот и разница. в файле 1 - форма правильнее. Vasill
Если вы можете все таблицы из разных книг переложить в одну, раскинув по листам, то можно поступить так
1. Все таблицы делаете однотипными, т.е. чтобы суммируемые ячейки были по одному адресу во всех таблицах
2. В первый лист, допустим с именем "Сумма" вставляете пустую таблицу, которую будете заполнять формулами.
3. Далее идут листы, которые суммируем с именами 1, 2, 3. N
В ячейках листа "Сумма", например в ячейке В2 формула
Если вы можете все таблицы из разных книг переложить в одну, раскинув по листам, то можно поступить так
1. Все таблицы делаете однотипными, т.е. чтобы суммируемые ячейки были по одному адресу во всех таблицах
2. В первый лист, допустим с именем "Сумма" вставляете пустую таблицу, которую будете заполнять формулами.
3. Далее идут листы, которые суммируем с именами 1, 2, 3. N
В ячейках листа "Сумма", например в ячейке В2 формула
Между листом 1 и N можно вставить любое количество листов, которые будут сммироваться.
См. файл. AlexM
Между листом 1 и N можно вставить любое количество листов, которые будут сммироваться.
См. файл. Автор - AlexM
Дата добавления - 12.02.2013 в 12:29
Как просто.
Я попробую.
Боюсь как бы не возникла сложность из-за объединения ячеек.
О результатах - отпишусь.
Как просто.
Я попробую.
Боюсь как бы не возникла сложность из-за объединения ячеек.
О результатах - отпишусь. Vasill
[quote=AlexM, ]Если вы можете все таблицы из разных книг переложить в одну, раскинув по листам, то можно поступить так [/quote]
А если это разные книги, вернее одинаковые, только названием (имя отдела) отличаются, там по одному листу, и нужно определенный столбик (квартал) просчитать и свести в один.
Казалось бы так же суммой, но отдать надо будет тока итоговый файл и связи порушаться. можно скопировать значения, но вот бы уже вставились нужные цифры.
Видимо только vba или vbs?
[quote=AlexM, ]Если вы можете все таблицы из разных книг переложить в одну, раскинув по листам, то можно поступить так [/quote]
А если это разные книги, вернее одинаковые, только названием (имя отдела) отличаются, там по одному листу, и нужно определенный столбик (квартал) просчитать и свести в один.
Казалось бы так же суммой, но отдать надо будет тока итоговый файл и связи порушаться. можно скопировать значения, но вот бы уже вставились нужные цифры.
Видимо только vba или vbs? SARAN
А если это разные книги, вернее одинаковые, только названием (имя отдела) отличаются, там по одному листу, и нужно определенный столбик (квартал) просчитать и свести в один.
Казалось бы так же суммой, но отдать надо будет тока итоговый файл и связи порушаться. можно скопировать значения, но вот бы уже вставились нужные цифры.
Видимо только vba или vbs? Автор - SARAN
Дата добавления - 24.09.2013 в 09:13
Public Function LoadButtonOpenBookAndCalculate() '\Добавить кнопку на панель инструментов "Форматирование"\
Dim pushLC As Object '\Объявляем переменную нового инструмента на панели задач\
Set pushLC = Application.CommandBars("Formatting").Controls.Add(Type:=msoControlButton, Temporary:=True) '\Добавляем новый элемент на панель\
With pushLC '\Настраиваем элемент\
.BeginGroup = True '\Добавить разделитель\
.Style = msoButtonAutomatic '\Кнопка содержит рисунок\
.Caption = "Открыть книгу для калькуляции: сложить текущие значения с значениями из выбранной книги" '\Имя/всплывающая подсказка\
.OnAction = "OpenBookForCalcSumValue" '\Назначить макрос\
.FaceId = 283 '\Иконка калькулятора\
End With
Set pushLC = Nothing '\Очищаем память\
End Function
'==========================================================================
' Открыть книгу для добавления из нее значений к текущим, путем сложения
' Last Update: 10/06/2011
'==========================================================================
Public Function OpenBookForCalcSumValue()
Dim PathAndNameBook As String, NameBook As String, BookList As Object, RangeValue(4) As Object, objCell As Object, k As Integer, q As Byte
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Do
k = k + 1 '\Наращиваем значение переменной\
Loop Until Mid(PathAndNameBook, Len(PathAndNameBook) - k, 1) = "\" '\Первый слеш справа\
NameBook = Right(PathAndNameBook, k) '\Получить только имя книги\
Set BookList = Workbooks(NameBook).Sheets("р6.5") '\Первый лист открытой книги\
ThisWorkbook.Activate '\Сделать активной текущую книгу\
With BookList '\C листом открытой книги\
Set RangeValue(0) = .Range("G10:W446")
End With
For q = 0 To 0 '\По массиву с диапазонами данных\
For Each objCell In RangeValue(q) '\Ячейка диапазона\
With objCell '\С ячейкой\
If .HasFormula = False And Range(.Address).HasFormula = False Then '\Если ячейки не содержат формул\
If .Locked = False And Range(.Address).Locked = False Then '\Ячейка не защищена?\
L1:
If IsNumeric(.Value) = True Then '\Число?\
Range(.Address) = Range(.Address) + .Value '\Перенести данные сложив их с ячейкой-получателем\
Application.ScreenUpdating = True
Workbooks(NameBook).Activate '\Показать книгу\
.Activate '\Показать ячейку с ошибкой\
.Value = InputBox("Значение в ячейке не является числом. Введите новое в этом окне и нажмите Enter" & Chr(10) & Chr(10) & "При нажатии Cancel значение она будет пропущена", "Ошибка в ячейке " & .Address, .Value)
ThisWorkbook.Activate '\На стартовую книгу\
Application.ScreenUpdating = False
GoTo L1
End If
End With
Workbooks(NameBook).Close SaveChanges:=False '\Не сохранять, закрыть\
ErrMsgAndQuit:
If Err = 9 Then MsgBox Prompt:="Книга указана неправильно", Buttons:=vbCritical, Title:="Ошибка"
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
PathAndNameBook = Empty
NameBook = Empty
Set BookList = Nothing
Set objCell = Nothing
Erase RangeValue()
k = 0
q = 0
End Function
Public Function LoadButtonOpenBookAndCalculate() '\Добавить кнопку на панель инструментов "Форматирование"\
Dim pushLC As Object '\Объявляем переменную нового инструмента на панели задач\
Set pushLC = Application.CommandBars("Formatting").Controls.Add(Type:=msoControlButton, Temporary:=True) '\Добавляем новый элемент на панель\
With pushLC '\Настраиваем элемент\
.BeginGroup = True '\Добавить разделитель\
.Style = msoButtonAutomatic '\Кнопка содержит рисунок\
.Caption = "Открыть книгу для калькуляции: сложить текущие значения с значениями из выбранной книги" '\Имя/всплывающая подсказка\
.OnAction = "OpenBookForCalcSumValue" '\Назначить макрос\
.FaceId = 283 '\Иконка калькулятора\
End With
Set pushLC = Nothing '\Очищаем память\
End Function
'==========================================================================
' Открыть книгу для добавления из нее значений к текущим, путем сложения
' Last Update: 10/06/2011
'==========================================================================
Public Function OpenBookForCalcSumValue()
Dim PathAndNameBook As String, NameBook As String, BookList As Object, RangeValue(4) As Object, objCell As Object, k As Integer, q As Byte
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Do
k = k + 1 '\Наращиваем значение переменной\
Loop Until Mid(PathAndNameBook, Len(PathAndNameBook) - k, 1) = "\" '\Первый слеш справа\
NameBook = Right(PathAndNameBook, k) '\Получить только имя книги\
Set BookList = Workbooks(NameBook).Sheets("р6.5") '\Первый лист открытой книги\
ThisWorkbook.Activate '\Сделать активной текущую книгу\
With BookList '\C листом открытой книги\
Set RangeValue(0) = .Range("G10:W446")
End With
For q = 0 To 0 '\По массиву с диапазонами данных\
For Each objCell In RangeValue(q) '\Ячейка диапазона\
With objCell '\С ячейкой\
If .HasFormula = False And Range(.Address).HasFormula = False Then '\Если ячейки не содержат формул\
If .Locked = False And Range(.Address).Locked = False Then '\Ячейка не защищена?\
L1:
If IsNumeric(.Value) = True Then '\Число?\
Range(.Address) = Range(.Address) + .Value '\Перенести данные сложив их с ячейкой-получателем\
Application.ScreenUpdating = True
Workbooks(NameBook).Activate '\Показать книгу\
.Activate '\Показать ячейку с ошибкой\
.Value = InputBox("Значение в ячейке не является числом. Введите новое в этом окне и нажмите Enter" & Chr(10) & Chr(10) & "При нажатии Cancel значение она будет пропущена", "Ошибка в ячейке " & .Address, .Value)
ThisWorkbook.Activate '\На стартовую книгу\
Application.ScreenUpdating = False
GoTo L1
End If
End With
Workbooks(NameBook).Close SaveChanges:=False '\Не сохранять, закрыть\
ErrMsgAndQuit:
If Err = 9 Then MsgBox Prompt:="Книга указана неправильно", Buttons:=vbCritical, Title:="Ошибка"
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
PathAndNameBook = Empty
NameBook = Empty
Set BookList = Nothing
Set objCell = Nothing
Erase RangeValue()
k = 0
q = 0
End Function
Public Function LoadButtonOpenBookAndCalculate() '\Добавить кнопку на панель инструментов "Форматирование"\
Dim pushLC As Object '\Объявляем переменную нового инструмента на панели задач\
Set pushLC = Application.CommandBars("Formatting").Controls.Add(Type:=msoControlButton, Temporary:=True) '\Добавляем новый элемент на панель\
With pushLC '\Настраиваем элемент\
.BeginGroup = True '\Добавить разделитель\
.Style = msoButtonAutomatic '\Кнопка содержит рисунок\
.Caption = "Открыть книгу для калькуляции: сложить текущие значения с значениями из выбранной книги" '\Имя/всплывающая подсказка\
.OnAction = "OpenBookForCalcSumValue" '\Назначить макрос\
.FaceId = 283 '\Иконка калькулятора\
End With
Set pushLC = Nothing '\Очищаем память\
End Function
'==========================================================================
' Открыть книгу для добавления из нее значений к текущим, путем сложения
' Last Update: 10/06/2011
'==========================================================================
Public Function OpenBookForCalcSumValue()
Dim PathAndNameBook As String, NameBook As String, BookList As Object, RangeValue(4) As Object, objCell As Object, k As Integer, q As Byte
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Do
k = k + 1 '\Наращиваем значение переменной\
Loop Until Mid(PathAndNameBook, Len(PathAndNameBook) - k, 1) = "\" '\Первый слеш справа\
NameBook = Right(PathAndNameBook, k) '\Получить только имя книги\
Set BookList = Workbooks(NameBook).Sheets("р6.5") '\Первый лист открытой книги\
ThisWorkbook.Activate '\Сделать активной текущую книгу\
With BookList '\C листом открытой книги\
Set RangeValue(0) = .Range("G10:W446")
End With
For q = 0 To 0 '\По массиву с диапазонами данных\
For Each objCell In RangeValue(q) '\Ячейка диапазона\
With objCell '\С ячейкой\
If .HasFormula = False And Range(.Address).HasFormula = False Then '\Если ячейки не содержат формул\
If .Locked = False And Range(.Address).Locked = False Then '\Ячейка не защищена?\
L1:
If IsNumeric(.Value) = True Then '\Число?\
Range(.Address) = Range(.Address) + .Value '\Перенести данные сложив их с ячейкой-получателем\
Application.ScreenUpdating = True
Workbooks(NameBook).Activate '\Показать книгу\
.Activate '\Показать ячейку с ошибкой\
.Value = InputBox("Значение в ячейке не является числом. Введите новое в этом окне и нажмите Enter" & Chr(10) & Chr(10) & "При нажатии Cancel значение она будет пропущена", "Ошибка в ячейке " & .Address, .Value)
ThisWorkbook.Activate '\На стартовую книгу\
Application.ScreenUpdating = False
GoTo L1
End If
End With
Workbooks(NameBook).Close SaveChanges:=False '\Не сохранять, закрыть\
ErrMsgAndQuit:
If Err = 9 Then MsgBox Prompt:="Книга указана неправильно", Buttons:=vbCritical, Title:="Ошибка"
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
PathAndNameBook = Empty
NameBook = Empty
Set BookList = Nothing
Set objCell = Nothing
Erase RangeValue()
k = 0
q = 0
End Function
SARAN, Вы нарушили, по крайней мере, четыре пункта Правил форума.
Прочитайте правила и создайте свою тему в разделе ВОПРОСЫ ПО VBA
Эту тему закрываю
SARAN, Вы нарушили, по крайней мере, четыре пункта Правил форума.
Прочитайте правила и создайте свою тему в разделе ВОПРОСЫ ПО VBA
Эту тему закрываю Pelena
Добрый день. Подскажите, как сделать макрос который сможет суммировать данные с разных книг (разного названия), но таблицы одинаковые. Суть проблемы такова, присылают 21 отчет, и мне нужно суммировать все в один. Заранее благодарен. Книгу с таблицами прикреплю.
[p.s.]
В том то и дело, книги будут называться по разному, листы в каждой книге называются одинаково, размер ячеек и расположение одинаково. А один файл, с точно такой же таблицей просто будет называться "общий отчет" или в том же духе. Нужно, что бы все данные из разных книг, лист "табличная форма", суммировались в книге "общий" в листе "табличная форма"
Добрый день. Подскажите, как сделать макрос который сможет суммировать данные с разных книг (разного названия), но таблицы одинаковые. Суть проблемы такова, присылают 21 отчет, и мне нужно суммировать все в один. Заранее благодарен. Книгу с таблицами прикреплю.
[p.s.]
В том то и дело, книги будут называться по разному, листы в каждой книге называются одинаково, размер ячеек и расположение одинаково. А один файл, с точно такой же таблицей просто будет называться "общий отчет" или в том же духе. Нужно, что бы все данные из разных книг, лист "табличная форма", суммировались в книге "общий" в листе "табличная форма" GSeReGa
Из Вашего примера - не понятно какие ячейки нужно суммировать - выделите эти ячейки каким - то одним цветом
Тогда все будет намного проще.
И еще как будут называться файлы всегда одинаково, или как макросу понять куда "тыкаться"?
- вложите парочку файлов для проверки.
Из Вашего примера - не понятно какие ячейки нужно суммировать - выделите эти ячейки каким - то одним цветом
Тогда все будет намного проще.
И еще как будут называться файлы всегда одинаково, или как макросу понять куда "тыкаться"?
- вложите парочку файлов для проверки. SLAVICK
Переносите все файлы в одну папку, затем циклом проходите по всем книгам в папке
и переносите нужные данные в сводный файл
Переносите все файлы в одну папку, затем циклом проходите по всем книгам в папке
и переносите нужные данные в сводный файл Kuzmich
Смотрите архив - там создал папку с файлами - туда нужно кидать файлы для обработки.
разархивируйте архив в любую папку.
В общем файле - кнопка нажимайте и ждите
С всех файлов данные просуммируются в общий файл
Смотрите архив - там создал папку с файлами - туда нужно кидать файлы для обработки.
разархивируйте архив в любую папку.
В общем файле - кнопка нажимайте и ждите
С всех файлов данные просуммируются в общий файл SLAVICK
Иногда все проще чем кажется с первого взгляда.
Читайте также: