Сравнение ячеек в excel в цикле
Доброго времени суток. Нужно написать цикл который сверяет даты на разных страницах и, в зависимости от результата, либо добавляет значение,либо оставляет ячейку пустой. строки дат фиксированы, но не фиксировано их количество.пробовал сразу сам, получается неочень. идея по количеству: чтобы сравнивало до ячейки с текстом "ИТОГО отгружено". Заранее спс
Доброго времени суток. Нужно написать цикл который сверяет даты на разных страницах и, в зависимости от результата, либо добавляет значение,либо оставляет ячейку пустой. строки дат фиксированы, но не фиксировано их количество.пробовал сразу сам, получается неочень. идея по количеству: чтобы сравнивало до ячейки с текстом "ИТОГО отгружено". Заранее спс Espada
в В12 и протянуть до конца строки.
Если в столбце А указаны названия листов. Автор - SkyPro
Дата добавления - 11.09.2013 в 16:23
А формула не подойдет?
[Перевод / Translate]
=IFERROR(HLOOKUP(B$4,INDIRECT("'"&$A12&"'!$B$4:$F$5"),2,0),"")
в В12 и протянуть до конца строки.
Если в столбце А указаны названия листов.
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле
А формула не подойдет?
[Перевод / Translate]
=IFERROR(HLOOKUP(B$4,INDIRECT("'"&$A12&"'!$B$4:$F$5"),2,0),"")
в В12 и протянуть до конца строки.
Если в столбце А указаны названия листов.
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле Espada
А формула не подойдет?
[Перевод / Translate]
=IFERROR(HLOOKUP(B$4,INDIRECT("'"&$A12&"'!$B$4:$F$5"),2,0),"")
в В12 и протянуть до конца строки.
Если в столбце А указаны названия листов.
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле Автор - Espada
Дата добавления - 11.09.2013 в 16:43
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле
Вот так получается.
Или формулой массива:
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле
Вот так получается.
Или формулой массива:
Необходимо чтобы последним столбцом был именно ИТОГО отгружено, так как последние 3 столбца высчитываются по другой формуле
Вот так получается.
Или формулой массива:
Согласен, если бы было фиксированное число дней, но в зависимости от фильтра дней может быть от 1-до 30.
Согласен, если бы было фиксированное число дней, но в зависимости от фильтра дней может быть от 1-до 30. Espada
=ЕСЛИОШИБКА(ГПР(ПОДСТАВИТЬ(B$4;"ИТОГО отгружено";"Общий итог");ДВССЫЛ("'"&$A11&"'!$B$4:$BB$5");2;0);"")
=ЕСЛИОШИБКА(ГПР(ПОДСТАВИТЬ(B$4;"ИТОГО отгружено";"Общий итог");ДВССЫЛ("'"&$A11&"'!$B$4:$BB$5");2;0);"")
=ЕСЛИОШИБКА(ГПР(ПОДСТАВИТЬ(B$4;"ИТОГО отгружено";"Общий итог");ДВССЫЛ("'"&$A11&"'!$B$4:$BB$5");2;0);"")
В Галактике формирую отчет за определённый период, т.е. каждый раз число дней разное, от этого и меняется размер таблицы, вы мне уже помогали с подобным отчетом, где нужно было скопировать строчку
[vba]
Dim rCell As Range, rRange As Range
With Sheets("Порошки")
Set rRange = Nothing
For Each rCell In .[b5].Resize(1, .UsedRange.Columns.Count)
If rCell <> "" Then
If rRange Is Nothing Then
Set rRange = rCell
Else
Set rRange = Union(rRange, rCell)
End If
End If
Next
rRange.Copy
Sheets("Анализ").[b12:g12].PasteSpecial Paste:=xlValues
Application.CutCopyMode = 0
End With
[/vba]
тогда помог, но там было 100% совпадение дат в 2-х таблицах, а здесь нужно сверят даты и только если совпадают добавить данные в ячейку. я думал мб ещё один цикл добавить на сравнение.
В Галактике формирую отчет за определённый период, т.е. каждый раз число дней разное, от этого и меняется размер таблицы, вы мне уже помогали с подобным отчетом, где нужно было скопировать строчку
[vba]
Dim rCell As Range, rRange As Range
With Sheets("Порошки")
Set rRange = Nothing
For Each rCell In .[b5].Resize(1, .UsedRange.Columns.Count)
If rCell <> "" Then
If rRange Is Nothing Then
Set rRange = rCell
Else
Set rRange = Union(rRange, rCell)
End If
End If
Next
rRange.Copy
Sheets("Анализ").[b12:g12].PasteSpecial Paste:=xlValues
Application.CutCopyMode = 0
End With
[/vba]
тогда помог, но там было 100% совпадение дат в 2-х таблицах, а здесь нужно сверят даты и только если совпадают добавить данные в ячейку. я думал мб ещё один цикл добавить на сравнение. Espada
Dim rCell As Range, rRange As Range
With Sheets("Порошки")
Set rRange = Nothing
For Each rCell In .[b5].Resize(1, .UsedRange.Columns.Count)
If rCell <> "" Then
If rRange Is Nothing Then
Set rRange = rCell
Else
Set rRange = Union(rRange, rCell)
End If
End If
Next
rRange.Copy
Sheets("Анализ").[b12:g12].PasteSpecial Paste:=xlValues
Application.CutCopyMode = 0
End With
[/vba]
тогда помог, но там было 100% совпадение дат в 2-х таблицах, а здесь нужно сверят даты и только если совпадают добавить данные в ячейку. я думал мб ещё один цикл добавить на сравнение. Автор - Espada
Дата добавления - 11.09.2013 в 17:20
Зачем макрос, если формулами это решается быстрее и удобнее?
Вот.
Осталось добавить остальные листы.
Зачем макрос, если формулами это решается быстрее и удобнее?
Вот.
Осталось добавить остальные листы. SkyPro
Вот.
Осталось добавить остальные листы. Автор - SkyPro
Дата добавления - 11.09.2013 в 17:26
Это было бы так, если бы точно знал сколько у меня столбцов будет в отчёте. Можно было бы задать расчет на всю строку, но последние 3 столбца высчитываются по другим формулам.
Это было бы так, если бы точно знал сколько у меня столбцов будет в отчёте. Можно было бы задать расчет на всю строку, но последние 3 столбца высчитываются по другим формулам. Espada
Это было бы так, если бы точно знал сколько у меня столбцов будет в отчёте. Можно было бы задать расчет на всю строку, но последние 3 столбца высчитываются по другим формулам. Автор - Espada
Дата добавления - 12.09.2013 в 14:32
Каким образом формируется отчет (я так понимаю под словом "отчет" вы подразумеваете лист "анализ")? Откуда берется кол-во столбцов?
Телепатией тут владеют не многие, я не в их числе.
Если макросом, то формулу можно добавить в сам макрос.
Если вручную, то формулу можно заносить вручную.
Если вы имеете ввиду "неизвестно кол-во столбцов" на листах ИЗ КОТОРЫХ должны тянуться данные, то в формуле это предусмотрено:
=ЕСЛИОШИБКА(ГПР(ПОДСТАВИТЬ(B$4;"ИТОГО отгружено";"Общий итог");ДВССЫЛ("'"&$A11&"'! $B$4:$BB$5 ");2;0);"")
Каким образом формируется отчет (я так понимаю под словом "отчет" вы подразумеваете лист "анализ")? Откуда берется кол-во столбцов?
Телепатией тут владеют не многие, я не в их числе.
Если макросом, то формулу можно добавить в сам макрос.
Если вручную, то формулу можно заносить вручную.
Если вы имеете ввиду "неизвестно кол-во столбцов" на листах ИЗ КОТОРЫХ должны тянуться данные, то в формуле это предусмотрено:
=ЕСЛИОШИБКА(ГПР(ПОДСТАВИТЬ(B$4;"ИТОГО отгружено";"Общий итог");ДВССЫЛ("'"&$A11&"'! $B$4:$BB$5 ");2;0);"") SkyPro
Прошу прощение. сейчас всё объясню. Я работаю в Галактике(бух.программа типа 1С). Там устанавливаю диапазон дат, за которые формируется отчёт(от одного дня до бесконечности). из галактики формирую документ екселя с исходными данными(эту страничку я не демонстрировал). Затем с помощью макроса я формирую сводную таблицу, из сводной формирую анализ(все поля кроме строки порошки)
затем формирую лист "Порошки"(копирую из сводной таблицы и с помощью фильтра(записаном в макросе) преобразую её в необходимый вид. ну из этой таблицы переношу данные на лист Анализ создаю строчку порошки и в неё заношу данные с листа порошки.
Как то так. Сформировал отчет отчёт за 2 дня(для примера) внутри отчет эксель который появляется непосредственно из галактики, и файлик с макросом
Прошу прощение. сейчас всё объясню. Я работаю в Галактике(бух.программа типа 1С). Там устанавливаю диапазон дат, за которые формируется отчёт(от одного дня до бесконечности). из галактики формирую документ екселя с исходными данными(эту страничку я не демонстрировал). Затем с помощью макроса я формирую сводную таблицу, из сводной формирую анализ(все поля кроме строки порошки)
затем формирую лист "Порошки"(копирую из сводной таблицы и с помощью фильтра(записаном в макросе) преобразую её в необходимый вид. ну из этой таблицы переношу данные на лист Анализ создаю строчку порошки и в неё заношу данные с листа порошки.
Как то так. Сформировал отчет отчёт за 2 дня(для примера) внутри отчет эксель который появляется непосредственно из галактики, и файлик с макросом Espada
В предыдущей статье Сравнение таблиц в Excel мы рассмотрели подход к сравнению сложных таблиц с использованием формул и без программирования.
В данной статье рассмотрим способ сравнения таблиц Excel с помощью VBA макросов на примере тех же исходных данных.
Проиллюстрируем задачу картинкой из первой статьи.
Для начала напишем алгоритм наших действий по сравнению таблиц.
- Определим диапазоны данных первой и второй таблицы, то есть найдем последние значимые строки и сохраним их номера в переменных (последняя строка таблицы 1 - last_i и последняя строка таблицы 2 - last_j).
- Начнем проходить по каждой строке таблицы 2 (внешний цикл), данные из которой нужно перенести в таблицу 1. С первой строки данных (в примере это строка 3) до последней строки таблицы 2.
- Для каждой строки таблицы 2 определим идентификатор строки, путем формирования строки, содержащей полный адрес квартиры (значения из нескольких колонок, разделенные дефисами).
- Начнем проходить по каждой строке таблицы 1 (внутренний цикл) с первой строки данных (в примере это строка 3) до последней строки таблицы 1, определяя при этом идентификатор строки.
- Сравним значения идентификаторов строк таблицы 1 и таблицы 2.
- Если идентификаторы равны, перепишем ФИО покупателя из ячейки таблицы 2 в соответствующую ячейку таблицы 1; прервем внутренний цикл по таблице 1 и перейдем к следующей строке таблицы 2 (переход к п.2).
Теперь остается реализовать алгоритм в виде программного кода макроса.
Для этого откроем вкладку Вид ленты функций Excel. Щелкнем на нижнюю часть со стрелкой кнопки Макросы. В открывшемся подменю выберем Запись макроса. В результате начнется запись нового макроса. Поскольку код мы будем формировать вручную, то еще раз зайдем в подменю макросов и выберем Остановить запись. Далее еще раз войдем в подменю макросов и выберем Макросы.
В появившемся диалоге выделим наш макрос и нажмем Изменить.
На экране откроется окно редактора макросов Visual Basic for Applications. В области кода (правая верхняя область) отображается код только что созданного пустого макроса.
В процедуру Макрос1 (между объявлениями начала и конца процедуры: Sub и End Sub) необходимо вставить код, решающий поставленную задачу. Образец кода представлен ниже.
Результат решения задачи:
Типовая задача, возникающая периодически перед каждым пользователем Excel - сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :
Число несовпадений можно посчитать формулой:
или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))
Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Типовая задача, возникающая периодически перед каждым пользователем Excel - сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.
Вариант 1. Синхронные списки
Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :
Число несовпадений можно посчитать формулой:
или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))
Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.
Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)
Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:
- залить цветом или как-то еще визуально отформатировать
- очистить клавишей Delete
- заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
- удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
- и т.д.
Вариант 2. Перемешанные списки
Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.
Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):
Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.
Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.
В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:
Полученный в результате ноль и говорит об отличиях.
И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:
Всем доброго времени суток! люди добрый помогите плиз составить расчет.
Пр: в 3 ячейках существуют числа.
A1=7; B1=4; C1=11
в D1 необходимо поставить условие таким образом,
чтоб при "A1 при "A1C1; A1>B1 A1 при "A1>B1 и A1>C1" в D1 подставлялась '3'.
как составить 1 формулу с соблюдением всех параметров?
P.S. Я себе уже все волосы вырвал
Сравнение ячеек данных Stringgrid и ячейками данных Access
Помогите пожалуйста! Как сравнивать данные ячеек стринггрида и данные базы аксесс типа стринг, и.
Сравнение данных в двух textarea и вывод данных
Добрый день! Возникла такая проблема, сразу скажу что в javascript слабо разбираюсь В 2.
Сравнение данных из ячеек DataGridView на двух разных формах
Есть 2 формы, на одной есть DataGridView и на второй - DataGridView. Нужно сравнить данные из.
Сравнение (поиск) ячеек из столбца и копирование к ним дополнительных данных
Добрый день! У меня к сожалению пока посредственные знания VBA, но я уже примерно попытался.
Решение
Добрый день!
Помогите пожалуйста с формулой.
У меня похожие условия, сравниванием 3 ячейки, нужно выбрать одинаковые и вынести информацию в четвертую, если найдется 2 совпадения, в пятую - если 3 совпадения. Данные в ячейках от 1 до 9.
Благодарю.
tam_tam, среди 4 значений есть еще вариант 2+2, его как считать - как 2 или как отдельную категорию?
Вообще, одной формулой видимо не получится, надо использовать промежуточные столбцы или функцию VBA - Вас что больше устроит?
Про вариант 2+2 я тоже думала. а, что если вместо значения будет подставляться "Есть"? Возможно такое?
В принципе, устроит любое решение.
Добрый день!
Абсолютно такая же проблема, но требующая решения через код VBA.
Имеется три столбца с набором данных, информацию требуется выводить в четвертый столбец.
Необходимо:
1. Если текст в ячейке столбца A совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С есть текст, то выводить в соседнюю ячейку столбца D "текст 1";
2. Если текст в ячейке столбца A совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С НЕТ текста, то выводить в соседнюю ячейку столбца D "текст 2";
3. Если текст в ячейке столбца A НЕ совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С есть текст, то выводить в соседнюю ячейку столбца D "текст 3";
4. Если текст в ячейке столбца A НЕ совпадает с текстом в ячейке столбца B, а в соседней ячейке столбца С НЕТ текста, то выводить в соседнюю ячейку столбца D "текст 4".
Читайте также: