Доброго времени суток уважаемые форумчане. Прошу помощи в седующей проблеме: Есть книга excel в которой есть три листа - на листах есть данные, имеющие одинаковую структуру, но отличающиеся данные. Необходимо в макросе собрать данные листов 1 и 2 отобранные по столбцу А листа Серка дабы получить следующий результат:
1 результат = (лист1столбец6+лист2столбец6+листСверкастолбец6) - (лист1столбец7+лист2столбец7+листСверкастолбец7) 2 результат = (лист1столбец8+лист2столбец8+листСверкастолбец8) - (лист1столбец9+лист2столбец9+листСверкастолбец9)
произвести вычисления в макросе и вставить полученные данные в Лист Сверка в столбцы K и L построчно.
Доброго времени суток уважаемые форумчане. Прошу помощи в седующей проблеме: Есть книга excel в которой есть три листа - на листах есть данные, имеющие одинаковую структуру, но отличающиеся данные. Необходимо в макросе собрать данные листов 1 и 2 отобранные по столбцу А листа Серка дабы получить следующий результат:
1 результат = (лист1столбец6+лист2столбец6+листСверкастолбец6) - (лист1столбец7+лист2столбец7+листСверкастолбец7) 2 результат = (лист1столбец8+лист2столбец8+листСверкастолбец8) - (лист1столбец9+лист2столбец9+листСверкастолбец9)
произвести вычисления в макросе и вставить полученные данные в Лист Сверка в столбцы K и L построчно.
К сожалению нет - пробовал - это малая часть файла - кажый лист имеет порядка 25-30 тысяч строк - расчет формул будет о-о-о-о-чень долгим. Тем более там нужно будет использовать ВПР для поиска уникальных значений - что довольно не быстро!
Quote (Serge_007)
не пойдёт
К сожалению нет - пробовал - это малая часть файла - кажый лист имеет порядка 25-30 тысяч строк - расчет формул будет о-о-о-о-чень долгим. Тем более там нужно будет использовать ВПР для поиска уникальных значений - что довольно не быстро!Ed_Vard
Сообщение отредактировал Ed_Vard - Среда, 11.05.2011, 16:26
Вот, наверное, не совсем то (или совсем не то), что нужно, но код получился интересный, как мне кажется: суммируем разность столбцов 6, 7 и 8, 9 для уникальных по ст. А по всем листам.
Code
Sub ertert() Dim x, i&, wshList(), bu, t() wshList = Array("сверка", "Лист1", "Лист2") With CreateObject("Scripting.Dictionary") .CompareMode = 1 For Each bu In wshList x = Sheets(bu).Range("A2:I" & Sheets(bu).Cells(Rows.Count, 1).End(xlUp).Row).Value For i = 1 To UBound(x) If Not .Exists(x(i, 1)) Then .Item(x(i, 1)) = Array(x(i, 6) - x(i, 7), x(i, 8) - x(i, 9)) Else t = .Item(x(i, 1)) t(0) = t(0) + x(i, 6) - x(i, 7) t(1) = t(1) + x(i, 8) - x(i, 9) .Item(x(i, 1)) = t End If Next Next bu x = Application.Transpose(Application.Transpose(.Items)) Sheets("сверка").[l2:m2].Resize(.Count).Value = x x = Application.Transpose(.Keys) Sheets("сверка").[k2].Resize(.Count).Value = x End With End Sub
Вот, наверное, не совсем то (или совсем не то), что нужно, но код получился интересный, как мне кажется: суммируем разность столбцов 6, 7 и 8, 9 для уникальных по ст. А по всем листам.
Code
Sub ertert() Dim x, i&, wshList(), bu, t() wshList = Array("сверка", "Лист1", "Лист2") With CreateObject("Scripting.Dictionary") .CompareMode = 1 For Each bu In wshList x = Sheets(bu).Range("A2:I" & Sheets(bu).Cells(Rows.Count, 1).End(xlUp).Row).Value For i = 1 To UBound(x) If Not .Exists(x(i, 1)) Then .Item(x(i, 1)) = Array(x(i, 6) - x(i, 7), x(i, 8) - x(i, 9)) Else t = .Item(x(i, 1)) t(0) = t(0) + x(i, 6) - x(i, 7) t(1) = t(1) + x(i, 8) - x(i, 9) .Item(x(i, 1)) = t End If Next Next bu x = Application.Transpose(Application.Transpose(.Items)) Sheets("сверка").[l2:m2].Resize(.Count).Value = x x = Application.Transpose(.Keys) Sheets("сверка").[k2].Resize(.Count).Value = x End With End Sub
Да - спасибо большое - весьма интересны код - у меня даже есть - куда его применить - но сюда он немного не подходит Немножечко не то, хотя я наверно сам виновант - не все описал - на листе Сверка я постоянно меняю данные подгружая их из файлов поэтому он имеет такую структуру и записываються в ячейки B2:I -макросом произвожу сцепление по строкам столбцов B:E:C в столбец А, а затем макросом удаляю дубликаты по столбцу А - все это на листе Сверка - т.е. дубликатов у меня на нем нет - это я сделать в примере забыл по запарке На листах Лист1 и Лист2 данные повторяються - но это одно и тоже значение - поэтому я и поставил одиковые суммы в столбцах поэтому если в столбце А несколько одиковых значений - сумировать не нужно все такие - нужно просто взять данные из любой строки или вернее считать их в Scripting.Dictionary как уникальные. В столбец К записывать ничего не нужно - достаточно будет - что это есть с столбце А Попытался описать в файле примера на листе сверка
Quote (nilem)
не совсем то
Да - спасибо большое - весьма интересны код - у меня даже есть - куда его применить - но сюда он немного не подходит Немножечко не то, хотя я наверно сам виновант - не все описал - на листе Сверка я постоянно меняю данные подгружая их из файлов поэтому он имеет такую структуру и записываються в ячейки B2:I -макросом произвожу сцепление по строкам столбцов B:E:C в столбец А, а затем макросом удаляю дубликаты по столбцу А - все это на листе Сверка - т.е. дубликатов у меня на нем нет - это я сделать в примере забыл по запарке На листах Лист1 и Лист2 данные повторяються - но это одно и тоже значение - поэтому я и поставил одиковые суммы в столбцах поэтому если в столбце А несколько одиковых значений - сумировать не нужно все такие - нужно просто взять данные из любой строки или вернее считать их в Scripting.Dictionary как уникальные. В столбец К записывать ничего не нужно - достаточно будет - что это есть с столбце А Попытался описать в файле примера на листе сверкаEd_Vard
На Лист1 и Лист2 повторы удалить можно? Тогда будет за один проход - удаляем дубликаты и складываем со значениями из Сверка. Значения по ст. А на всех листах всегда одни и те же? Вот так без удаления повторов. Предполагается, что повторы в ст. А отсортированы, т.е. идут один за другим.
Code
Sub ertert() Dim x, i&, wshList(), bu, t() wshList = Array("сверка", "Лист1", "Лист2") With CreateObject("Scripting.Dictionary") .CompareMode = 1 For Each bu In wshList x = Sheets(bu).Range("A1").CurrentRegion.Value For i = 2 To UBound(x) If x(i, 1) <> x(i - 1, 1) Then If Not .Exists(x(i, 1)) Then .Item(x(i, 1)) = Array(x(i, 6) - x(i, 7), x(i, 8) - x(i, 9)) Else t = .Item(x(i, 1)) t(0) = t(0) + x(i, 6) - x(i, 7) t(1) = t(1) + x(i, 8) - x(i, 9) .Item(x(i, 1)) = t End If End If Next Next bu x = Application.Transpose(Application.Transpose(.Items)) Sheets("сверка").[k2:l2].Resize(.Count).Value = x End With End Sub
На Лист1 и Лист2 повторы удалить можно? Тогда будет за один проход - удаляем дубликаты и складываем со значениями из Сверка. Значения по ст. А на всех листах всегда одни и те же? Вот так без удаления повторов. Предполагается, что повторы в ст. А отсортированы, т.е. идут один за другим.
Code
Sub ertert() Dim x, i&, wshList(), bu, t() wshList = Array("сверка", "Лист1", "Лист2") With CreateObject("Scripting.Dictionary") .CompareMode = 1 For Each bu In wshList x = Sheets(bu).Range("A1").CurrentRegion.Value For i = 2 To UBound(x) If x(i, 1) <> x(i - 1, 1) Then If Not .Exists(x(i, 1)) Then .Item(x(i, 1)) = Array(x(i, 6) - x(i, 7), x(i, 8) - x(i, 9)) Else t = .Item(x(i, 1)) t(0) = t(0) + x(i, 6) - x(i, 7) t(1) = t(1) + x(i, 8) - x(i, 9) .Item(x(i, 1)) = t End If End If Next Next bu x = Application.Transpose(Application.Transpose(.Items)) Sheets("сверка").[k2:l2].Resize(.Count).Value = x End With End Sub
На Лист1 и Лист2 повторы удалить можно? Тогда будет за один проход - удаляем дубликаты и складываем со значениями из Сверка.
Удалять на этих листах дубликаты нельзя - т.к. они используються для визуального контроля!
Quote (nilem)
Значения по ст. А на всех листах всегда одни и те же?
Значение по Лист1 и Лист2 всегда одно и тоже по столбцу А - на листе сверка - они меняються в зависимости от того - какие файлы я загружаю - Но они всегда совпадают с какимито значениями по столбцу А листов 1 и 2.
Quote (nilem)
Вот так без удаления повторов. Предполагается, что повторы в ст. А отсортированы, т.е. идут один за другим.
Ваш вариант вроде в примере работает нормально - но при использовании в моем файле - переносит на лист сверка се разности по лист1 и лист2 Вот прикладываю пример файла с макросом - который дает результат - который мне нужен: Это вариация одного из Ваших макросов. В нем я не могу использовать больше двух листов - не знаю как это сделать - а мне нужно в дальнейшем - т.к. будут кроме лист 1 и 2 еще и лист 3 и т.д. еще такие же листы - с которых нужно будет брать данные. И также его нужно запускать два раза - чтоб сначала получить данные по разности с Листа 1 а потом тока разность итоговую. Прикладываю пример - как то что я пробовал сделать - может так станет более понятно:
nilem, Добрый день - большое спасибо за помощь!
Quote (nilem)
На Лист1 и Лист2 повторы удалить можно? Тогда будет за один проход - удаляем дубликаты и складываем со значениями из Сверка.
Удалять на этих листах дубликаты нельзя - т.к. они используються для визуального контроля!
Quote (nilem)
Значения по ст. А на всех листах всегда одни и те же?
Значение по Лист1 и Лист2 всегда одно и тоже по столбцу А - на листе сверка - они меняються в зависимости от того - какие файлы я загружаю - Но они всегда совпадают с какимито значениями по столбцу А листов 1 и 2.
Quote (nilem)
Вот так без удаления повторов. Предполагается, что повторы в ст. А отсортированы, т.е. идут один за другим.
Ваш вариант вроде в примере работает нормально - но при использовании в моем файле - переносит на лист сверка се разности по лист1 и лист2 Вот прикладываю пример файла с макросом - который дает результат - который мне нужен: Это вариация одного из Ваших макросов. В нем я не могу использовать больше двух листов - не знаю как это сделать - а мне нужно в дальнейшем - т.к. будут кроме лист 1 и 2 еще и лист 3 и т.д. еще такие же листы - с которых нужно будет брать данные. И также его нужно запускать два раза - чтоб сначала получить данные по разности с Листа 1 а потом тока разность итоговую. Прикладываю пример - как то что я пробовал сделать - может так станет более понятно:Ed_Vard
nilem, Выкладываю слегка измененый - но в принципе тот файл - в который мне нужен макрос. В столбцах Z:AA - расчет формулами - как должно быть. В столбцах AC:AD - как получаеться макросом. Лучше в макросе дописывать - какие листы считать - а не использовать все - т.к. могут быть лишние листы - которые нужны - но в расчете не должны учавствовать - типа как вот так: wshList = Array("сверка", "Лист1", "Лист2") - то что нужно я смогу дописать какой лист. Выкладываю файл - большой размер:
nilem, Выкладываю слегка измененый - но в принципе тот файл - в который мне нужен макрос. В столбцах Z:AA - расчет формулами - как должно быть. В столбцах AC:AD - как получаеться макросом. Лучше в макросе дописывать - какие листы считать - а не использовать все - т.к. могут быть лишние листы - которые нужны - но в расчете не должны учавствовать - типа как вот так: wshList = Array("сверка", "Лист1", "Лист2") - то что нужно я смогу дописать какой лист. Выкладываю файл - большой размер:
А-а-а-а, теперь понял. Файл обрезал, чтобы войти в стольник.
К сожалению - в реальности оказалось - что листы 1 и 2 имеют разное количество строк данных - поэтому подсчет идет неправильно
Уважаемые форумчане - можно ли как то оптимизировать вычисления формулами (поскольку макросом не совсем правильный результат получаеться) для большого количества данных в результате на листе 3? пример файла прилагаю.
Quote (nilem)
А-а-а-а, теперь понял. Файл обрезал, чтобы войти в стольник.
К сожалению - в реальности оказалось - что листы 1 и 2 имеют разное количество строк данных - поэтому подсчет идет неправильно
Уважаемые форумчане - можно ли как то оптимизировать вычисления формулами (поскольку макросом не совсем правильный результат получаеться) для большого количества данных в результате на листе 3? пример файла прилагаю.Ed_Vard
Доброго времени суток уважаемые форумчане! Прошу помощи у Вас - т.к. уже голову сломал - не хватает знаний Направление задачи тоже - но усложнилось - и не могу придумать - как ее решить. Есть книга excel в которой есть четыре листа с данными необходимыми для вычисления:1-2010,2-2010,1-2011,КОРР-ТП - на листах есть данные. Необходимо в макросе собрать данные с этих листов, произвести вычисления на листе Серка получить результат в столбцы U и V:
До настоящего времени использовал макрос уважаемого nilem за что ему еще раз большое спасибо, вот такого содержания:
Code
Option Explicit
Sub Сверка_2_БК() Dim x, i&, wshList(), bu, t() wshList = Array("1-2010", "2-2010", "1-2011") 'здесь дописываем Array("1 полугодие", "Лист2", "Лист3") и т.д. без "сверка"
With CreateObject("Scripting.Dictionary") .CompareMode = 1 x = [a4].CurrentRegion.Value For i = 2 To UBound(x) .Item(x(i, 1) & "" & x(i, 12)) = Array(x(i, 14) - x(i, 15), x(i, 16) - x(i, 17)) Next i
For Each bu In wshList x = Sheets(bu).[a4].CurrentRegion.Value For i = 2 To UBound(x) If .exists(x(i, 1) & "" & x(i, 12)) Then t = .Item(x(i, 1) & "" & x(i, 12)) t(0) = Round(t(0) + x(i, 14) - x(i, 15), 2) t(1) = Round(t(1) + x(i, 16) - x(i, 17), 2) .Item(x(i, 1) & "" & x(i, 12)) = t End If Next i Next bu
x = Application.Transpose(Application.Transpose(.Items)) Sheets("Сверка").[u5:v5].Resize(.Count).Value = x End With
End Sub
То - что мне нужно получить - попытался объяснить в файле примера на листе 1-2011. Свормулировал как то непонятно возможно - за что прошу сильно не пинать - но просто уже сам запуталя и буковки в слова никак не складываються. Прошу подсказать - возможно ли это вообще реализовать и в какую сторону копать!
Доброго времени суток уважаемые форумчане! Прошу помощи у Вас - т.к. уже голову сломал - не хватает знаний Направление задачи тоже - но усложнилось - и не могу придумать - как ее решить. Есть книга excel в которой есть четыре листа с данными необходимыми для вычисления:1-2010,2-2010,1-2011,КОРР-ТП - на листах есть данные. Необходимо в макросе собрать данные с этих листов, произвести вычисления на листе Серка получить результат в столбцы U и V:
До настоящего времени использовал макрос уважаемого nilem за что ему еще раз большое спасибо, вот такого содержания:
Code
Option Explicit
Sub Сверка_2_БК() Dim x, i&, wshList(), bu, t() wshList = Array("1-2010", "2-2010", "1-2011") 'здесь дописываем Array("1 полугодие", "Лист2", "Лист3") и т.д. без "сверка"
With CreateObject("Scripting.Dictionary") .CompareMode = 1 x = [a4].CurrentRegion.Value For i = 2 To UBound(x) .Item(x(i, 1) & "" & x(i, 12)) = Array(x(i, 14) - x(i, 15), x(i, 16) - x(i, 17)) Next i
For Each bu In wshList x = Sheets(bu).[a4].CurrentRegion.Value For i = 2 To UBound(x) If .exists(x(i, 1) & "" & x(i, 12)) Then t = .Item(x(i, 1) & "" & x(i, 12)) t(0) = Round(t(0) + x(i, 14) - x(i, 15), 2) t(1) = Round(t(1) + x(i, 16) - x(i, 17), 2) .Item(x(i, 1) & "" & x(i, 12)) = t End If Next i Next bu
x = Application.Transpose(Application.Transpose(.Items)) Sheets("Сверка").[u5:v5].Resize(.Count).Value = x End With
End Sub
То - что мне нужно получить - попытался объяснить в файле примера на листе 1-2011. Свормулировал как то непонятно возможно - за что прошу сильно не пинать - но просто уже сам запуталя и буковки в слова никак не складываються. Прошу подсказать - возможно ли это вообще реализовать и в какую сторону копать!Ed_Vard
Непонятно... Ed_Vard, попробуйте как-то "алгоритмически" что ли ("а-ля дядя Хьюго" ) описать задачу. Например: Заносим в массив таблицу с листа "Сверка", используя в качестве уникальных связку РегНомер-Страховой. Перебираем последовательно таблицы на листах 1-2010, 2-2010, 1-2011, КОРР-ТП, и, увидев соответствующую РегНомер-Страховой, перезаписываем в исходном массиве Н/С, У/С, Н/Н, У/Н (если совпадают о/п и год). Корректировки из КОРР-ТП мы увидим в последнюю очередь, и просто перезапишем нужные значения. В массиве пересчитываем СТР-АЯ и НАК-АЯ и переносим обратно на лист Сверка. Может, так и надо?
Непонятно... Ed_Vard, попробуйте как-то "алгоритмически" что ли ("а-ля дядя Хьюго" ) описать задачу. Например: Заносим в массив таблицу с листа "Сверка", используя в качестве уникальных связку РегНомер-Страховой. Перебираем последовательно таблицы на листах 1-2010, 2-2010, 1-2011, КОРР-ТП, и, увидев соответствующую РегНомер-Страховой, перезаписываем в исходном массиве Н/С, У/С, Н/Н, У/Н (если совпадают о/п и год). Корректировки из КОРР-ТП мы увидим в последнюю очередь, и просто перезапишем нужные значения. В массиве пересчитываем СТР-АЯ и НАК-АЯ и переносим обратно на лист Сверка. Может, так и надо?nilem
nilem, да - наверно лучше я и сам не сформулировал Только немного уточню вот этот момент:
Quote (nilem)
(если совпадают о/п и год).
Только если совпадают КАТ, о/п и год - т.к. на листе могут быть два совпадающих значения по РегНомеру-Страховой и они сумируются - что правильно, но в коректировке указываеться - какая именно КАТ корректируеться и за какой перриод (столбцы J и K) т.е ее нужно подставить вместо того значения а потом уже сложить с со следующим совпадающим РегНомер-Страховой на даном листе. Например-если смотреть мой пример- на листе 1-2011 есть строка 32 011-011-000006|РОГА И КОПЫТА|5006|СЗВ-6-1| КОР|УСЕН|1|2011|2|2010|123-123-123 21|ФИО ЧЕЛОВЕКА|100|80|50|25| это корректировка, которая дожна заменить строку 15 с листа 2-2010, а не строку 16 011-011-000006|РОГА И КОПЫТА|2011|СЗВ-6-1| ИСХ|УСЕН|2|2010|||123-123-123 21|ФИО ЧЕЛОВЕКА|5000|4500|3000|2500
Все остально вроде как Вы описали кажеться. Т.е. макрос должен делать в принципе тоже самое - что и ранее, только учитывать - что если по совпавшему РегНомеру-Страховой есть КОР в столбце Е - то нужно заменить этими значениями соответствующие значения в указанном периоде (столбцы J и K - в них указываеться за какой период идет корректировка, в ИСХ эти столбцы пусты - период для них указан в в столбцах H и I) при условии что совпадает у них КАТ - столбец F.
nilem, да - наверно лучше я и сам не сформулировал Только немного уточню вот этот момент:
Quote (nilem)
(если совпадают о/п и год).
Только если совпадают КАТ, о/п и год - т.к. на листе могут быть два совпадающих значения по РегНомеру-Страховой и они сумируются - что правильно, но в коректировке указываеться - какая именно КАТ корректируеться и за какой перриод (столбцы J и K) т.е ее нужно подставить вместо того значения а потом уже сложить с со следующим совпадающим РегНомер-Страховой на даном листе. Например-если смотреть мой пример- на листе 1-2011 есть строка 32 011-011-000006|РОГА И КОПЫТА|5006|СЗВ-6-1| КОР|УСЕН|1|2011|2|2010|123-123-123 21|ФИО ЧЕЛОВЕКА|100|80|50|25| это корректировка, которая дожна заменить строку 15 с листа 2-2010, а не строку 16 011-011-000006|РОГА И КОПЫТА|2011|СЗВ-6-1| ИСХ|УСЕН|2|2010|||123-123-123 21|ФИО ЧЕЛОВЕКА|5000|4500|3000|2500
Все остально вроде как Вы описали кажеться. Т.е. макрос должен делать в принципе тоже самое - что и ранее, только учитывать - что если по совпавшему РегНомеру-Страховой есть КОР в столбце Е - то нужно заменить этими значениями соответствующие значения в указанном периоде (столбцы J и K - в них указываеться за какой период идет корректировка, в ИСХ эти столбцы пусты - период для них указан в в столбцах H и I) при условии что совпадает у них КАТ - столбец F.Ed_Vard
Сообщение отредактировал Ed_Vard - Пятница, 28.10.2011, 00:15
Доброго времени суток уважаемы форумчане Вопрос относиться к вышеизложенному чуть выше. Возможно ли как то при считывании данных в массив, при попадании значения нового значения заменить им строку которую уже считали на предыдущем листе при совпадении определенных условий?
Доброго времени суток уважаемы форумчане Вопрос относиться к вышеизложенному чуть выше. Возможно ли как то при считывании данных в массив, при попадании значения нового значения заменить им строку которую уже считали на предыдущем листе при совпадении определенных условий?Ed_Vard
Ну чтобы как-то снять с якоря. На листе "промежуточный результат" жмем зеленую кнопку. Выводятся данные с листов "1-2010", "2-2010", "1-2011", "КОРР-ТП", которые соответствуют связке РЕГ НОМЕР-СТРАХОВОЙ с листа "сверка" (Sub ertert() модуль1). Теперь, видимо, их надо как-то скомпоновать и посчитать страховую и накопительную части. Но вот как? В примере строк не так много - может вручную сделаете, чтобы увидеть нужный результат? И пояснения какие-нибудь, желательно "алгоритмически"
Всех с праздником!
Ну чтобы как-то снять с якоря. На листе "промежуточный результат" жмем зеленую кнопку. Выводятся данные с листов "1-2010", "2-2010", "1-2011", "КОРР-ТП", которые соответствуют связке РЕГ НОМЕР-СТРАХОВОЙ с листа "сверка" (Sub ertert() модуль1). Теперь, видимо, их надо как-то скомпоновать и посчитать страховую и накопительную части. Но вот как? В примере строк не так много - может вручную сделаете, чтобы увидеть нужный результат? И пояснения какие-нибудь, желательно "алгоритмически"
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
) описать задачу. Например: 
