Страницы: 1
RSS
Объединить две таблицы с добавлением новых значений и заменой по условию
 
Здравствуйте.
Помогите сформировать общую таблицу на основании двух одинаковых по размеру (имена рядов и столбцов), но разных по содержанию таблиц, находящихся в разных файлах.
Объединение таблиц должно решать следующие задачи:
1) дополнить недостающие данные из одной таблицы в другую;
2) если в совпадающей ячейке уже имеются данные, но они меньше тех, которые копируются, то происходит замена, если больше, то замена не происходит.
Если возможно, то предусмотреть когда несколько рядов или столбцов не совпадают, то объединение данных таблиц происходит путем сопоставления названия имен в строках и столбцах.
Для Excell 2003
 
Максим, если всё выглядит, как в Вашем файле, то вот:
Код
=ЕСЛИ(МАКС(C8;C20)=0;"";МАКС(C8;C20))
 
Aleksei_Zhigulin, Спасибо. Считает если обе таблицы находятся на одном листе одного файла, но в условии
Цитата
Максим написал:
разных по содержанию таблиц, находящихся в разных файлах.
и в примере написал: файл 1, файл 2, файл 3
В итоге: не работает.
Кроме того, если это возможно, хотелось, что бы происходила проверка (от случайной ошибки) имен рядов и столбцов при объединении, т.е. из ячейки "ААА(строка)-111а(столбец)" в ячейку "ААА(строка)-111а(столбец)"
Изменено: Максим - 21.01.2019 16:30:35
 
Цитата
Максим написал:
В итоге: не работает.
Вы это проверяли? Специально, на всякий пожарный, посмотрел перед выкладкой - с закрытыми файлами формула также функционирует.
Цитата
Максим написал:
Считает если обе таблицы находятся на одном листе одного файла
С этим сложно спорить. :)  Вот чисто из любопытства - какую формулу Вы бы хотели увидеть? Которая ссылается на конкретные файлы на Вашем компьютере?  :) Формула сделана на Вашем примере, подразумевается, что вместо C8 будет ссылка на ячейку файла №1, вместо C20 - ссылка на аналогичную ячейку файла №2  и т.д. Странно, что приходится это объяснять.
Изменено: Aleksei_Zhigulin - 21.01.2019 18:29:31
 
Цитата
Aleksei_Zhigulin написал:
Вы это проверяли?
Да, проверял, ничего не происходит. Может, что-то не так? Вот формула:
=ЕСЛИ(МАКС('[2018 до.xls]Лист1'!$D$5;'[2018 после.xls]Лист1'!$D$5)=0;"";МАКС('[2018 до.xls]Лист1'!$D$5;'[2018 после.xls]Лист1'!$D$5))
 
Aleksei_Zhigulin, извините, разобрался. Дело в том, что при копировании Вашей формулы и замены ссылок на свои, по-умолчанию, ссылка на ячейку присваивается "жесткой", т.е. $D$5 и когда начинаешь её растягивать на весь лист, то ничего не происходит, т.к. первая ячейка пустая. Убрал знаки $ и всё заработало. Спасибо.

P.S. И всё же можно, что бы происходила сверка имён рядов и строк при объединении (поиск совпадающих координат "имя строки" x "имя столбца" и если совпадают, то объединяет) от случайного человеческого фактора, когда добавится новая строка или столбец в один из файлов. В файле "Пример" имена шапки указаны.
Изменено: Максим - 22.01.2019 10:44:52
 
Цитата
Максим написал:
И всё же можно, что бы происходила сверка имён рядов и строк при объединении
Я бы посоветовал Вам использовать ИНДЕКС+ПОИСКПОЗ, но т.к. в первой строке у Вас объединённые ячейки, то понадобится СМЕЩ, а эта функция не работает с закрытыми файлами. Другое формульное решение мне в голову не приходит. Будь у Вас более современная версия Excel, решал бы в Power Query.
 
Aleksei_Zhigulin,
Цитата
Aleksei_Zhigulin написал:
эта функция не работает с закрытыми файлами.
В этом проблемы нет. Файлы всё равно открываются. Так что если есть возможность решения, то напишите пожалуйста формулу, которая будет работать с открытыми исходными файлами.
 
Попробовал сам составить формулу:
Код
=ЕСЛИ(МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+
(C$31="а"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+
(C$31="а")))=0;"";МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+
(C$31="а"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+(C$31="а"))))

но что-то упустил. Происходит смещение влево на один столбец и выводит значения только по столбцу "б".
 
Максим, нет, я Вас обманул, СМЕЩ не поможет. Могу предложить в исходных данных привести в удобный формат шапку и использовать формулу типа этой (формула массива):
Код
=ЕСЛИ(ИНДЕКС($B$6:$J$13;ПОИСКПОЗ($V32;$B$6:$B$13;0);ПОИСКПОЗ(W$31&W$30;$B$7:$J$7&$B$6:$J$6;0))=0;"";
      ИНДЕКС($B$6:$J$13;ПОИСКПОЗ($V32;$B$6:$B$13;0);ПОИСКПОЗ(W$31&W$30;$B$7:$J$7&$B$6:$J$6;0)))
 
Aleksei_Zhigulin, не разобрался как нужно привести формат шапки, чтобы работала Ваша последняя формула?

P.S. Немного доделал свою формулу: подправив её отдельно для столбца "а" и столбца "б". Теперь если растягивать формулу сразу двух столбцов на весь лист - формула адекватно работает.
Одного не могу понять (прошу объяснить и проверить формулу на наличие "подводных камней") почему:
1) для столбца "а" в формуле, при ссылке на ячейку с именем столбца "а", можно после знака равно указывать в кавычках любое имя, но не тождественное "а":
Код
=ЕСЛИ(МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+(C$31="б"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+(C$31="б")))=0;"";МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+(C$31="б"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+(C$31="б"))))

2) в формуле же для столбца "б", при ссылке на ячейку с именем столбца "б", нужно после знака равно указывать обязательно "б":
Код
=ЕСЛИ(МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+(D$31="б"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+(D$31="б")))=0;"";МАКС(ИНДЕКС($C$8:$J$13;ПОИСКПОЗ($B32;$B$20:$B$25);ПОИСКПОЗ(C$30;$C$6:$J$6;)+(D$31="б"));ИНДЕКС($C$20:$J$25;ПОИСКПОЗ($B32;$B$8:$B$13);ПОИСКПОЗ(C$30;$C$18:$J$18;)+(D$31="б"))))

3) при схожести формул (буква в ссылке C$31 естественно меняется при растягивании) растягивать/копировать их в другие ячейки нужно совместно. Иначе будет работать только та, которую растягиваешь.
 
Цитата
Максим написал:
не разобрался как нужно привести формат шапки, чтобы работала Ваша последняя формула?
В файле во вложении работающая формула при изменённой шапке. Если такой вариант Вас не устроит, по этой задаче больше ничем помочь не могу, уж извините.
Код
=ЕСЛИ(МАКС(ИНДЕКС($B$6:$J$13;ПОИСКПОЗ($V32;$B$6:$B$13;0);ПОИСКПОЗ(W$31&W$30;$B$7:$J$7&$B$6:$J$6;0));
           ИНДЕКС($B$18:$J$25;ПОИСКПОЗ($V32;$B$18:$B$25;0);ПОИСКПОЗ(W$31&W$30;$B$19:$J$19&$B$18:$J$18;0)))=0;"";
      МАКС(ИНДЕКС($B$6:$J$13;ПОИСКПОЗ($V32;$B$6:$B$13;0);ПОИСКПОЗ(W$31&W$30;$B$7:$J$7&$B$6:$J$6;0));
           ИНДЕКС($B$18:$J$25;ПОИСКПОЗ($V32;$B$18:$B$25;0);ПОИСКПОЗ(W$31&W$30;$B$19:$J$19&$B$18:$J$18;0))))
 
Aleksei_Zhigulin, Спасибо. Вопрос можно считать закрытым.
Страницы: 1
Наверх