[size=6][font=Arial][color=black]Имеется 2 таблицы, которые нужно стыковать раз в месяц по одному столбцу. В каждой из таблиц нужно убрать двойники по коду сбыт комп(первые столбцы в таблицах), просуммировав ячейки с объемами, добавив колонку количество лицевых счетов.А затем состыковать по этим ячейкам, так что совпавшие по коду строчки были напротив друг друга, а не совпавшие из обоих таблиц просто остались в своей половине. Для примера кусочек, на 1 и 2 листах имеющиеся таблицы, на 3 что должно примерно получится. Можно ли такие операции осуществить с помощью встроенных функций? или еще как то?
[size=6][font=Arial][color=black]Имеется 2 таблицы, которые нужно стыковать раз в месяц по одному столбцу. В каждой из таблиц нужно убрать двойники по коду сбыт комп(первые столбцы в таблицах), просуммировав ячейки с объемами, добавив колонку количество лицевых счетов.А затем состыковать по этим ячейкам, так что совпавшие по коду строчки были напротив друг друга, а не совпавшие из обоих таблиц просто остались в своей половине. Для примера кусочек, на 1 и 2 листах имеющиеся таблицы, на 3 что должно примерно получится. Можно ли такие операции осуществить с помощью встроенных функций? или еще как то? katrinazima
Судя по тому что я понял, в ЛИСТЕ1 в дублирующих колонках разница только в сумме! Поэтому тут, я сначала сделал бы в ЛИСТЕ1, в конце таблицы: напротив строки "A2=ЕСЛИ(A2=A3;U2+U3;U2)"!
После, скопировав в ЛИСТ3 первый столбец удалил бы дубликаты и подставил при помощи ВПР все значения, включая новый столбец, как сумму одинаковых значений!
После этого при помощи той же функции ВПР подставил бы значения из 2 листа, опираясь на значения из 1 столбца в ЛИСТ3!
katrinazima я бы делал так:
Судя по тому что я понял, в ЛИСТЕ1 в дублирующих колонках разница только в сумме! Поэтому тут, я сначала сделал бы в ЛИСТЕ1, в конце таблицы: напротив строки "A2=ЕСЛИ(A2=A3;U2+U3;U2)"!
После, скопировав в ЛИСТ3 первый столбец удалил бы дубликаты и подставил при помощи ВПР все значения, включая новый столбец, как сумму одинаковых значений!
После этого при помощи той же функции ВПР подставил бы значения из 2 листа, опираясь на значения из 1 столбца в ЛИСТ3!Baykal
Сообщение отредактировал Baykal - Пятница, 11.07.2014, 16:03
Baykal, лицевых счетов с совпадающим кодом может быть и 15, и 8 и так не получится "A2=ЕСЛИ(A2=A3;U2+U3;U2)"! . Всего лицевых счетов порядка 18000, у меня даже комп (на работе ) при растягивании формулы ВПР иногда висит даже , ее приходится применять дважды, чтобы вытащить нехватающие из 2 листа(в нем тоже могут быть лицевые которых нет в первом) в третьем. Я до этого примерно так и делала, только суммировала в Visual FoxPro, это очень долго и легко накосячить. Подумала, что может у Excel есть возможности сделать, как то по другому. Спасибо за ответ!
Baykal, лицевых счетов с совпадающим кодом может быть и 15, и 8 и так не получится "A2=ЕСЛИ(A2=A3;U2+U3;U2)"! . Всего лицевых счетов порядка 18000, у меня даже комп (на работе ) при растягивании формулы ВПР иногда висит даже , ее приходится применять дважды, чтобы вытащить нехватающие из 2 листа(в нем тоже могут быть лицевые которых нет в первом) в третьем. Я до этого примерно так и делала, только суммировала в Visual FoxPro, это очень долго и легко накосячить. Подумала, что может у Excel есть возможности сделать, как то по другому. Спасибо за ответ! katrinazima
Попытаюсь разбить задачу на части, т.к. не понятно видимо. У меня есть 2 таблицы, в первых столбцах есть так называемый код сбыт компании(это то по чему они совпадают). Мне нужно с листа2 добавить строчки по этому коду в лист1. С помощью форума я смогла сделать вот так: [vba]
НО, с листа 2 нужно еще добавить в лист1 те строчки с кодами сбыт компании, которых нет на листе1. И это еще не все. До перетаскивания данных, на обоих листах нужно убрать двойные коды(та же первая колонка, и их бывает разное количество, но не больше 100 точно), просуммировав колонку объем. Это нужно чтобы сравнить объемы потребления по одному и тому же коду.
Попытаюсь разбить задачу на части, т.к. не понятно видимо. У меня есть 2 таблицы, в первых столбцах есть так называемый код сбыт компании(это то по чему они совпадают). Мне нужно с листа2 добавить строчки по этому коду в лист1. С помощью форума я смогла сделать вот так: [vba]
НО, с листа 2 нужно еще добавить в лист1 те строчки с кодами сбыт компании, которых нет на листе1. И это еще не все. До перетаскивания данных, на обоих листах нужно убрать двойные коды(та же первая колонка, и их бывает разное количество, но не больше 100 точно), просуммировав колонку объем. Это нужно чтобы сравнить объемы потребления по одному и тому же коду.katrinazima
В колонку "Объем (в т.ч. неуч. потери)(РГЭС)" вставляет с листа2 не то что нужно, наверно из-за того что не убраны двойные коды в листах. Не разбираюсь вообще
В колонку "Объем (в т.ч. неуч. потери)(РГЭС)" вставляет с листа2 не то что нужно, наверно из-за того что не убраны двойные коды в листах. Не разбираюсь вообщеkatrinazima
Попробую 1 часть задачи описать. На листе "с двойниками" -исходные данные. Нужно, чтобы в итогом виде в первой колонке код не повторялся, т.е. если А1=А2=...Аn, то ячейки столбца "Объем (в т.ч. неуч. потери)(РГЭС)" S1+S2+...Sn, а остальные ячейки в строчке можно взять с любой строчки из перечня n(хотя желательно, где больше объем). В итоге должно получится 1 строчка(вместо n строчек) с одним кодом, суммарным объемом(в ячейке S), добавить колонку Кол-во ЛС(z), указать в ней число n(а если строчка одна, то 1) и всеми остальными данными. В прилагаемом файле выделены изменения Очень надеюсь на Вашу помощь
Попробую 1 часть задачи описать. На листе "с двойниками" -исходные данные. Нужно, чтобы в итогом виде в первой колонке код не повторялся, т.е. если А1=А2=...Аn, то ячейки столбца "Объем (в т.ч. неуч. потери)(РГЭС)" S1+S2+...Sn, а остальные ячейки в строчке можно взять с любой строчки из перечня n(хотя желательно, где больше объем). В итоге должно получится 1 строчка(вместо n строчек) с одним кодом, суммарным объемом(в ячейке S), добавить колонку Кол-во ЛС(z), указать в ней число n(а если строчка одна, то 1) и всеми остальными данными. В прилагаемом файле выделены изменения Очень надеюсь на Вашу помощьkatrinazima
Двойные коды можно убрать, с суммированием количества. Но что делать с другими полями - inn(ЯСК) там разные, разные наименования, адреса тоже вероятно могут отличаться. Это на первом листе. А на втором там вообще чуть ли не все поля разные. Может тогда со второго листа брать только общее потребление по точке? Это проще - можно его запомнить в словаре по каждой точке, потом подгрузить на первый лист (сперва там убрать дубли).
Или может делать иначе - просто в новый файл/лист вывести сверку потребления по точкам. А эти два листа вообще не менять. И кстати это легко сделать вручную и формулами - сперва в копии убрать дубли по точкам, затем СУММПРОИЗВ() или СУММЕСЛИ() по этим двум исходным файлам. Ну или две сводные, затем сравниваем хоть ВПР() эти две сводные.
P.S. Перечитал сообщение №3 - ну может тогда сделать макросом вывод в новый файл: столбец всех точек (из обоих файлов), а рядом столбцы с суммами. Где-то будут пустые ячейки, если не совпадают точки между файлами. И код будет довольно простой - можно собрать 3 словаря, один общий точек, и два с суммами по листу. Далее цикл по общему, по нему извлекаем суммы из двух других.
Вот код для файла 1229640.xls: [vba]
Код
Option Explicit
Sub sverka2() Dim a(), aa(), i&, d1 As Object, d2 As Object, dAll As Object, k
Set d1 = CreateObject("Scripting.Dictionary"): d1.comparemode = 1 Set d2 = CreateObject("Scripting.Dictionary"): d2.comparemode = 1 Set dAll = CreateObject("Scripting.Dictionary"): dAll.comparemode = 1
a = [Лист1!A1].CurrentRegion.Columns(1).Value aa = [Лист1!A1].CurrentRegion.Columns(21).Value For i = 2 To UBound(a) dAll.Item(a(i, 1)) = 0& d1.Item(a(i, 1)) = d1.Item(a(i, 1)) + aa(i, 1) Next
a = [Лист2!A1].CurrentRegion.Columns(1).Value aa = [Лист2!A1].CurrentRegion.Columns(19).Value For i = 2 To UBound(a) dAll.Item(a(i, 1)) = 0& d2.Item(a(i, 1)) = d2.Item(a(i, 1)) + aa(i, 1) Next
ReDim a(1 To dAll.Count, 1 To 3) i = 0 For Each k In dAll.keys i = i + 1 a(i, 1) = k a(i, 2) = d1.Item(k) a(i, 3) = d2.Item(k) Next
Workbooks.Add(1).Sheets(1).[a1].Resize(i, 3) = a End Sub
[/vba] Можно коды отсортировать - но это несложно сделать и вручную на листе. Можно массив расширить и тут же рядом писать разницу двух сумм. Далее можно поставить фильтр или отсортировать по этому полю особо злостных "несовпаденцев".
Двойные коды можно убрать, с суммированием количества. Но что делать с другими полями - inn(ЯСК) там разные, разные наименования, адреса тоже вероятно могут отличаться. Это на первом листе. А на втором там вообще чуть ли не все поля разные. Может тогда со второго листа брать только общее потребление по точке? Это проще - можно его запомнить в словаре по каждой точке, потом подгрузить на первый лист (сперва там убрать дубли).
Или может делать иначе - просто в новый файл/лист вывести сверку потребления по точкам. А эти два листа вообще не менять. И кстати это легко сделать вручную и формулами - сперва в копии убрать дубли по точкам, затем СУММПРОИЗВ() или СУММЕСЛИ() по этим двум исходным файлам. Ну или две сводные, затем сравниваем хоть ВПР() эти две сводные.
P.S. Перечитал сообщение №3 - ну может тогда сделать макросом вывод в новый файл: столбец всех точек (из обоих файлов), а рядом столбцы с суммами. Где-то будут пустые ячейки, если не совпадают точки между файлами. И код будет довольно простой - можно собрать 3 словаря, один общий точек, и два с суммами по листу. Далее цикл по общему, по нему извлекаем суммы из двух других.
Вот код для файла 1229640.xls: [vba]
Код
Option Explicit
Sub sverka2() Dim a(), aa(), i&, d1 As Object, d2 As Object, dAll As Object, k
Set d1 = CreateObject("Scripting.Dictionary"): d1.comparemode = 1 Set d2 = CreateObject("Scripting.Dictionary"): d2.comparemode = 1 Set dAll = CreateObject("Scripting.Dictionary"): dAll.comparemode = 1
a = [Лист1!A1].CurrentRegion.Columns(1).Value aa = [Лист1!A1].CurrentRegion.Columns(21).Value For i = 2 To UBound(a) dAll.Item(a(i, 1)) = 0& d1.Item(a(i, 1)) = d1.Item(a(i, 1)) + aa(i, 1) Next
a = [Лист2!A1].CurrentRegion.Columns(1).Value aa = [Лист2!A1].CurrentRegion.Columns(19).Value For i = 2 To UBound(a) dAll.Item(a(i, 1)) = 0& d2.Item(a(i, 1)) = d2.Item(a(i, 1)) + aa(i, 1) Next
ReDim a(1 To dAll.Count, 1 To 3) i = 0 For Each k In dAll.keys i = i + 1 a(i, 1) = k a(i, 2) = d1.Item(k) a(i, 3) = d2.Item(k) Next
Workbooks.Add(1).Sheets(1).[a1].Resize(i, 3) = a End Sub
[/vba] Можно коды отсортировать - но это несложно сделать и вручную на листе. Можно массив расширить и тут же рядом писать разницу двух сумм. Далее можно поставить фильтр или отсортировать по этому полю особо злостных "несовпаденцев".Hugo
Public Sub RefreshData() 'Created using add-in ActiveTables Dim strConnection As String Dim strSQL As String strConnection = iif(Val(Application.Version) < 12,"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';","OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';") strSQL = "SELECT [Код точки уч#сбыт#комп#(РГЭС)], SUM([Объем (в т#ч# неуч# потери)(РГЭС)]) AS Объем, count(*) AS Колво, FIRST([Код точки учета (РГЭС)]), FIRST([ПС (РГЭС)]), FIRST([СШ (РГЭС)]), FIRST([Фидер (РГЭС)]), FIRST([КТП (РГЭС)]), FIRST([Фидер 0#4(РГЭС)]), FIRST([Наименование точки учета(РГЭС)]), FIRST([Наименование потребителя (РГЭС)]), FIRST([Уровень напряжения(РГЭС)]), FIRST([Номер счетчика(РГЭС)]), FIRST([Тип счетчика(РГЭС)]), FIRST([Код типа счетчика(РГЭС)]), FIRST([Новое показание(РГЭС)]), FIRST([Старое показание(РГЭС)]), FIRST([Расчетный коэф#(РГЭС)]), FIRST([Процент потерь(РГЭС)]), FIRST([Расчетные потери(РГЭС)]), FIRST([РЭС(РГЭС)]), FIRST([Неуч#потери(РГЭС)]), FIRST([МОП/Норм(РГЭС)]), FIRST([ИНН(РГЭС)]), FIRST([Бюджет(РГЭС)]), FIRST([Подр# ЯСК(РГЭС)]) FROM [с двойниками$] GROUP BY [Код точки уч#сбыт#комп#(РГЭС)] ORDER BY [Код точки уч#сбыт#комп#(РГЭС)], SUM([Объем (в т#ч# неуч# потери)(РГЭС)]) DESC " With ThisWorkbook.ActiveSheet .UsedRange.Clear With .QueryTables.Add(strConnection, .Range("A1"), strSQL) .Refresh False .Delete End With End With End Sub
[/vba]
[vba]
Код
Public Sub RefreshData() 'Created using add-in ActiveTables Dim strConnection As String Dim strSQL As String strConnection = iif(Val(Application.Version) < 12,"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=3';","OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=3';") strSQL = "SELECT [Код точки уч#сбыт#комп#(РГЭС)], SUM([Объем (в т#ч# неуч# потери)(РГЭС)]) AS Объем, count(*) AS Колво, FIRST([Код точки учета (РГЭС)]), FIRST([ПС (РГЭС)]), FIRST([СШ (РГЭС)]), FIRST([Фидер (РГЭС)]), FIRST([КТП (РГЭС)]), FIRST([Фидер 0#4(РГЭС)]), FIRST([Наименование точки учета(РГЭС)]), FIRST([Наименование потребителя (РГЭС)]), FIRST([Уровень напряжения(РГЭС)]), FIRST([Номер счетчика(РГЭС)]), FIRST([Тип счетчика(РГЭС)]), FIRST([Код типа счетчика(РГЭС)]), FIRST([Новое показание(РГЭС)]), FIRST([Старое показание(РГЭС)]), FIRST([Расчетный коэф#(РГЭС)]), FIRST([Процент потерь(РГЭС)]), FIRST([Расчетные потери(РГЭС)]), FIRST([РЭС(РГЭС)]), FIRST([Неуч#потери(РГЭС)]), FIRST([МОП/Норм(РГЭС)]), FIRST([ИНН(РГЭС)]), FIRST([Бюджет(РГЭС)]), FIRST([Подр# ЯСК(РГЭС)]) FROM [с двойниками$] GROUP BY [Код точки уч#сбыт#комп#(РГЭС)] ORDER BY [Код точки уч#сбыт#комп#(РГЭС)], SUM([Объем (в т#ч# неуч# потери)(РГЭС)]) DESC " With ThisWorkbook.ActiveSheet .UsedRange.Clear With .QueryTables.Add(strConnection, .Range("A1"), strSQL) .Refresh False .Delete End With End With End Sub
Доброе утро! Огромное спасибо, что уделили внимание моей проблеме!
Pelena, я попробовала применить макрос и он ругается, не понимаю почему, вот на эту строчку: [vba]
Код
rst.Open "SELECT a.F1 FROM [Лист1$A2:A" & c1 & "] AS a INNER JOIN [Лист2$A2:A" & c2 & "] AS b ON a.F1 = b.F1 GROUP BY a.F1 ORDER BY Val(a.F1)" _ , "Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 12.0;HDR=NO'"
[/vba]
PowerBoy, к какому файлу код?он мне все таблицы на листах потер. Я что-то не так делаю?
Hugo, некоторые столбцы я могу удалить из обоих таблиц, которые не обязательны для сверки, но такие как адрес и наименование обязательны(я удалю тогда их). А с адрес на одном коде не может быть разный, поэтому без разницы какую из строчек брать. Наименования могут быть разные, но человек за которым закреплен участок знает, что по такому то адресу не один абонент(мы создаем колонку с количеством лицевых для подтверждения). Вот этот вариант то, что нужно, как я поняла:
ЦитатаHugo
P.S. Перечитал сообщение №3 - ну может тогда сделать макросом вывод в новый файл: столбец всех точек (из обоих файлов), а рядом столбцы с суммами. Где-то будут пустые ячейки, если не совпадают точки между файлами. И код будет довольно простой - можно собрать 3 словаря, один общий точек, и два с суммами по листу. Далее цикл по общему, по нему извлекаем суммы из двух других.
Именно так и нужно, чтобы были ВСЕ ПРОСУММИРОВАННЫЕ строчки из обоих файлов(там где нет совпадений пустота, или нет данных) объем складывать, а в остальные текст из верхней(к примеру, а так из любой). Столбец с кодами должен быть один,а все остальное - шапка из обоих файлов. Найти разницу и отсортировать, это я в ручную сделаю(мне их потом по разным листах разносить надо будет, где коды не совпали на отдельную). Ваш код оставляет только 3 колонки(A,N,AD), а как мне все остальные добавить? и шапку добавить? В файле убрала, что разрешили, остальное никак нельзя(этой информацией пользуются)
Доброе утро! Огромное спасибо, что уделили внимание моей проблеме!
Pelena, я попробовала применить макрос и он ругается, не понимаю почему, вот на эту строчку: [vba]
Код
rst.Open "SELECT a.F1 FROM [Лист1$A2:A" & c1 & "] AS a INNER JOIN [Лист2$A2:A" & c2 & "] AS b ON a.F1 = b.F1 GROUP BY a.F1 ORDER BY Val(a.F1)" _ , "Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 12.0;HDR=NO'"
[/vba]
PowerBoy, к какому файлу код?он мне все таблицы на листах потер. Я что-то не так делаю?
Hugo, некоторые столбцы я могу удалить из обоих таблиц, которые не обязательны для сверки, но такие как адрес и наименование обязательны(я удалю тогда их). А с адрес на одном коде не может быть разный, поэтому без разницы какую из строчек брать. Наименования могут быть разные, но человек за которым закреплен участок знает, что по такому то адресу не один абонент(мы создаем колонку с количеством лицевых для подтверждения). Вот этот вариант то, что нужно, как я поняла:
ЦитатаHugo
P.S. Перечитал сообщение №3 - ну может тогда сделать макросом вывод в новый файл: столбец всех точек (из обоих файлов), а рядом столбцы с суммами. Где-то будут пустые ячейки, если не совпадают точки между файлами. И код будет довольно простой - можно собрать 3 словаря, один общий точек, и два с суммами по листу. Далее цикл по общему, по нему извлекаем суммы из двух других.
Именно так и нужно, чтобы были ВСЕ ПРОСУММИРОВАННЫЕ строчки из обоих файлов(там где нет совпадений пустота, или нет данных) объем складывать, а в остальные текст из верхней(к примеру, а так из любой). Столбец с кодами должен быть один,а все остальное - шапка из обоих файлов. Найти разницу и отсортировать, это я в ручную сделаю(мне их потом по разным листах разносить надо будет, где коды не совпали на отдельную). Ваш код оставляет только 3 колонки(A,N,AD), а как мне все остальные добавить? и шапку добавить? В файле убрала, что разрешили, остальное никак нельзя(этой информацией пользуются)katrinazima
rst.Open "SELECT a.F1 FROM [Лист1$A2:A" & c1 & "] AS a INNER JOIN [Лист2$A2:A" & c2 & "] AS b ON a.F1 = b.F1 GROUP BY a.F1 ORDER BY Val(a.F1)" _ , "Provider='Microsoft.ACE.OLEDB.4.0';Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 4.0;HDR=NO'"
[/vba] тоже самое
Pelena, заменила вот так: [vba]
Код
rst.Open "SELECT a.F1 FROM [Лист1$A2:A" & c1 & "] AS a INNER JOIN [Лист2$A2:A" & c2 & "] AS b ON a.F1 = b.F1 GROUP BY a.F1 ORDER BY Val(a.F1)" _ , "Provider='Microsoft.ACE.OLEDB.4.0';Data Source='" & ThisWorkbook.FullName & "';Extended Properties='Excel 4.0;HDR=NO'"
Pelena, похоже работает, как надо. Вы не представляете,как облегчили мне работу, не хватает слов выразить благодарность! Кодом Hugo тоже пользуюсь, проще, чем в другие программы куда то кидать И всем остальным огромное спасибо!
Pelena, похоже работает, как надо. Вы не представляете,как облегчили мне работу, не хватает слов выразить благодарность! Кодом Hugo тоже пользуюсь, проще, чем в другие программы куда то кидать И всем остальным огромное спасибо!katrinazima
Pelena, подскажите пожалуйста, как работает макрос, он не всегда выдает что нужно. Например, на листе2 имеется строчка с кодом, таким же как на первом, а макрос не вытаскивает всю строчку(только код) и трет его на Листе2. С чем это связано?
Pelena, подскажите пожалуйста, как работает макрос, он не всегда выдает что нужно. Например, на листе2 имеется строчка с кодом, таким же как на первом, а макрос не вытаскивает всю строчку(только код) и трет его на Листе2. С чем это связано?katrinazima
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
