Как сравнить число с массивом в excel - Word и Excel - помощь в работе с программами

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава Оглавление Следующая глава

Выборки, основанные на одном или нескольких условиях. Ряд функций Excel используют операторы сравнения. Например, СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. Эти функции осуществляют выборки на основе одного или нескольких условий (критериев). Проблема в том, что эти функции могут только складывать, подсчитывать количество, и находить среднее. А если вы хотите наложить условия на поиск, например, максимального значения или стандартного отклонения? В этих случаях, поскольку не существует встроенной функции, вы должны изобрести формулу массива. Нередко это связано с использованием оператора сравнения массивов. Первый пример в этой главе, показывает, как рассчитать минимальное значения при одном условии.

Воспользуемся функцией ЕСЛИ, чтобы выбрать элементы массива, отвечающие условию. На рис. 4.1 в левой таблице присутствуют столбец с названиями городов и столбец с временем. Требуется найти минимальное время для каждого города и поместить это значение в соответствующую ячейку правой таблицы. Условие для выборки – название города. Если вы используете функцию МИН, то сможете найти минимальное значение столбца В. Но как вы выберите только те числа, что относятся только к Окленду? И как вам скопировать формулы вниз по колонке? Поскольку в Excel нет встроенной функции МИНЕСЛИ, вам необходимо написать оригинальную формулу, совмещающую функции ЕСЛИ и МИН.

Рис. 4.1. Цель формулы: выбрать минимальное время для каждого города

Скачать заметку в формате Word или pdf в формате Excel2013

Как показано на рис. 4.2, вам следует начать ввод формулы в ячейку E3 с функции МИН. Но вы же не можете поместить в аргумент число1 все значения столбца B!? Вы хотите отобрать только те значения, которые относятся к Окленду.

Рис. 4.2. Начните с ввода функции МИН

Как показано на рис. 4.3, на следующем этапе введите функцию ЕСЛИ в качестве аргумента число1 для МИН. Вы вложили ЕСЛИ внутрь МИН.

Рис. 4.3. Добавьте ЕСЛИ внутрь МИН

Разместив курсор в месте введения аргумента лог_выражение функции ЕСЛИ (рис. 4.4), вы выделяете диапазон с названиями городов А3:А8, а затем нажимаете F4, чтобы сделать ссылки на ячейки абсолютными (подробнее см., например, Относительные, абсолютные и смешанные ссылки на ячейки в Excel). Затем вы набираете сравнительный оператор – знак равенства. Наконец, вы выделите ячейку слева от формулы – D3, оставляя ссылку на нее относительной. Сформулированное условие позволит выбрать только Окленды при просмотре диапазона А3:А8.

Рис. 4.4. Создайте оператор массива в аргументе лог_выражение функции ЕСЛИ

Итак, вы создали оператор массива с помощью оператора сравнения. В любой момент обработки массива оператор массива является оператором сравнения, так что результатом его работы будет массив, состоящий из значений ИСТИНА и ЛОЖЬ. Чтобы убедиться в этом, выделите массив (для этого щелкните во всплывающей подсказке на аргумент лог_выражение) и нажмите F9 (рис. 4.5). Обычно вы используете один аргумент лог_выражение, возвращающее либо ИСТИНУ, либо ЛОЖЬ; здесь же результирующий массив вернет несколько значений ИСТИНЫ и ЛЖИ, так что функция МИН выберет минимальное число только для тех городов, которые соответствуют значению ИСТИНА.

Рис. 4.5. Чтобы увидеть массив, состоящий из значений ИСТИНА и ЛОЖь, щелкните во всплывающей подсказке на аргумент лог_выражение и нажмите F9

Надеюсь, вы не забыли (см. главу 2), чтобы «не испортить» формулу после F9 нажмите Ctrl+Z, и формула вернется в исходное состояние.

Как показано на рис. 4.7, далее следует ввести точку с запятой, чтобы перейти к вводу аргумента значение_если_истина функции ЕСЛИ (обратите внимание, что во всплывающей подсказке выделился второй аргумент функции ЕСЛИ). Ведите диапазон В3:В8 и нажмите F4. Выделенный диапазон должен иметь такой же размер, как и массив аргумента лог_выражение (в нашем случае в каждом массиве по 6 элементов). На этом ввод можно завершить, так как аргумент значение_если_ложь является необязательным, и вы, действительно, не хотите что-то считать, для тех ячеек, где аргумента лог_выражение принимает значение ЛОЖЬ. Функция МИН запрограммирована так, что проигнорирует логические значения.

Рис. 4.7. Введите диапазон в аргумент значение_если_истина

Как только вы ввели закрывающую скобку функции ЕСЛИ, всплывающая подсказка «сообразила», что ввод функции ЕСЛИ завершен, и стала показывать синтаксис функции МИН (рис. 4.8). Обратите внимание, что вся функция ЕСЛИ разместилась в аргументе число1 функции МИН.

Рис. 4.8. Функция ЕСЛИ заняла место число1 среди аргументов функции МИН

Чтобы увидеть массив, возвращаемый функцией ЕСЛИ, во всплывающей подсказке щелкните на аргумент1, и нажмите F9 (рис. 4.9). Видно, что массив вернул 4 ЛОЖНЫХ логических значения, когда город не совпадает с Oakland и два числа – 9 и 6 – когда город совпадает с Oakland. Это удивительное использование функции ЕСЛИ позволяет отфильтровать значения, которые вам не нужны и найти минимум только среду «нужных» значений. Аналогичным образом вы можете использовать функцию ЕСЛИ и в других агрегатных функций, таких как МАКС и СТАНДОТКЛОН (СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г в версии Excel2010).

Рис. 4.9. Во всплывающей подсказке щелкните на аргумент1, и нажмите F9, чтобы увидеть результирующий массив

Введите вторую закрывающую скобку (для функции МИН). Чтобы убедится, что функция ЕСЛИ не была изначально запрограммирована для обработки массивов, поместите курсор обратно в аргумент лог_выражение функции ЕСЛИ (кликните во всплывающей подсказке на этом аргументе, чтобы выделить содержимое, относящееся к нему). Обратите внимание, что весь массив «сидит» в аргументе лог_выражение (рис. 4.10). Т.е. Excel не понимает, что перед ним функция массива, эта функция не была изначально запрограммирована для обработки массивов, поэтому чтобы завершить ввод формулы в ячейку нажмите Ctrl+Shift+Enter. После этого Excel поместит фигурные скобки в начале и в конце формулы.

Рис. 4.10. Функция ЕСЛИ изначально не предназначена для работы с массивами, поэтому для корректного ввода формулы нажмите Ctrl+Shift+Enter

Вы можете скопировать формулы массива вниз столбца; фигурные скобки также копируются (рис. 4.12). Если вы измените исходные данные (названия городов и/или время), формулы массива отработают эти изменения и немедленно покажут новый результат.

Рис. 4.12 Формулу массива можно «протащить» вниз по столбцу

Только что вы создали прекрасную формулу массива! Однако, чтобы в каждом конкретном случае быть способным выбирать оптимальные пути решения, предлагаю вам кратко ознакомиться с функциями баз данных и сводных таблиц.

Функции для работы с базами данных

В Excel есть группа из 12 функций для работы с базами данных (их еще называют БД-функции). Эти функции могут производить расчеты на основе критериев. Например, ДМИН, ДМАКС, БДСУММ. Эти функции отлично справятся с работой, если у вас один-единственный расчет, а колонки имеют заголовки. На рис. 4.13 показано, как можно использовать функцию ДМИН для вычисления минимального времени по городу Окленд. Преимущество БД-функций заключается в том, что если вам нужно сделать единственное вычисление, например, найти минимальное время для Окленда (а по Сиэтлу и др. не интересует), то ДМИН создать проще, чем описанную выше =МИН(ЕСЛИ()) формулу массива. Да и работать ДМИН будет быстрее, чем формула массива (что важно при больших объемах данных). К недостатку БД-функции можно отнести требование формировать аргумент критерий из двух полей: заголовка и собственно критерия (рис. 4.14).

Рис. 4.13. Функция ДМИН

Рис. 4.14. Аргумент критерий должен содержать заголовок (D2) и критерий (D3)

На рис. 4.15 показано, во что может превратиться конструкция с ДМИН, если вам необходимо извлечь данные для нескольких городов. К тому же вам не удастся «протянуть» формулу вдоль колонки. На рис. 4.15 показаны формулы, которые необходимые ввести для каждого города.

Рис. 4.15. «Протащить» БД-функцию по колонке не получится

Если бы вам требовалось извлечь минимальное время для большого числа городов, функция ДМИН доставила бы вам много хлопот. Рис. 4.16 показывает метод, который использует БД-функцию совместно с опцией Excel Таблицей данных для облегчения выборки по большому количеству городов (в главе 21 Таблицы данных рассматриваются более подробно).

Рис. 4.16. БД-функция и Таблица данных, работая вместе, могут заменить функцию массива

Чтобы получить результат как на рис. 4.16, выполните следующее:

Шаг 1. Создайте формулу ДМИН в ячейке Е3.

Шаг 2. Поместите названия городов в ячейки D6:D8.

Шаг 3. В ячейке Е5 создайте ссылку на ячейку Е3.

Шаг 4. Выделите область D5:Е8.

Шаг 5. Откройте диалоговое окно Таблицы данных, для чего нажмите Alt Д, а затем Т, или пройдите по меню Данные — Работа с данными — Анализ «что если» — Таблица данных

Шаг 6. Введите D3 в область «Подставлять значения по строкам в:» (всё равно, какую ссылку вы введете – абсолютную или относительную).

Итак, когда использовать ДМИН, а когда формулу массива МИН(ЕСЛИ(…))? Если вам нужно выполнить единственное вычисление, и данные у вас организованы надлежащим образом (имеются заголовки), используйте ДМИН. Если у вас много вычислений и/или данные не организованы для быстрого использования БД-функций, воспользуйтесь формулой массива. Рассмотрите также возможность совместного использования БД-функции и инструмента Таблица данных.

Сводные таблицы

Если вам не требуется, чтобы после внесения изменений в исходные данные, результаты мгновенно обновлялись (что характерно для формул), рассмотрите возможность использования сводной таблицы, которая позволит применить один или несколько критериев для отбора данных. Почему? Потому что сводные таблицы довольно легко создать. Особенно они будут полезны, если критериев много, и/или они весьма «заковыристые». Этот раздел просматривает одну-вычисление условия. Рис. 4.17 показан завершенный сводная Таблица для расчета минимального с одним условием.

Рис. 4.17. Создать сводную таблицу быстро и просто

Чтобы получить результат как на рис. 4.16, выполните следующее:

Шаг 1. Убедитесь, что все колонки имеют имена и нет пустых строк и колонок.

Шаг 2. Встаньте на любую ячейку исходных данных перейдите на вкладку ленты Вставить и в области Таблицы кликните на Сводная таблица.

Шаг 3. В открывшемся диалоговом окне Создание сводной таблицы включите На существующий лист и в поле Диапазон введите D1.

Шаг 4. Перетащите Город поле Строки и Время, ч в поле Значения.

Шаг 5. Щелкните правой кнопкой мыши на одной из ячеек диапазона Е1:Е4 и в контекстном меню выберите опцию Параметры полей значений; в открывшемся окне на вкладке Операция выберите Минимум.

Шаг 6. Встаньте на любую ячейку сводной таблицы, и сделайте активной вкладку Работа со сводными таблицами – Конструктор; в области Макет кликните на Общие итоги и выберите Отключить для строк и столбцов; там же (в области Макет) кликните на Макет отчета и выберите Показать в табличной форме.

Шаг 7. Кликните на ячейке D1 и введите Город, кликните на Е1 и введите Минимальное время.

Так что же предпочесть: сводную таблицу или формулу массива? Главный недостаток сводных таблиц заключается в том, что они не обновляются сразу же после изменения исходных данных (нужно встать на любую ячейку сводной таблицы, кликнуть правой кнопкой мыши и выбрать Обновить). В то же время формулы обновляются моментально. Если у вас несколько критериев для отбора, сводные таблицы позволяют проще справиться с ними, перетаскивая поля в области строк, колонн, значений и фильтра. Формулы массива с каждым дополнительным критерием становятся всё сложнее и сложнее. Если вы хотите изменить функцию, в сводной таблице вы просто щелкните правой кнопкой мыши и выберите одну из операций: Сумма, Количество, Минимум… (всего их 11). Используя формулу массива, необходимо перестроить формулу, но… в вашем распоряжении не 11, а 350 функций Excel. Сводные таблице занимают место на листе, их сложнее форматировать. Если в вашем арсенале будет и то, и другое, вы сможете гибче решать стоящие перед вами задачи.

Операции сравнения массивов с несколькими условиями

Если у вас нескольких критериев, вы можете использовать несколько функций ЕСЛИ в одной формуле (количество функций ЕСЛИ равно числу критериев). Например, определим максимальное количество продаж для каждого представителя в западном регионе (рис. 4.18).

Рис. 4.18. Цель – рассчитать максимальный объем продаж для каждого сотрудника на западе

Вы хотите написать формулу, которую можно «протянуть» по столбцу, также вы хотите, чтобы формула автоматически обновлялась при изменении исходных данных. Вы можете решить задачу с помощью функции МАКС с двумя вложенными функциями ЕСЛИ. Начните ввод формулы в ячейку F5 с функции МАКС; в качестве аргумента число1 вложите функцию ЕСЛИ, которая позволит вам выбрать только те значения, которые соответствуют вашим условиям (рис. 4.19).

Рис. 4.19. Наберите =МАКС(ЕСЛИ(

В качестве первого аргументу лог_выражение функции ЕСЛИ наберите оператор сравнения, спрашивающий: «совпадает ли значение в столбце Регион с условием в ячейке F2?» (рис. 4.20). В качестве результата оператор сравнения $A$3:$A$12=$F$2 вернет последовательность значений ИСТИНА и ЛОЖЬ.

Рис. 4.20. Введите оператор массива

Далее введите разделитель – точка с запятой, и всплывающая подсказка покажет, что вы переместились из области набора аргумента лог_выражение к набору аргумента значение_если_истина (рис. 4.21). Обычно, здесь вводят значение, которые хотят получить в случае если аргумент лог_выражение вернул значение ИСТИНА, но ведь у вас есть еще одно условие, поэтому введите еще одну функцию ЕСЛИ (рис. 4.22). В качестве первого аргумента введите оператор массива, спрашивающий: «совпадает ли значение в столбце Представитель с условием в ячейке Е5?»

Рис. 4.21. Введите разделитель – точку с запятой – это перенесет вас в область набора следующего аргумента

Рис. 4.22. Добавьте вторую функцию ЕСЛИ и оператор массива, проверяющий второе условие

Просто чтобы убедиться, что вторая ЕСЛИ «сидит» в аргументе значение_если_истина первой ЕСЛИ, встаньте курсором на втором слове ЕСЛИ (рис. 4.23). Видно, что во всплывающей подсказке выделился аргумент значение_если_истина. До Excel 2007, вы могли вложить друг в друга до семи функций ЕСЛИ; начиная с Excel 2007 вы можете вложить до 64 функций.

Рис. 4.23. Вторая ЕСЛИ «сидит» в аргументе значение_если_истина первой ЕСЛИ

Введите разделитель – точку с запятой. Теперь вы можете добавлять массив Продажи в качестве аргумента значение_если_истина второй ЕСЛИ (рис. 4.24). Эмпирическое правило для внедрения друг в друга функций ЕСЛИ гласит: продолжайте последовательно вводить функции ЕСЛИ в качестве аргумента лог_выражение предыдущей ЕСЛИ, пока у вас не закончатся условия. Затем вы можете ввести в качестве аргумента значение_если_истина. При этом все значения аргументов значение_если_ложь могут быть опущены, поскольку функция МАКС (и целый ряд других) игнорирует логические значения.

Рис. 4.24. Введите диапазон значений для аргумента значение_если_истина второй ЕСЛИ

Три закрывающие скобки завершат набор формулы (рис. 4.25). Вы можете проверить, что вы набрали правильное число закрывающих скобок, следя за их цветом. Последняя закрывающая скобка должна иметь тот же цвет, что и первая открывающая.

Рис. 4.25. Черная закрывающая скобка означает, что набор формулы завершен

Если во всплывающей подсказке вы щелкните на аргумент число1, а затем нажмете клавишу F9, то увидите, что две вложенные функции ЕСЛИ отобрали только те значения, которые соответствуют обоим критериям (рис. 4.26). Числа 914 и 610 были выбраны потому, что они относятся, как к региону Запад, так и к представителю Chin.

Рис. 4.26. Две вложенные функции ЕСЛИ отобрали только те значения, которые соответствуют обоим критериям

Примечание: рассмотренный пример демонстрирует И критерии. С И критериями, все условия функций ЕСЛИ должны вернуть значение ИСТИНА. В главе 11, вы познакомитесь с ИЛИ критерием; для истинности всего выражения, по крайней мере, одна ЕСЛИ должна вернуть значение ИСТИНА.

Еще пара слов о том, как работает формула. Каждая из двух функций ЕСЛИ проверяет свой массив: первая – А3:А12 на предмет совпадения со значением в F2, вторая – В3:В12 на совпадение с Е5. Только в том случае, когда оба условия выполнены, функция ЕСЛИ возвращает соответствующие значения из третьего массива – С3:С12. Формула выглядит так:

=МАКС(ЕСЛИ({ИСТИНА:ЛОЖЬ:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА}; ЕСЛИ({ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}; {914:355:590:610:506:825:710:280:363:685})))

Мы знаем, что функция ЕСЛИ не была изначально запрограммирована на работу с массивами, поэтому для ввода формулы в ячейку нажмите Ctrl+Shift+Enter. После того, как вы введете формулу массива, убедитесь, что появились фигурные скобки в начале и в конце формулы. Поскольку они есть, вы можете скопировать формулу вниз по столбцу (рис. 4.29).

Рис. 4.29. Формула массива работает в трех ячейках

После того, как вы создали формулу массива, можно задуматься и о том, как решать задачу другим способом? Как и в первом примере этой главы вам придут на помощь функция для работы с базами данных ДМАКС (рис. 4.30) и сводная таблица (рис. 4.31).

Рис. 4.30. ДМАКС легко найдет максимальное значение при двух условиях, а вот копирование формулы по столбцу затруднено

Рис. 4.31. Сводная таблица очень удобна для отыскания максимального значения при двух условиях, но она требует обновления, если исходные данные изменились

Следующий раздел описывает, как использовать оператор сравнения НЕ и некоторые новые удивительные функции Excel доступные с версии 2010 г., которые могут осуществлять выборки на основе условий (например, нахождение минимума или максимума), без Ctrl+Shift+Enter.

Определение минимума при двух условиях, одно из которых НЕ. Рис. 4.32 показаны результаты нескольких гонок на велосипедах BMX (маленькие гоночные велосипеды). Результаты представлены в трех колонках: название трассы, имя гонщика, время прохождения трассы. Задача – найти лучшее время каждого гонщика, но, поскольку трасса PI гораздо короче других, вы не хотите ее учитывать. Это значит, что вам нужна функция массива МИН с двумя условиями: соответствие имени гонщика и «все трассы, кроме PI». Можно использовать функцию МИН с двумя ЕСЛИ. Первая функция ЕСЛИ выбирает только трассы, которые не являются PI (обратите внимание, что оператор сравнения НЕ вводится как два последовательных символа «меньше» и «больше» <>). Вторая функция ЕСЛИ сравнивает имена гонщиков. (Для ввода функции используйте Ctrl+Shift+Enter.)

Рис. 4.32. Цель – найти минимальное время для каждого гонщика на всех трасса, кроме PI

Если вы используете Excel 2010 года или более позднюю версию, вы можете использовать новую функцию АГРЕГАТ, чтобы еще больше упростить расчет, и обойтись без Ctrl+Shift+Enter. (Функция АГРЕГАТ имеет множество различных применений, но в этой книге рассматриваются только те, что относятся к функциям массива.)

Функция АГРЕГАТ

Рассмотрим туже задачу, что и в предыдущем разделе. Как показано на рис. 4.33, если начать вводить в ячейку F5 название функции АГРЕГАТ, всплывающая подсказка предложит выбрать из двух вариантов. Выберите верхний, содержащий массива в качестве третьего аргумента. Наличие массива в качестве аргумент делает функцию АГРЕГАТ способной обрабатывать операции с массивами без использования Ctrl+Shift+Enter.

Рис. 4.33. Начните вводить АГРЕГАТ и подсказка предложит вам выбрать из двух вариантов

Первый аргумент функции АГРЕГАТ позволяет вам выбрать одну из 19 различных функций (рис. 4.34). Функции с 1-й по 13-ю не могут обработать массивы; функции с 14-й по 19-ю – могут. Кажется странным, что функция 5, МИН, не может быть использовано для операций с массивом, но вы сможете обойти эту проблему с помощью функции 15, НАИМЕНЬШИЙ, устанавливая четвертый аргумент (k) равным единице, т.е. выбирая первое наименьшее число, оно же минимальное.

Рис. 4.34. Первый аргумент функции АГРЕГАТ

Второй аргумент позволяет выбрать один из 7 вариантов обработки строк массива (рис. 4.35). Вариант №6 поможет в нашем случае, потому что позволит игнорировать ошибку, когда хотя бы одно из условий не выполняется.

Рис. 4.35. Второй аргумент функции АГРЕГАТ

Итак, начав набирать функцию АГРЕГАТ, выберите во всплывающем меню в качестве первого аргумента функцию 15, НАИМЕНЬШИЙ (рис. 4.36). А в качестве второго аргумента опцию под номером 6 (рис. 4.37).

Рис. 4.36. Выберите в качестве первого аргумента функцию 15, НАИМЕНЬШИЙ

Рис. 4.37. Выберите в качестве второго аргумента опцию 6 – Пропускать ошибочные значения

После ввода разделите – точки с запятой, третий аргумент функции АГРЕГАТ – массив – выделяется полужирным шрифтом (рис. 4.38). Т.е. третий аргумент функции АГРЕГАТ изначально запрограммирован для обработки массива без использования клавиш Ctrl+Shift+Enter (помните, что это верно только для функций с 14-й по 19-ю).

Рис. 4.38. Третий аргумент функции АГРЕГАТ – массив – изначально запрограммирован для обработки без использования клавиш Ctrl+Shift+Enter

Теперь можно в качестве третьего аргумента функции АГРЕГАТ ввести полученную ранее функцию ЕСЛИ (точнее две вложенные функции ЕСЛИ) для отбора по имени гонщика и всем трекам, кроме PI:

ЕСЛИ($A$3:$A$13<>$F$2;ЕСЛИ($B$3:$B$13=E5;$C$3:$C$13)

Заметим, что если у вас есть массив, который помещен в любой из аргументом функции ЕСЛИ ввод формулы требует нажатия Ctrl+Shift+Enter, даже несмотря на то, что саму функцию ЕСЛИ вы поместите внутрь функции, умеющей работать с массивами, например АГРЕГАТ или СУММПРОИЗВ. Образно говоря, функция ЕСЛИ является козырной и бьет другие функции. Если вы стремитесь избежать ввода Ctrl+Shift+Enter, постарайтесь обойтись без ЕСЛИ.

Чтобы проверить это правило, введите формулу:

=АГРЕГАТ(15;6;ЕСЛИ($A$3:$A$13<>$F$2;ЕСЛИ($B$3:$B$13=E5;$C$3:$C$13));1)

Вы получаете ошибку #ЗНАЧ! Если же вы введете формулу с помощью клавиш Ctrl+Shift+Enter, то формула будет работать, но… это признание вашего поражения, так как вспомните, что цель была – придумать формулу массива, которая не требовала нажатия Ctrl+Shift+Enter. Поэтому, если вы хотите остаться верным своей цели, вы должны скорректировать формулу в аргументе массив и избавиться от ЕСЛИ. Решение основано на замечательном свойстве функции АГРЕГАТ пропускать ошибочные значения! Замените ЕСЛИ($A$3:$A$13<>$F$2;ЕСЛИ($B$3:$B$13=E5;$C$3:$C$13) на $C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5)). Заметьте, что в знаменателе вы должны добавить скобки для каждого оператора сравнения, а также дополнительный набор скобок для всего знаменателя.

Вы поместили в числитель массив значений (время), а в знаменатель – произведение двух условий (операторы сравнения массивов). Только в том случае, когда оба условия вернут значение ИСТИНА, знаменатель будет равен единице.[1] Если хотя бы одно сравнение неверно, знаменатель обращается в ноль. Деление на ноль даст ошибку, и функция АГРЕГАТ ее пропустит.

Если вы выделите аргумент массив в формуле АГРЕГАТ и нажмете F9, то увидите результирующий массив (рис. 4.39).

Рис. 4.39. Выделите аргумент массив в формуле АГРЕГАТ (а) и нажмете F9 (б)

Результирующий массив содержит только числа, которые удовлетворяют обоим условиям (И критерий). Фактически ваша формула массива отфильтровала числа, отвечающие обоим условиям. Теперь вы, наверное, понимаете, почему следовало применить деление. Оно привело к возникновению ошибки #ДЕЛ/0! Произведение не подошло бы, так как привело к появлению элементов массива равным нулю. Именно один их этих нулей и выбрала бы функция МИН. В тоже время ошибки #ДЕЛ/0! не составляют проблемы для функции АГРЕГАТ, поскольку второй ее аргумент содержит опцию 6, дающий команду функции пропускать ошибочные значения. Поскольку мы избавились от функции ЕСЛИ внутри функции АГРЕГАТ, ввод нашей формулы не требует нажатия Ctrl+Shift+Enter. Вы с легкостью «протянете» формулу по столбцу F.

Так что же использовать, функцию АГРЕГАТ с двоичными условиями или формулу с МИН и двумя ЕСЛИ? По времени работы и сложности написания эти две конструкции примерно одинаковы. Но… функция АГРЕГАТ не требует нажатия Ctrl+Shift+Enter. Поэтому я предпочту ее.

Посмотрите, как можно решить эту же задачу с использованием ДМИН (рис. 4.41) и сводной таблицы (рис. 4.42). Обратите внимание на фильтр сводной таблицы не позволяет выбрать «НЕ PI». После создания сводной таблицы используйте фильтр в поле Трасса поставьте галочку в Выделить несколько элементов и снимите галочку напротив PI.

Рис. 4.41. Решение с помощью функции ДМИН и Таблицы данных

Рис. 4.42. Решение с помощью сводной таблицы

Использование условий при вычислении стандартного отклонения. Итак, в этой главе вы узнали, как сделать выборки для нахождения минимума или максимума с использованием функции АГРЕГАТ, конструкции ЕСЛИ(ЕСЛИ()), функции базы данных ДМИН или сводной таблицы. Однако, если бы вам потребовалось рассчитать стандартное отклонение вы могли бы использовать соответствующую БД-функцию, сводную таблицу и конструкцию с ЕСЛИ, но не функцию АГРЕГАТ. Почему? Потому что два стандартных отклонения функции АГРЕГАТ (под номерами 7 и 8 ) не могут обработать массивы.

Для расчета стандартного отклонения воспользуйтесь функцией СТАНДОТКЛОН.В (СТАНДОТКЛОН в версии Excel до 2010) и вложенными функциями ЕСЛИ (рис. 4.43).

Рис. 4.43. Вычисление стандартного отклонения с одним условием

Итак, в этой главе вы научились использовали функцию ЕСЛИ и логические операторы для фильтрации (отбора) значений внутри формулы. Это важные инструменты, которые пригодятся вам и в иных аспектах работы с Excel.

Какой метод выборки на основе критериев предпочесть?

Метод	Преимущества	Недостатки
Сводные таблицы	• Легко создавать, особенно с использованием нескольких критериев.• Легко изменить.	• Требует команды Обновить.• Только 11 функций.
Функции для работы с базой данных	• Просто создавать• Быстрая работа формул.• Отсутствие Ctrl+Shift+Enter.	• Нелегко скопировать.• Данные должны содержать заголовки.
АГРЕГАТ	• Результат изменяется сразу при изменении данных.• Легко скопировать формулу по столбцу.• Отсутствие Ctrl+Shift+Enter.	• Нелегко создавать.• Время расчета больше, чем у БД-функции.• Всего 19 функций и только 6 из них работают с массивами.
Конструкция с одним или несколькими ЕСЛИ	• Результат изменяется сразу при изменении данных.• Легко скопировать формулу по столбцу.• Позволяет воспользоваться 350 формулами Excel.	• Требует Ctrl+Shift+Enter.• Нелегко создавать.• Время расчета больше, чем у БД-функции.

Время работы формул, использованных в этой главе. Как говорилось в главе 2, формулы массива обрабатывающие большие наборы данных, могут увеличить время расчета. Иногда я буду приводить данные о времени работы формул. Это неофициальные результаты, которые получил лично я. Время зависит от многих факторов, и в вашей конкретной ситуации могут время может отличаться (рис. 4.44).

Рис. 4.44. БД-функция работает быстрее, чем конструкция МИН(ЕСЛИ(ЕСЛИ(…))) и АГРЕГАТ

[1] В Excel есть так называемое неявное преобразование типов. Логическое значение ИСТИНА соответствует числу 1, а ЛОЖЬ = 0. Подробнее это будет рассмотрено в главе 11. См. также, например, Что такое -- или как превратить ИСТИНА в 1, а ЛОЖЬ в 0?

Источник

Методы сравнения таблиц в Microsoft Excel

Смотрите также Rangeиз категорииAkazotik прицеплять файлы? ЕслиIf .Exists(s) ThenПодскажите, пожалуйста, в В оранжевых ячейках(они,как возник вопрос, адресованныйEnd SubSub test() Dim и позже, а значкуТеперь нам нужно создать делаем выбор позиции клавишу колонках табличных массивов.Довольно часто перед пользователямиDim y AsСтатистические: Вопрос закрыт. Нашёл да, то я Cells(.Item(s), 1).Interior.Color = чём я ошибаюсь? я сейчас вижу, больше наверное кНо в общем

ws As Worksheet, также для версий

«Вставить функцию» подобное выражение и«Повторяющиеся значения»

Способы сравнения

F5 В нашем случае Excel стоит задача Range, которая подсчитывает сколько корреляционный анализ, позволяющий

сразу же прицеплю vbGreen
Hugo более отвлекают ,нежели
nilem.Во втором массиве тот же перебор,

a, g, f, до Excel 2007. для всех других.. не совпали данные сравнения двух таблицs1 = 1 раз каждый элемент сравнивать массивы различной

именно файлы, чтобNext i: Почему решили что приносят пользы; вобщем, сочетания цифр 48-8(они но усовершенствованный -

Способ 1: простая формула

aLR&, gLR&, i&, с выполнением этогоОткрывается элементов первой таблицы.Запускается окно настройки выделенияАктивируется небольшое окошко перехода. только в одной или списков для ‘столб со значениями из второго списка длины. не гонять никого.End With не все? Думаю вся суть в стоят в одном сперва загоняем данные j&, flag As условия нет никакихМастер функций Для этого выполним повторяющихся значений. Если Щелкаем по кнопке строке. При их выявления в них которые ищем встречался в первом:

БМВBemaEnd Sub все, и даже зеленой ячейке) изначально столбце) не будет,а в массивы, потом Boolean Set ws

проблем. Но в. Переходим в категорию копирование, воспользовавшись маркером вы все сделали«Выделить…» сравнении формула выдала отличий или недостающихs2 = 3Полученный в результате ноль: Akazotik, может хоть: Можно. Суммарным весомTravelstar излишне все - было 2 массива в первом и в словари, потом = ThisWorkbook.Worksheets(«Лист1») aLR
Excel 2007 и

«Статистические» заполнения, как это правильно, то вв его нижнем
результат элементов. Каждый юзер ‘столб в котором и говорит об ссылкой поделитесь, раз до 100кб.: Благодарю! ведь после получения : первая и третьем есть,значит комбинаций
первый словарь сверяем = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Excel 2010 дляи производим выбор мы уже делали данном окне остается левом углу.«ЛОЖЬ» справляется с этой ищем отличиях. нашли?Akazotik
Такой подход приемлемый, результата сравнения продолжаются… вторая части столбца с цифрами 48-8-х-х-х-х с двумя другими. gLR = ws.Cells(ws.Rows.Count, того, чтобы открыть наименования прежде. Ставим курсор только нажать наПосле этого, какой бы. По всем остальным задачей по своему,s3 = 4И, наконец, «высший пилотаж»Ігор Гончаренко: у меня файлики слегка изменю структуру
Вообще со словарём «Шифр». Одну из быть не должно.МожноВариант с признаком 7).End(xlUp).Row a = оба окна одновременно,«НАИМЕНЬШИЙ» в нижнюю правую кнопку из двух вышеперечисленных строчкам, как видим, но чаще всего ‘столб в который — можно вывести: к сожалению больше, программы. С таким было бы быстрее
них нужно каким-то ли в макросе столбца: ws.Range(ws.[a2], ws.Cells(aLR, 3)).Value требуется провести дополнительные. Щелкаем по кнопке часть элемента листа,«OK» вариантов вы не формула сравнения выдала
на решение указанного записываем НЕ найденные отличия отдельным списком.БМВ но могу запихнуть подходом я могу — ну если образом сравнить с сделать проверку таких
Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>Option Explicit g = ws.Range(ws.[g2], манипуляции. Как это«OK» который содержит функцию. Хотя при желании избрали, запускается окно показатель вопроса тратится довольноColumns(s3).Clear
Для этого придется

: Ігор Гончаренко, Я эти в архив. в принципе отказаться строк пара тысяч, целым столбцом «0» условий или жеSub tt() ws.Cells(gLR, 9)).Value ReDim сделать рассказывается в

.СЧЁТЕСЛИ в соответствующем поле выделения групп ячеек.«ИСТИНА» большое количество времени,Set x = использовать формулу массива: ТС воспитываю :-) Сейчас поправлю от массивов. то не важно… , получив при лучше это делатьDim b, c, f(1 To gLR отдельном уроке.Функция, и после преобразования данного окошка можно

Устанавливаем переключатель в

. так как далеко Range(Cells(1, s1), Cells(Cells(Rows.Count,
Выглядит страшновато, но свою , а неІгор ГончаренкоSkyProTravelstar этом массив с с помощью функций oDict1 As Object, — 1, 1Урок: Как открыть ЭксельНАИМЕНЬШИЙ его в маркер выбрать другой цвет позициюКроме того, существует возможность не все подходы

s1).End(xlUp).Row, s1)) работу выполняет отлично ….. :-): а как вычисляется: Можно еще SQL: Вот и по нулей и единиц. листа. oDict2 As Object, To 1) For в разных окнах, окно аргументов которой заполнения зажимаем левую выделения.«Выделить по строкам» с помощью специальной к данной проблемеWith x

;)

он же и «мера похожести массивов» запросом. логике, вроде как Именно в сравненииnilem oDict3 As Object i = 1

Способ 2: выделение групп ячеек

Как видим, существует целый было раскрыто, предназначена кнопку мыши иПосле того, как мы. Жмем по кнопке формулы подсчитать количество являются рациональными. В.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=xRoody_woody успелможете рассказать наИспользовал класс от

всё и проверяется…А возникла трудность.: Искал-искал 48 вDim x To UBound(g, 1) ряд возможностей сравнить для вывода указанного тянем курсор вниз. произведем указанное действие,«OK» несовпадений. Для этого то же время, _: Добрый день.Андрей VG

пальцах или на Nerv строк и действительноВижу, что своим результирующем массиве (кстати,Dim kk, ii, j = 1: таблицы между собой. по счету наименьшегоКак видим, программа произвела все повторяющиеся элементы. выделяем тот элемент существует несколько проверенных, Unique:=TrueСтоит следующая задача:: Михаил, а вы массивах, как вамДопустим вы работаете с
много… по несколько описанием внутри файла не сразу сообразил, y& flag = False
Какой именно вариант значения. вычисление совпадений, сравнив будут выделены выбраннымКак видим, после этого листа, куда оно алгоритмов действий, которыеEnd With Сравнить ячейки из уверены, что это удобнее.
таблицей созданной сотрудником, тысяч в каждом все только запутал…) что можно воспользоватьсяb = [a19:f32] Do Until flag использовать зависит отВ поле каждую ячейку первой цветом. Те элементы, несовпадающие значения строк

Способ 3: условное форматирование

будет выводиться. Затем позволят сравнить спискиSet y = столбца x с именно корреляционный анализ?Akazotik который в неупорядоченный массиве… Описание проблемы в

«Найти и выделить»c = [a35:f48] Or j > того, где именно«Массив» таблицы с данными, которые не совпадают, будут подсвечены отличающимся щелкаем по значку или табличные массивы Range(Cells(1, s2), Cells(Cells(Rows.Count, ячейкой из столбца Что-то не припомню,: Я пытался сделать способ заполняет информацию,А вот факт шапке темы -) — неSet oDict1 = UBound(a, 1) If расположены табличные данные
следует указать координаты которые расположены во останутся окрашенными в оттенком. Кроме того,«Вставить функцию»
в довольно сжатые s2).End(xlUp).Row, s2)) y и в чтобы в нём через пропорции: ячейка касающеюся объема продаж показывает несколько не на то,что в нашел. CreateObject(«scripting.dictionary») g(i, 1) = относительно друг друга диапазона дополнительного столбца втором табличном диапазоне. свой изначальный цвет как можно судить. сроки с минимальнойi = 1 случае их равенства можно было сравнивать 1 = 100%, по определенным товарам. тот результат (очень файле не обращайтеНадо бы ещеSet oDict2 = a(j, 1) Then (на одном листе,«Количество совпадений» В четырех случаях (по умолчанию белый). из содержимого строки
В окне

затратой усилий. ДавайтеFor Each w записать значение ячейки выборки с разным ячейка 2 = Одной из ваших
жаль, что не внимания.) повторы убрать. У CreateObject(«scripting.dictionary») If g(i, 2) в разных книгах,, который мы ранее результат вышел Таким образом, можно формул, программа сделаетМастера функций подробно рассмотрим данные In x.SpecialCells(xlCellTypeVisible)
из строки, совпадающей числом элементов. ?%, но кол-во задач будет – могу сейчас вложить
Формуляр Вас Ексель 2007/10?Set oDict3 = = a(j, 2) на разных листах), преобразовали с помощью
«1» сразу визуально увидеть, активной одну изв группе операторов варианты.If y.Find(w, LookAt:=xlWhole)

с ячейкой изВидимо — это ячеек не совпадает, сравнение. Следует проверить программку…).: Нули и единичкиMCH CreateObject(«scripting.dictionary») Then If Abs(a(j, а также от функции, а в двух в чем отличие ячеек, находящуюся в«Математические»Скачать последнюю версию

Is Nothing Then столбца y из
открытие! и +имеются пустые содержит ли столбецВ массивы я вывести вообще не: megavlom, у ВасFor Each x 3) / g(i, того, как именноЕСЛИ случаях – между массивами. указанных не совпавших
выделяем наименование ExcelCells(i, s3) = столбца z. ЯУчитывая первую ссылку, ячейки. Так что таблицы конкретное значение вгоняю диапазон из проблема. Дальше-то с в качестве примера In [a3:f16] 3) — 1) пользователь желает, чтобы
. Делаем все ссылки«0»При желании можно, наоборот, строках.СУММПРОИЗВЧитайте также: Сравнение двух w так себе представляю то скорее всего я даже в или нет. Конечно разных книг, первый ними чего?

указаны числа 37,If Len(x.Value) Then < 0.03 Then это сравнение выводилось абсолютными.. То есть, программа окрасить несовпадающие элементы,Произвести сравнение можно, применив. Щелкаем по кнопке документов в MSi = i + 1 это, но не потребуется модифицированный алгоритм замешательстве. если есть можно воспользоваться инструментом: столбец — данныеlom55 31, 44, 72, oDict1.Item(x.Value & «|»

flag = True на экран.В поле

не смогла отыскать а те показатели,

Способ 4: комплексная формула

метод условного форматирования.«OK» WordEnd If владею достаточными знаниями. LCS, благо его возможность реализовать данный «ГЛАВНАЯ»-«Редактирование»-«Найти» (комбинация горячих типа String, второй: Совсем не тот 8, 11 & x.Column) =

End If EndАвтор: Максим Тютюшев«K» во второй таблице которые совпадают, оставить Как и в.Существует довольно много способовNextДля понимания: у

как раз можно

метод для массивов клавиш CTRL+F). Однако — Date и компот.)При этом числа x.Value

If j =Фдуч1985указывается, какое по два значения, которые с заливкой прежним предыдущем способе, сравниваемыеАктивируется окно аргументов функции сравнения табличных областей

Me.ShowAllData меня есть 2 на формулах реализовать. разной длины, то при регулярной необходимости при проверке, еслиВот собственно формула,если
72 и 44Next j + 1: Всем добрый вечер! счету наименьшее значение имеются в первом цветом. При этом области должны находитьсяСУММПРОИЗВ в Excel, ноBeep
прайса с артикулами,Типовая задача, возникающая периодически я буду рад выполнения поиска по совпали String-и, то все на кучу встречаются в 3
For Each x Loop If Notочень нужна Ваша нужно вывести. Тут табличном массиве. алгоритм действий практически на одном рабочем, главной задачей которой все их можноEnd Sub названиями и ценами перед каждым пользователемИсправлено таблице данный способ проверяю даты, так слепить: массиве в одном In b flag Then f(i,

помощь. Есть два указываем координаты первойКонечно, данное выражение для тот же, но

листе Excel и является вычисление суммы разделить на триGuest двух дилеров с Excel — сравнитьІгор Гончаренко оказывается весьма неудобным. вот, при проверке200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ( (СМЕЩ($J$7;;;ПОИСКПОЗ($I$5;$L$7:$L$15;-1)-1)=СМЕЩ($A$3;;;СЧЁТЗ($A$3:$A$15)) )*СМЕЩ($C$3;;;СЧЁТЗ($C$3:$C$15)) ) столбце. это ошибка?If Len(x) Then 1) = 1 массива (пример приложил,
ячейки столбца с того, чтобы сравнить в окне настройки быть синхронизированными между произведений выделенного диапазона. большие группы:: Я не силён одинаковыми, но перепутанными между собой два: в колонку А Кроме этого данный работы алгоритма видно,
То есть, всеИнтересно, какая конечная oDict2.Item(x) = x Next ws.Range(ws.[f2], ws.Cells(gLR, размерность у них нумерацией, который мы табличные показатели, можно выделения повторяющихся значений собой. Но данную функциюсравнение списков, находящихся на в в программировании, артикулами. Необходимо найти диапазона с данными пишите 1 массив, инструмент не позволяет что не все действо должно происходить
задача с этимиNext 6)).Value = f условная, на самом недавно добавили. Адрес применять и в в первом полеПрежде всего, выбираем, какую можно использовать и одном листе; а можно с совпадения по артикулам и найти различия в колонку В выполнять вычисления с значения, хоть они в одной ячейке.) числами, что в

For Each x End Subкак проверите деле 10 тыс. оставляем относительным. Щелкаем существующем виде, но вместо параметра табличную область будем

для наших целей.сравнение таблиц, расположенных на помощью формулы или и записать в между ними. Способ — 2-й (размерности

найденным результатом. Каждому и равны, совпадают…ПОИСКПОЗ ищет во итоге хотите получить? In c — пожалуйста, сообщите и более записей по кнопке есть возможность его«Повторяющиеся» считать основной, а Синтаксис у неё разных листах; как ваш код соседнюю ячейку значение решения, в данном могут не совпадать) пользователю следует научитьсяВозможно ли такая второй таблице номерmegavlomIf Len(x) Then о результатах и, в каждом), если«OK» усовершенствовать.
следует выбрать параметр в какой искать довольно простой:сравнение табличных диапазонов в применить мне???? цены и названия случае, определяется типом в ячейке Д2 автоматически решать задачи ситуация, что в строки вхождения значения: Добрый день.Как бы oDict3.Item(x) = x если не трудно
их сравнить, в.Сделаем так, чтобы те«Уникальные» отличия. Последнее давайте=СУММПРОИЗВ(массив1;массив2;…) разных файлах.k61 детали. Что бы исходных данных. можете наблюдать как в Excel. одном из массивов ,введенного в желтую четче обьяснить.Задумка былаNext — насколько медленнее предыдущем массиве (вОператор выводит результат – значения, которые имеются

. После этого нажать

будем делать воВсего в качестве аргументовИменно исходя из этой: Мне пришло грозное проверить действительно лиЕсли списки синхронизированы (отсортированы), вычисляется процент схожестиЧтобы автоматизировать данный процесс в значениях типа ячейку(ближайшего меньшего) - такая -сделать переборkk = oDict1.keys работает этот макрос январе) произошли изменения число во второй таблице, на кнопку
второй таблице. Поэтому можно использовать адреса классификации, прежде всего, письмо из органов это одинаковые детали то все делается массивов по мере стоит воспользоваться формулой String присутствуют буквы назвем этот номер
чисел столбцов каждогоii = oDict1.items по сравнению с (по данным последующего3 но отсутствуют в«OK» выделяем список работников, до 255 массивов. подбираются методы сравнения, о необходимости перехода
и насколько отличаются весьма несложно, т.к. внесения данных в с использованием функций не EN, а Х. Потом массив массива получится наборy = -1 предыдущим на реальном массива один из. Именно оно наименьшее первой, выводились отдельным
. находящийся в ней. Но в нашем а также определяются на лицензионное ПО. цены. надо, по сути,
них и все =ИЛИ() и =СОВПАД(). RUS? / из Х-1 елементов комбинаций.после эти триFor Each x объеме данных. объектов исчез, он из нумерации несовпадающих списком.
Таким образом, будут выделены Переместившись на вкладку случае мы будем конкретные действия иДумаю придётся переходитьБуду очень благодарен сравнить значения в
строго по ВашемуЧтобы легко проверить наличиеnilem нужно сравнить с набора сравнить между In iiФдуч1985 отмечен желтым цветом строк табличных массивов.Прежде всего, немного переработаем именно те показатели,

«Главная» использовать всего два алгоритмы для выполнения на лицензионный Excel, за помощь. соседних ячейках каждой алгоритму товаров в таблице: Редко, но бывает. массивом столбца «0»,состоящим собой и совпадающиеy = y + 1: Спасибо большое! Очень
и обозначен «1»). С помощью маркера нашу формулу которые не совпадают., щелкаем по кнопке массива, к тому задачи. Например, при освежив версию доRoody_woody
строки. Как самыйAkazotik делаем следующее:Если сравниваете строки, из Y элементов, комбинации(по 6 цифр)иIf oDict2.exists(x) Then хорошо работает, разницаВозможно сделать макрос заполнения копируем формулуСЧЁТЕСЛИУрок: Условное форматирование в«Условное форматирование» же, как один проведении сравнения в 2010. :(: Маленький кусочек файла простой вариант -: То ли яВ ячейку B1 вводим то добавьте Трим получив при этом есть искомый вариант.ЗаIf oDict3.exists(x) Then во времени несущественная, для автоматизации этого до самого низа., а именно сделаем
Экселе, которая имеет месторасположение аргумент. разных книгах требуетсяА пока файл скину. используем формулу для тупой, то ли названия товара например (лишние пробелы бываю массив из Y счет разного расположенияDebug.Print kk(y) на 19 тыс. процесса? Чтобы «ушедшие»Теперь, зная номера строк
её одним изТакже сравнить данные можно на ленте вСтавим курсор в поле одновременно открыть два в формате xlsНадо сравнить столбец
сравнения значений, выдающую файлик не работает, – Монитор. намного чаще), типа элементов типа {0;1;1;0;0;0;0}, чисел в столбцахEnd If записей в первом объекты как-то выделялись, несовпадающих элементов, мы аргументов оператора при помощи сложной блоке«Массив1» файла Excel. приложите… А со столбцом на выходе логические ибо в ячейкеВ ячейке B2 вводим такого который в свою

происходит разный наборEnd If случае это занимает к примеру, единицами? можем вставить вЕСЛИ формулы, основой которой«Стили»и выделяем наКроме того, следует сказать,

clever2012 B на наличие значения
Д2 находится функция следующую формулу:200?’200px’:»+(this.scrollHeight+5)+’px’);»>If Trim(arr1(i, 1)) = очередь без проблем вариантов.То что естьNext 10 сек, во Опираться при сравнении ячейку и их. Для этого выделяем является функция

Способ 5: сравнение массивов в разных книгах

. Из выпадающего списка листе сравниваемый диапазон что сравнивать табличные: прикрепляю одинаковых значений иИСТИНА (TRUE) счисл(), которая выдаётОбязательно после ввода формулы Trim(arr2(k, 2)) Then умножается на массив повторы чисел вEnd Sub втором случае около на цены и значения с помощью первую ячейку, вСЧЁТЕСЛИ переходим по пункту данных в первой области имеет смыслk61 если есть совпадение,или рандомное число для подтверждения нажмитеи еще , столбца «2», после разных столбцах массива

Serge_007 минуты.

бюджеты объектов нельзя, функции которой расположен оператор. С помощью данного«Управление правилами» области. После этого только тогда, когда: Проверяем. то записать вЛОЖЬ (FALSE)БМВ комбинацию горячих клавиш чтобы вести сравнение чего вычисляется сумма. -так должно быть.Excel: Объясните как у

Vega-NGC

lumpics.ru

Сравнение двух массивов в excel

 т.к. их колебанияИНДЕКС
СЧЁТЕСЛИ инструмента можно произвести. в поле ставим они имеют похожуюЧисло сообщений - столбец G и:: Ігор Гончаренко, Ну CTRL+SHIFT+Enter. Ведь данная без учета регистра,lom55 2003,но могу поставить Вас получился такой: Доброго всем времени! условно составляют +-5%.
. Выделяем первый элемент. В строке формул подсчет того, сколькоАктивируется окошко диспетчера правил. знак структуру. 1111 !!!!!!! H значение совпадающегоЧисло несовпадений можно посчитать так то наверно формула должна выполняться в самомо верху:

 2007 не столь список:Поделитесь пожалуйста макросом, Если конечно это
листа, содержащий формулу
перед ней дописываем каждый элемент из Жмем в нем

«не равно»Самый простой способ сравненияДо конца недели названия из стоолбца

 формулой: уже перебор.
в массиве. Если модуляSerge_007 существенно.Числа,которых нет сразу37 31 44 72 который будет при никак нельзя заложитьНАИМЕНЬШИЙ выражение выбранного столбца второй на кнопку( данных в двух буду Не поститься, К и цены=СУММПРОИЗВ(—(A2:A20<>B2:B20))Akazotik, Ваш вопрос все сделано правильноКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>Option Compare Text, правила читал; считал в трех массивах 8 11 своём запуске выдавать в код…. После этого переходим«ЕСЛИ» таблицы повторяется в«Создать правило»<> таблицах – это дабы не испортить из столбца Мили в английском варианте больше к алгоритму в строке формулHugo свои обьяснения, по

 будут отсеяны до? окно например: (Выберитеikki в строку формулбез кавычек и первой.

.) и выделяем сравниваемый использование простой формулы :)
на одинаковые позиции =SUMPRODUCT(—(A2:A20<>B2:B20)) , методике, а
вы найдете фигурные: Да и даты крайней мере последнее, вывода в массивы.Если
megavlom первый файл для: а что гарантирует

 и перед наименованием открываем скобку. Далее,Оператор

В запустившемся окне производим диапазон второй области. равенства. Если данные
Hugo
в ячейку рядомЕсли в результате получаем не к тому скобки. там может в внятными.) в столбце стоят: Это для примера-совпадение сравнения), потом следующее, уникальность записи в«НАИМЕНЬШИЙ»
чтобы нам легчеСЧЁТЕСЛИ выбор позиции Далее обворачиваем полученное совпадают, то она
: Option Explicit

 с совпадением. ноль — списки

 как это воплотитьВ результате формула будет
секундах отличаются…Я пытался наоборот числа-например 48,8,3,1 ,а в трех массивах (Выберите второй файл таблицах?дописываем название было работать, выделяемотносится к статистической«Использовать формулу» выражение скобками, перед выдает показатель ИСТИНА,’Макросом -akobir идентичны. В противном в Excel. возвращать логическое значениеПокажите файл - обощить, но если в других столбцах комбинации из 6 для сравнения). Принциппервые три столбца?«ИНДЕКС» в строке формул группе функций. Его. В поле которыми ставим два а если нет,’1.два диапазона в: случае — вAkazotik ИСТИНА или ЛОЖЬ. можно неоригинальный нужна конкретика…Обновил файл,вобщем.) таких чисел нет,то цифр работы макроса: сравнитьпс. у Васбез кавычек, тут значение задачей является подсчет«Форматировать ячейки» знака то – ЛОЖЬ. два массиваRoody_woody них есть различия.

: Желательно воплотить это В зависимости отЕщё раз столкнулсяSerge_007 и комбинаций 48-8,48-3,48-1,8-3,8-1,3-1Hugo два массива на там на Пречистенке же открываем скобку«ЕСЛИ»

 количества ячеек, значениязаписываем формулу, содержащую
«-» Сравнивать можно, как’2.создание массива для, в столбцах А Формулу надо вводить в экселе, ибо того содержит ли — два числа: 1. Вместо #ЗНАЧ! таких не будет.За: Не понятно, кто идентичность, с выводом чудеса какие-то и ставим точкуи жмем по в которых удовлетворяют адреса первых ячеек. В нашем случае числовые данные, так результатов
и В абсолютно как формулу массива, от программирования я
таблица исходное значение совершенно одинаковые всюду

CyberForum.ru

Сравнение массивов (сравнение макросом)

 что должно получиться счет этого происходит формирует эти комбинации. информационного сообщения оФдуч1985 с запятой ( иконке заданному условию. Синтаксис диапазонов сравниваемых столбцов, получилось такое выражение: и текстовые. Недостаток

’3.один перебор n различные данные.
т.е. после ввода довольно далёк или нет. глазом (и в в М5 при отсев.Если вручную, то проделанной работе -: Условно да, первые;«Вставить функцию» данного оператора имеет

 разделенные знаком «не—(A2:A7<>D2:D7) данного способа состоит значений массива в

Roody_woody формулы в ячейкуБМВ Locals тоже) кодом К5 равным 97?megavlom я сгенерил список то есть: Результат
три. Там и ). Затем выделяем в . такой вид: равно» (Щелкаем по кнопке в том, что словарь : Простите, ошибся. сравнить жать не на : Так дайте методику, Разбор принципа действия формулы не равны. Но 2. И почему?: Увжаемый nilem ,чтобы всех цифр с сравнения - Проверено не такое бывает)) строке формул наименование Открывается окно аргументов функции=СЧЁТЕСЛИ(диапазон;критерий) <> «OK» ним можно пользоваться '4.m проверок массива A c J Enter тут помогут воплотить. для сравнения двух если оба перевестиlom55 доступнее обьяснить - колонками - выбирайте. листов 8, Найдено ikki «ИНДЕКС» ЕСЛИ Аргумент ). Только перед данным . только в том
на наличие вYouGreed, а на Просто по ссылке столбцов разных таблиц: в string -: 1) 36. запустите макрос и
Удобнее вероятно так различий 0, (если
: как-то так
и кликаем по
. Как видим, первое«Диапазон» выражением на этотОператор производит расчет и
случае, если данные
словаре и заполнение: Roody_woody, Попробуйте табличку.
Ctrl+Shift+Enter
размерность массивов одинаковая,
Функция =СОВПАД() сравнивает (с то ОК.
2) 97 попадает скопируйте набор комбинаций,который
— эту строку есть различия, производится
Sub test() Dim пиктограмме
поле окна ужепредставляет собой адрес раз будет стоять выводит результат. Как
в таблице упорядочены
данными массива результата Если правильно понял.
. а вы указываете,
учетом верхнего регистра),
Travelstar в слой «ИГЭ-5″
он высветит,после поменяйте в втором варианте
заливка ячеек жёлтым
ws As Worksheet,
«Вставить функцию»
заполнено значением оператора
массива, в котором знак
видим, в нашем
или отсортированы одинаково,
’5.выгрузка результатов (тут
200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(ЗАМЕНИТЬ($A1;1;2;82);$J$1:$M$15;СТОЛБЕЦ(B1););»»)
Если с отличающимися ячейками
что у вас
являются ли два
: Спасибо за подсказки,

 — номер 3 местами массивы1 и2 напишите так: цветом или красным)…
 a, g, f,.СЧЁТЕСЛИ

 производится подсчет совпадающих«=» случае результат равен синхронизированы и имеют нет предварительной очистки

akobir надо что сделать, разная. Вот еще
значения идентичными или но не помогло… во второй таблице. и опять запустите
If Len(x.Value) ThenЕсли есть что dict As Object,После этого открывается небольшое
. Но нам нужно значений.. Кроме того, ко числу
равное количество строчек. диапазона)
: то подойдет другой вариант, и тоже
нет. Если да,
Вот несколько файлов
Он в расчет
макрос.Сравните результаты первого
oDict1.Item(x.Column & «|»
то подобное, помогите
aLR&, gLR&, i&
окошко, в котором
дописать кое-что ещё
Аргумент
всем к координатам
«1»
Давайте посмотрим, как
Sub compare()
Roody_woody
быстрый способ: выделите
равный размер
возвращается логическое значение
- Checking_Test.xlsm -
не берется.Над ним
и второго массивов
& x.Value) =
пожалуйста найти.
On Error Resume
нужно определить, ссылочный
в это поле.
«Критерий»
столбцов в данной
, то есть, это
использовать данный способ

Dim a(), b(),, У вас в оба столбца иAkazotik ИСТИНА. Учитывая тот программа в этой же

 ,они будут отличаться,а x.Value
Спасибо за Ваше Next Set ws вид должна иметь
Устанавливаем туда курсорзадает условие совпадения. формуле нужно применить означает, что в на практике на

 c(), i As столбце J числа нажмите клавишу: У меня разное факт что формула- Проверяемый файл.xls smile таблице находятся 2
по идее должнытогда список будет внимание и помощь! = ThisWorkbook.Worksheets(«Г‹ГЁГ±ГІ1») aLR

 функция и к уже В нашем случае абсолютную адресацию. Для

 сравниваемых списках было примере двух таблиц, Long, ii As сохранены как текст.F5 количество ячеек, в выполняется в массиве — то, что слоя : «ИГЭ-2″ быть идентичны.

 с колонкой впереди.megavlom = ws.Cells(ws.Rows.Count, 1).End(xlUp).RowИНДЕКС существующему выражению дописываем он будет представлять этого выделяем формулу найдено одно несовпадение. размещенных на одном Long Для начала нужно, затем в открывшемся одном случае 192, функция СОВПАД сравнивает будет когда-то проверяться и «ИГЭ-3» сnilemЕсли выгрузить на

: Добрый вечер.Есть три gLR = ws.Cells(ws.Rows.Count,или предназначенный для«=0» собой координаты конкретных курсором и трижды smile Если бы списки листе.
’1. их преобразовать в окне кнопку

 в другом 283, значение в ячейке // пока пустой весом 17 и: По всякому менял лист и отсортировать,
массива с числами.Необходимо 7).End(xlUp).Row a = работы с массивами.без кавычек. ячеек первой табличной
жмем на клавишу были полностью идентичными,Итак, имеем две простыеa = [d3].CurrentRegion.Value

 число. Я простоВыделить (Special) + имеются пустые. B1 с каждым- Отчёт PCN.xls 19 соответственно. — одно и то получите сравнить данные в ws.Range(ws.[a2], ws.Cells(aLR, 3)).Value Нам нужен второйПосле этого переходим к области.F4 то результат бы таблицы со спискамиb = [f3].CurrentRegion.Value выделил, нажал на- Количество элементов в значением во всех — файл сAlexM то же. Только1|0 них и при g = ws.Range(ws.[g2], вариант. Он установлен полюВыделяем первый элемент дополнительного. Как видим, около был равен числу

 работников предприятия и’2. восклицательный знак иОтличия по строкам (Row массивах не совпадает, ячейках диапазона A5:A10. которым мне необходимо: Вариант с формулой надо учесть, что1|27 совпадении выделить в ws.Cells(gLR, 9)).Value ReDim по умолчанию, так

«Значение если истина» столбца, в котором всех адресов столбцов«0» их окладами. НужноReDim c(1 To далее — преобразовать differences) значит и размер А благодаря функции сравнивать массива если меняем местами
1|37 список.Если по отдельности f(1 To gLR что в данном
. Тут мы воспользуемся

 будет производиться подсчет появился знак доллара,.

 сравнить списки сотрудников UBound(a), 1 To в число.. В последних версиях их не может =ИЛИ() формула возвращает- Отчёт Sat.xls200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ((A$3:A$15=ТРАНСП(ЕСЛИ(H$7:H$15 верхние таблички, то1|46 перебором каждый,это понятно,а

excelworld.ru

Сравнение двух одномерных массивов с разным числом элементов

 — 1, 1 окошке просто щелкаем
ещё одной вложенной количества совпадений. Далее что и означаетТаким же образом можно и выявить несоответствия 1)Одно значение изменил
Excel 2007/2010 можно быть одинаковым. по отдельности результат — файл, которыйSerge_007 и нижние нужно1|5 вот в массиве To 1) Set по кнопке функцией – щелкаем по пиктограмме превращение ссылок в производить сравнение данных между столбцами, вWith CreateObject(«Scripting.Dictionary») для проверки. также воспользоваться кнопкойAkazotik вычислений функции =СОВПАД(). необходимо сравнить с
: Алекс, а зачем поменять соответственно. Визуально

2|0 пока понимаю не dict = CreateObject(«Scripting.Dictionary»)«OK»СТРОКА«Вставить функцию»

 абсолютные. Для нашего в таблицах, которые
которых размещены фамилии.’3.Смотрите файл.Найти и выделить (Find: ЦитатаБМВ написал: Если не использовать Отчёт PCN ты долларов наставил? — картинки УФ2|31 до конца. For i =.. Вписываем слово. конкретного случая формула расположены на разныхДля этого нам понадобитсяFor i =Roody_woody & Select) -Так дайте методику, функцию ИЛИ, тогда
Алгоритм пока пишется, Формула ведь в должны совпадать в2|8kim 1 To UBound(a,Запускается окно аргументов функции«СТРОКА»

Происходит запуск примет следующий вид: листах. Но в дополнительный столбец на 1 To UBound(b)

: Решение найдено. Выделение группы ячеек тут помогут воплотить.
формула будет возвращать это наработки. одной ячейке будет каждой паре верхних3|0: Добрый вечер! 1) dict.Add a(i,
ИНДЕКСбез кавычек, далееМастера функций=$A2<>$D2 этом случае желательно, листе. Вписываем туда.Item(b(i, 1)) =В G1 и (Go to Special) Собственно вот так только результат первогоЕсли будут уЕщё вариант: и нижних табличек.3|1Вы не первый 1) & «|». Данный оператор предназначен открываем скобки и

. Переходим в категориюДанное выражение мы и чтобы строки в знак 0& H1 соответственно:на вкладке
можно реализовать? сравнения. кого-нибудь интересные замечания

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ((A3:A15=ТРАНСП(ЕСЛИ(ПОИСКПОЗ(;K5-K7:K15)>СТРОКА(1:10);I7:I15)))*B3:B15)Теперь результат пишем3|15 день на форуме & a(i, 2)
для вывода значения,

 указываем координаты первой«Статистические»
записываем в вышеуказанное них были пронумерованы.«=»Next200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР($A1;$J:$M;4;0);ЕСЛИОШИБКА(ВПР($A1&»»;$J:$M;4;0);»—«))Главная (Home)АlехМВот как можно применять буду признателен.lom55 в строку через3|17

 и должны прекрасно & «|» & которое расположено в
ячейки с фамилией

. Находим в перечне поле. После этого В остальном процедура. Затем кликаем по'4. Код200?'200px':''+(this.scrollHeight+5)+'px');">=ЕСЛИОШИБКА(ВПР($A1;$J:$K;2;0);ЕСЛИОШИБКА(ВПР($A1&"";$J:$K;2;0);"--"))Excel выделит ячейки, отличающиеся

: Akazotik, а файл сразу несколько такихTravelstar
: Точно! Транспонировать же символ «|» -3|5

 знать, что прикрепленный a(i, 3), «» определенном массиве в во второй таблице, наименование
щёлкаем по кнопке сравнения практически точно
первому наименованию, которое


excelworld.ru

Сравнение двумерных массивов (Макросы/Sub)

For i =и протянуть вниз
содержанием (по строкам). вы тут до формул на практике: и ещё 2
надо.) так, вроде, проще.3|72 файл-пример значительно ускорит Next For i указанной строке. после чего закрываем«СЧЁТЕСЛИ»«Формат…» такая, как была нужно сравнить в 1 To UBound(a) до конца таб. Затем их можно сих не выложили. при сравнении двух файлаСпасибо большое Вам Проверьте.3|8 решение. Или Вам = 1 ToКак видим, поле скобки. Конкретно в. После его выделения . описана выше, кроме первом списке. ОпятьIf Not .exists(a(i,Во вложении результат. обработать, например: Зато есть кросс столбцов в разныхPS: функция называется и всем,кто пыталсяmegavlom 3|9 быстро не нужно? UBound(g, 1) If «Номер строки» нашем случае в
щелкаем по кнопкеАктивируется окно
того факта, что ставим символ

 1)) Then iiВсем большое спасибозалить цветом или как-то на другом форуме таблицах одновременно: Compare(arr1 as Variant, помочь.
: За внимание спасибо,проверить4|0 К тому же Not dict.exists(g(i, 1)уже заполнено значениями

 поле«OK»«Формат ячеек» при внесении формулы«=» = ii + за помощь! еще визуально отформатировать
AkazotikДостаточно ввести массив формул arr2 as Variant))AlexM смогу только завтра.4|1
это является нарушением & «|» & функции«Значение если истина».. Идем во вкладку придется переключаться междус клавиатуры. Далее 1: c(ii, 1)Формулы нужно оформлятьочистить клавишей: АlехМ,я не могу в одну ячейкуHugo
: Когда файл отправилmegavlom4|11 3 пункта g(i, 2) &НАИМЕНЬШИЙполучилось следующее выражение:

Происходит запуск окна аргументов«Заливка»
листами. В нашем кликаем по первой = a(i, 1) тегами (кнопка fx)!Delete
выложить файл, т.к. (E2), потом скопировать
: Не, я отказываюсь понял что $: Здравствуйте.Уважаемый nilem первое4|15правил форума
«|» & g(i,

. От уже существующегоСТРОКА(D2) оператора. Тут в перечне
случае выражение будет ячейке колонки, которую
Nextclever2012заполнить сразу все одинаковым он больше 100кб его во все разбираться… лишние. Решил не решение более удачное..,при4|46

… 3)) Then f(i, там значения следует
Теперь оператор
СЧЁТЕСЛИ цветов останавливаем выбор
иметь следующий вид: мы сравниваем, воEnd With: Здравствуйте! Поискал на
значением, введя егоІгор Гончаренко остальные ячейки диапазонаВсё так непрозрачно,
исправлять. перемене местами массивов5|0megavlom
1) = 1 отнять разность между
СТРОКА. Как видим, наименования на цвете, которым

=B2=Лист2!B2 второй таблице. Получилось’5.
форуме нужный мне и нажав: БМВ,

 E3:E8. Обратите внимание, эта привязка кФормула созданная совместными
всё таки часть5|12: Извините,что не доглядел Next ws.Range(ws.[f2], ws.Cells(gLR, нумерацией листа Excelбудет сообщать функции
полей в этом хотим окрашивать теТо есть, как видим, выражение следующего типа:[h3].Resize(ii, 1) = ответ, не нашел.Ctrl+Enterа клянчить у что теперь мы
активной в некий усилиями комбинаций меняется(так и5|37 отсутствие файла.Хотя с

 6)).Value = f и внутренней нумерациейЕСЛИ окне соответствуют названиям элементы, где данные перед координатами данных,=A2=D2
cЕсть два числовых
удалить все строки с

 автора «расскажите как используем абсолютные адреса момент книге/листу… Ещё

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ((A3:A15=ТРАНСП(ЕСЛИ(H7:H15 должно быть). Фильтрацию
5|44 нашей связью... End Sub табличной области. Как номер строки, в аргументов. не будут совпадать. которые расположены на Хотя, конечно, в каждомEnd Sub столбца - задача выделенными ячейками, используя считать, расскажите как ссылок на диапазон куча MISSING, ну Travelstar этого я сделаю5|7 HugoФдуч1985 видим, над табличными которой расположена конкретная Устанавливаем курсор в поле Жмем на кнопку

 других листах, отличных конкретном случае координаты
Hugo сравнить первый массив команду считать…» — это $A$2:$A$12 во втором да это меньшее…

: Доброго времени суток! функциями листа.Ваше решение5|74
: Не совсем понял,: Круто! Хорошо работает!

excelworld.ru

Как сравнить два столбца таблицы Excel на совпадения значений

значениями у нас фамилия, и в«Диапазон»«OK» от того, где будут отличаться, но: Карен, чего-то врёт со вторым иГлавная — Удалить - не перебор? аргументе функции СОВПАД.Покажите два файла,Прошу помощи в меня устраивает,поэтому наверное6|0 как Вам нужен А можно еще только шапка. Это случае, когда условие,. После этого, зажав. выводится результат сравнения, суть останется одинаковой. фильтр — вот вывести те числа Удалить строки с

Функция СОВПАД позволяет сравнить два столбца таблицы

АlехМ,Скачать формулу для сравнения напишите код открытия написании функции для

тему можно закрыть.Спасибо.6|31 список, да и

заложить в код значит, что разница заданное в первом
левую кнопку мыши,Вернувшись в окно создания
указывается номер листаЩелкаем по клавише эти есть в в отдельную строку листа (Home -а выложил бы двух столбцов таблиц этих конкретных файлов, сравнения двух двумерныхlom55

6|7 это вероятно не изменение площади в составляет одну строку. поле, будет выполняться, выделяем все значения правила форматирования, жмем

и восклицательный знак.

Enter обоих списках: либо столбец, которых

Delete — Delete автор файл, что на совпадение определения диапазонов, передачу массивов размерностью .: Доброго времени суток.megavlom главное — поэтому районе 3% или Поэтому дописываем в функция столбца с фамилиями на кнопкуСравнение можно произвести при, чтобы получить результаты1-38/2012 нет во втором Rows) измениться бы? ВыВ первом аргументе должны диапазонов на сравнение200?’200px’:»+(this.scrollHeight+5)+’px’);»>Сравнительно недавно начал

: Список -просто вывод пока вывожу в это космос получится? полеЕСЛИ второй таблицы. Как

«OK» помощи инструмента выделения сравнения. Как видим,1-52/2012 массиве.и т.д. знаете как посчитать быть относительные адреса — вот тогдаFunction Compare(arr1 As осваивать excel и

всех совпадающих в окно отладки (можноikki

«Номер строки»будет выводить этот видим, координаты тут. групп ячеек. С

exceltable.com

Сравнение двух массивов чисел в эксель

 при сравнении первых1-65/2012Помогите кто чемЕсли списки разного размера «схожесть» двух массивов? ссылок на ячейки можно посмотреть что Variant, arr2 As в поисках решения 3 массивах комбинаций.К
на лист/массив/строку выводить):: и где у
значение номер в ячейку. же попадают вПосле автоматического перемещения в его помощью также ячеек обоих списков1-114/2012
может!!! и не отсортированы
дайте мне ссылку (как и в где как сравнивается Variant) своей задачи набрел сожалению проверить макрос200?’200px’:»+(this.scrollHeight+5)+’px’);»>Option Explicit вас площадь? я«-1»

 Жмем на кнопку указанное поле. Но окно

 можно сравнивать только программа указала показатель1-117/2012

Заранее выражаю благодарность (элементы идут в на раздел математики предыдущем примере). или нет.’ массивы типа на ваш прекрасный

 и отписаться смогуSub tt() не телепат, сорри

без кавычек.«OK» для наших целей«Диспетчера правил» синхронизированные и упорядоченные«ИСТИНА»

1-109/2012 откликнувшимся на проблему! разном порядке), то
«массивы и схожести»AkazotikЯ так и Variant — хранят

 форум.) В поиске только второго числа.Dim a, b,это будетВ поле. следует сделать данныйщелкаем по кнопке списки. Кроме того,, что означает совпадение1-84/2012k61 придется идти другим я почитаю и: Приветствую всех. Возникла
не смог ничего

 диапазоны значений - решения не нашел.Hugo c, oDict1 Asсовершенно«Массив»Как видим, первый результат адрес абсолютным. Для«OK» в этом случае данных.У меня недавно: Sub wwww() путем.

 тоже буду знать. проблема с сравнением запустить — какие каждый по 2Столкнулся я со: Хорошо. Object, oDict2 Asдругой код.

указываем адрес диапазона отображается, как этого выделяем данныеи в нем.
списки должны располагатьсяТеперь нам нужно провести было 11111 -Dim x AsСамое простое и быстроеБМВ

 массивов в экселе. файлы открывать, что столбца следующей трудностью :Первый вариант -

 Object, oDict3 Asнужен другой подход значений второй таблицы.«ЛОЖЬ» координаты в полеТеперь во второй таблице рядом друг с аналогичную операцию и тоже пару постов Range

 решение: включить цветовое: Ігор Гончаренко, Суть вопроса: есть нажимать…Sheets(1).Activate есть массив из как раз выводит Object — и я При этом все. Это означает, что

 и жмем на элементы, которые имеют
другом на одном с остальными ячейками разлогиненным писал :)Dim y As

 выделение отличий, используяЯ надеялся что два массива разнойSkyProDim i As N элементов и

 список всех совпадающих.Dim x пока даже не координаты делаем абсолютными,

 значение не удовлетворяет клавишу
данные, несовпадающие с листе. обеих таблиц вHugo Range
условное форматирование. Выделите
ТС поймет что длины с числами.: В месте, где Long массив из MВторой вариант -a = [a3:f16] определился, какой именно. то есть, ставим условиям оператора

F4 соответствующими значениями первой
Выделяем сравниваемые массивы. Переходим тех колонках, которые: И кстати -s1 = 1 оба диапазона с проблема не с Требуется найти меру у вас функцияDim k As элементов ,содержащий все список с признакомb = [a19:f32]кстати, скорее всего, перед ними знакЕСЛИ. табличной области, будут во вкладку
мы сравниваем. Но

 числовыми массивами тут ‘столб со значениями данными и выберите
Excel, а с похожести данных массивов сравнения, выведите на Long значения первого массива колонок. В разныхc = [a35:f48] будет работать на доллара уже ранее. То есть, перваяКак видим, ссылка приняла

 выделены выбранным цветом.«Главная» можно просто провести и «не пахнет»! которые ищем

 на вкладке математическим аппаратом. В в процентах. лист два массива

For i = (где M>N). Нужно

 колонках могут бытьSet oDict1 = порядок медленнее. описанным нами способом. фамилия присутствует в
абсолютную форму, чтоСуществует ещё один способ

. Далее щелкаем по копирование формулы, чтоGuests2 = 3Главная — Условное форматирование конце концов перваяСслыка на пример и покажите их LBound(arr1) To UBound(arr1) их сравнить и одинаковые числа.
CreateObject(«scripting.dictionary»)Фдуч1985Жмем на кнопку обоих списках. характеризуется наличием знаков применения условного форматирования

planetaexcel.ru

Поиск отличий в двух списках

значку позволит существенно сэкономить: Да, действительно врёт. ‘столб в котором — Правила выделения ссылка худо-бедно дает подобной задачи: здесь.For k = получить массив из

Вариант 1. Синхронные списки

nilemSet oDict2 =: Ой Площадь обозначается«OK»С помощью маркера заполнения, доллара. для выполнения поставленной«Найти и выделить» время. Особенно данный :( ищем ячеек — Повторяющиеся понимание что имеетсяБыла попытка решитьВывести можно вот LBound(arr2) To UBound(arr2)

M элементов (нулей: Вариант с УФ,

CreateObject(«scripting.dictionary»)

в 3-м столбце).

уже привычным способомЗатем переходим к полю задачи. Как и, который располагается на фактор важен приСкрытые фильтром неs3 = 4 значения (Home - в виду, но данную задачу через так:If arr1(i, 1) и единиц в доп. табличками, формулами

Set oDict3 =ikkiПосле вывода результат на копируем выражение оператора«Критерий» предыдущие варианты, он ленте в блоке сравнивании списков с проверяет? ‘столб в который Conditional formatting - доп условие, о пропорции, но в200?’200px’:»+(this.scrollHeight+5)+’px’);»>[a1].resize(ubound(имя_массива), 2) = имя_массива = arr2(k, 2) местах совпадения значений и макросом (хотел CreateObject(«scripting.dictionary»): давайте на всякий экран протягиваем функциюЕСЛИ, установив туда курсор.

требует расположения обоих инструментов большим количеством строк.У нас эвакуация

записываем НЕ найденные Highlight cell rules
разной размерности, делает связи с наличием
Travelstar Then ‘ тут елементов — все еще сводную прикрутить
For Each x случай уточним задачу? с помощью маркерана весь столбец. Щелкаем по первому сравниваемых областей на«Редактирование»Процедуру копирования легче всего
по случаю окончания

Вариант 2. Перемешанные списки

Columns(s3).Clear — Duplicate Values) её бесполезной. Беда пустых ячеек и: Вот и значения сравниваю первую колонку

это в одной — не придумалось In aнадо: заполнения до конца Как видим, по элементу с фамилиями одном листе, но. Открывается список, в выполнить при помощи рабочего дня. ЗавтраSet x =: в том, что разной длины массивов

массивов первого массива и строке). Желательно с)If Len(x) Thenдля каждой записи столбца вниз. Как двум позициям, которые

в первом табличном в отличие от котором следует выбрать маркера заполнения. Наводим разберу…. Range(Cells(1, s1), Cells(Cells(Rows.Count,Если выбрать опцию при наличии методики

идея невозымела успеха.nilem вторую колонку второго  помощью формул, такСортировка по 6-ти oDict1.Item(x) = x из «правой» таблицы видим, обе фамилии, присутствуют во второй диапазоне. В данном

ранее описанных способов, позицию курсор на правый

Подпись: Карен. s1).End(xlUp).Row, s1))Повторяющиеся задача становится, скорееЗаранее спасибо за

: например: массива как на освоение

planetaexcel.ru

Сравнение массивов (Формулы/Formulas)

 столбцам почему-то неNext
найти в «левой» которые присутствуют во таблице, но отсутствуют случае оставляем ссылку условие синхронизации или«Выделение группы ячеек…» нижний угол ячейки,clever2012Set y =, то Excel выделит всего ,элементарной для помощь.200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub ertert()If arr1(i, 2)
макросов пока нет идет, сделал поFor Each x таблице хотя бы второй таблице, но в первой, формула относительной. После того, сортировки данных не. где мы получили: Спасибо за суету Range(Cells(1, s2), Cells(Cells(Rows.Count, цветом совпадения в кодирования , аCcылки на файликDim x, i&,
= arr2(k, 1) времени да и

 3-м. In b одну в точности
отсутствуют в первой, выдает номера строк. как она отобразилась будет являться обязательным,Кроме того, в нужное показатель Карен. Буду ждать s2).End(xlUp).Row, s2)) наших списках, если может и нет. с массивами и s$ Then ‘ если большой необходимости.

MCHIf Len(x) Then соответствующую ей по выведены в отдельныйОтступаем от табличной области в поле, можно

 что выгодно отличает нам окно выделения«ИСТИНА»

 завтрашнего дня.i = 1 опцию
P.S. Бессонница? :-)

 попытками сравнения (Файлx = Range(«A1»).CurrentRegion.Value ввреху всё ОК,Свои старания прикрепляю: Ну и мой oDict2.Item(x) = x первым двум полям диапазон. вправо и заполняем щелкать по кнопке данный вариант от группы ячеек можно. При этом он
HugoFor Each w
Уникальные

АlехМ «Тон чистый», вкладка
With CreateObject(«Scripting.Dictionary») то сравниваю вторую
в файле.)
вариант формулами (в
Next запись, причем значение
При сравнении диапазонов в
колонку номерами по«OK»
ранее описанных. попасть и другим

excelworld.ru

Сравнение двух числовых массивов.

 должен преобразоваться в: Ну ждите, ждите… In x- различия.
: Возможно не массивы 1+2), а так.CompareMode = 1 колонку первого иФормуляр желтых ячейках)For Each x третьего поля должно разных книгах можно

порядку, начиная от.

Производим выделение областей, которые способом. Данный вариант

 черный крестик. Это :)
If y.Find(w, LookAt:=xlWhole)Цветовое выделение, однако, не
нужно сравнивать. же на изначальные
For i = первую колонку второго…: Если цифры в
megavlom In c отличаться не более
использовать перечисленные выше1В элемент листа выводится
нужно сравнить.
особенно будет полезен и есть маркерFishka
Is Nothing Then всегда удобно, особенноНа другом форуме
массивы (Файлы «Фраза
2 To UBound(x)Cells(i, 2).Interior.Color =
ст-це 2 всегда: Здравствуйте Уважаемые Форумчане.Спасибо
If Len(x) Then чем на 3%.
способы, исключая те
. Количество номеров должно
результат. Он равен
Выполняем переход во вкладку
тем пользователям, у

 заполнения. Жмем левую: clever2012
Cells(i, s3) = для больших таблиц.
файл есть, и 1″ и «Фраза
.Item(x(i, 1) & 255 ‘ ну совпадают с номером
за Ваш труд oDict3.Item(x) = xесли ни одной
варианты, где требуется совпадать с количеством числу
под названием
которых установлена версия кнопку мыши иВ надстройке есть
w
Также, если внутри в файле есть,
2″, вкладки Тон)
x(i, 2)) =
и если совпало буквы, то вспомогательная и помощь.Вам nilem
Next
такой записи не размещение обоих табличных
строк во второй«1»
«Главная» программы ранее Excel
тянем курсор вниз
«Сравнение диапазонов» (раздел
i = i + 1
самих списков элементы
как автор написал,
Akazotik

 i — что-то делаю.. таблица не нужна. особое спасибо.Вы догадалисьFor Each x найдено, то напротив областей на одном

 сравниваемой таблице. Чтобы. Это означает, что. Делаем щелчок по 2007, так как на количество строчек
2). Ваши столбцыEnd If могут повторяться, то формулы сравнения массивов.
: надо составить функциюNext iEnd If

lom55 ,что мне надо.Сейчас

 In oDict1.Items искомой записи слева
листе. Главное условие ускорить процедуру нумерации,
в перечне имен кнопке метод через кнопку в сравниваемых табличных

 сравнились и данныеNext

этот способ не
К сожалению формул сравнения элементов массива
x = Range(«D1»).CurrentRegion.ValueEnd If
: Спасибо,что откликнулись! постараюсь понять макрос.ВозможноIf oDict2.exists(x) Then
ставим единичку. для проведения процедуры можно также воспользоваться второй таблицы фамилия
«Условное форматирование»«Найти и выделить» массивах.

правильно вывелись.
Beep подойдет. таких там неBema

For i =
Next k
Вторая таблица не

задам пару вопросов
If oDict3.exists(x) Thenтак? сравнения в этом

маркером заполнения.

«Гринев В. П.»
. В активировавшемся спискеэти приложения не
Как видим, теперь вclever2012
End Sub

В качестве альтернативы можно
обнаружил. Зато есть: Не хорошо помогающих
1 To UBound(x)Next i спомагательная — она по нему(если что-тоDebug.Print x
Фдуч1985

случае – это

После этого выделяем первую
, которая является первой выбираем позицию

поддерживают. Выделяем массивы,

 дополнительном столбце отобразились: Замечательная вещь. Спасибоk61 использовать функцию формула определяющая абсолютную

по ссылкам гонять.
s = x(i,
End Function
имеет свое назначение.)
не пойму).
End If
: Точно, так

открытие окон обоих ячейку справа от в списке первого«Правила выделения ячеек»

 которые желаем сравнить, все результаты сравнения за совет. Тема: Sub wwww()

СЧЁТЕСЛИ величину разницы некогоAkazotik
2) & x(i,Данные сравниваются, но
Числа в столбцеmegavlomEnd Ifikki
файлов одновременно. Для

 колонки с номерами табличного массива, встречается. В следующем меню и жмем на

 данных в двух закрытаDim x As

(COUNTIF) значения и 100.

: А тут можно 1) почему-то не все. «2» не совпадают.: Добрый вечер.У меня

Next: проверяйте версий Excel 2013 и щелкаем по

planetaexcel.ru

один раз.

Источник

billi

Пользователь

Сообщений: 64
Регистрация: 24.07.2014

24.07.2014 16:06:17

Доброго всем времени суток. Уважаемые гуру нужен небольшой хелп. Задача следущая, сравнить значение ячейки с массивом и если совпадение присутствует, то должно быть истина, если нету , то ложь. Попробовал ПОИСКПОЗ , вроде то что надо , но она говорит номер строки, а хотелось бы просто истина или ложь.

Источник

Сравнение ячеек в Excel

Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству вхождений определенного символа, а также вы научитесь сравнивать между собой сразу несколько ячеек.

При использовании Excel для анализа данных точность является наиболее важной задачей. Неправильная информация приводит к срыву сроков, неверной оценке тенденций, неправильным решениям и потере доходов.

Несмотря на то, что ваши формулы Excel будут абсолютно верны, их результаты могут быть ошибочными из-за некорректных значений в ваших таблицах. Сравнивать две ячейки вручную несложно, но заметить разницу между сотнями и тысячами текстовых и числовых значений практически невозможно.

Как сравнить две ячейки без учета и с учетом регистра букв.
Сравниваем сразу несколько ячеек.
Сравнение нескольких ячеек с образцом
Содержимое какой ячейки длиннее?
Одинаковы ли ячейки по количеству вхождений определённого символа?

В этом руководстве вы узнаете, как автоматизировать утомительную и чреватую ошибками задачу сравнения ячеек, а также какие формулы лучше всего использовать в каждом конкретном случае.

Как сравнить две ячейки в Excel.

Существует два разных способа выяснения тождества текстовых данных в Excel в зависимости от того, требуется ли это делать с учетом регистра или без него.

Формула без учета регистра.

Чтобы сопоставить две ячейки в Excel без учета регистра, используйте самый простой вариант:

=A1=B1

Где A1 и B1 – объекты для сопоставления. Результатом являются логические значения ИСТИНА и ЛОЖЬ.

Если вы хотите в качестве результата вывести свои собственные сообщения для обозначения совпадений и различий, вставьте приведенный выше оператор в логический тест функции ЕСЛИ. Например:

=ЕСЛИ(A2=B2; «Совпадает»; «НЕ совпадает»)

Как вы видите на скриншоте ниже, обе формулы одинаково хорошо сравнивают текст, даты и числа:

Формула с учетом регистра.

В некоторых ситуациях может быть важно не просто сравнить текст, но и учесть регистр символов. Сопоставлениес учетом регистра можно выполнить с помощью функции Excel СОВПАД (EXACT в английской версии):

СОВПАД(текст1; текст2)

Где текст1 и текст2 — две сравниваемые ячейки.

Предполагая, что ваши значения находятся в A2 и B2, расчёт выглядит следующим образом:

=СОВПАД(A2; B2)

В результате вы получите ИСТИНА для текстовых данных, точно совпадающих с регистром каждого символа, в противном случае — ЛОЖЬ.

Если вы хотите, чтобы функция СОВПАД выдавала другие результаты, вставьте ее в формулу ЕСЛИ и введите свой собственный текст для вариантов ответа:

=ЕСЛИ(СОВПАД(A2; B2); «Совпадает»; «НЕ совпадает»)

На следующем скриншоте показаны результаты с учетом регистра:

Как сравнить несколько ячеек в Excel

Чтобы сопоставить между собой более 2 ячеек, используйте выражения, описанные в приведенных выше примерах, в сочетании с оператором И.

Формула без учета регистра для более чем двух ячеек

В зависимости от того, как вы хотите отображать результаты, используйте одну из следующих формул:

=И(A2=B2; A2=C2)

или

=ЕСЛИ(И(A2=B2; A2=C2); «Совпадает»; «НЕ совпадает»)

Оператор И возвращает ИСТИНА, если все клетки таблицы содержат одно и то же значение, ЛОЖЬ, если любое значение отличается. Функция ЕСЛИ выводит введенные вами метки: «Совпадает» и «Не совпадает».

Как показано на скриншоте ниже, формула отлично работает с любыми типами данных — текстом, датами и числовыми значениями:

Сравниваем несколько ячеек с учетом регистра букв.

Чтобы сопоставить несколько текстовых значений друг с другом и убедиться, что они точно совпадают, используйте:

=И(СОВПАД(A2;B2); СОВПАД(A2; C2))

или

=ЕСЛИ(И(СОВПАД(A2;B2);СОВПАД(A2; C2));»Точное совпадение»; «НЕ совпадает»)

Как и в предыдущем примере, первая формула выдает значения ИСТИНА и ЛОЖЬ, тогда как вторая отображает ваши собственные сообщения для совпадений и различий:

Если же нам достаточно хотя бы двух совпадений значений, то сделаем это так:

=ЕСЛИ(ИЛИ(СОВПАД(A2;B2);СОВПАД(A2; C2);СОВПАД(B2;C2)); «Не менее 2 точных совпадений»; «НЕ совпадает»)

То есть, если в двух случаях из трёх будут одинаковые значения, то это будет для нас положительным результатом.

Но если в нашей таблице нужно проверить много ячеек, то приведенное выше выражение с условием ИЛИ станет очень громоздким. Поэтому можно использовать функцию СЧЁТЕСЛИ.

Мы может рассчитать количество совпадений в нашем диапазоне ячеек при помощи этой формулы массива:

{=СЧЁТЕСЛИ(A2:D2;A2:D2)}

Чтобы выделить группы ячеек с полностью совпадающими значениями, можно использовать выражение:

=ЕСЛИ(СЧЁТЕСЛИ(A2:D2;A2:D2)=СЧЁТЗ(A2:D2);»Полное совпадение»;»»)

Как видите, регистр символов здесь, к сожалению, не учитывается.

Кроме того, обратите внимание на последнюю строку. В одной из ячеек имеются ведущие пробелы, поэтому полного совпадения нет. Аналогичный результат будет, если после текста случайно будет введен так называемый концевой пробел. А его визуально обнаружить крайне сложно. Поэтому обнаружение и удаление лишних пробелов — весьма важная задача. Ссылку на нужную инструкцию смотрите в конце этой статьи.

Сравните диапазон ячеек с образцом.

В следующих примерах показано, как можно проверить, что все адреса в заданном диапазоне содержат тот же текст, что и в ячейке-образце.

Совпадает ли ячейка с образцом?

Если регистр символов не имеет значения, вы можете использовать для сопоставления с образцом примерно такое выражение:

ЧСТРОК( диапазон ) * ЧИСЛСТОЛБ( диапазон ) = СЧЁТЕСЛИ( диапазон ; ячейка образца )

В логической проверке функции ЕСЛИ вы проверяете равенство двух чисел:

Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов), и
Количество ячеек, содержащих то же значение, что и в образце (возвращенное функцией СЧЁТЕСЛИ ).

Предполагая, что образец текста находится в C2, а данные находятся в диапазоне A2: B6, формула выглядит следующим образом:

(ЧСТРОК(A2:B6)*ЧИСЛСТОЛБ(A2:B6))=СЧЁТЕСЛИ(A2:B6;C2)

Чтобы сделать результаты более удобными для пользователя, т. е. вывести осмысленный текст вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы делали в предыдущих примерах:

=ЕСЛИ((ЧСТРОК(A2:B6)*ЧИСЛСТОЛБ(A2:B6))=СЧЁТЕСЛИ(A2:B6;C2);»Всё совпадает»; «Есть несовпадения»)

Как показано на скриншоте выше, формула отлично справляется с диапазоном текстовых значений, но ее также можно использовать для сопоставления чисел и дат.

Проверяем совпадение с образцом с учётом регистра букв.

Если регистр символов имеет значение, вы можете проверить совпадение ячейки с образцом текста, используя следующие формулы массива.

ЕСЛИ((ЧСТРОК(диапазон )*ЧИСЛСТОЛБ(диапазон ))=(СУММ(—СОВПАД( образец ; диапазон ))); «текст_если_ совпадает»;»текст_если_НЕ_совпадает»)

Поскольку исходный диапазон находится в области A2: B6, а образец текста — в C2, получаем следующее:

{=ЕСЛИ((ЧСТРОК(B2:C6)*ЧИСЛСТОЛБ(B2:C6))=(СУММ(—СОВПАД(C2;B2:C6))); «Всё совпадает»;»НЕ совпадает»)}

В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter. При правильном вводе Excel заключает формулу массива в {фигурные скобки}, как показано ниже:

Как соотнести две ячейки по длине.

Иногда вам может потребоваться проверить, содержат ли данные в каждой ячейке одинаковое количество символов. Решение для этой задачи очень простое. Сначала вы получаете длину каждой из этих двух ячеек с помощью функции ДЛСТР, а затем проверяете равенство этих чисел.

Предположим, что исходные значения находятся в A2 и B2. Используйте любой из вариантов:

=ДЛСТР(A2)=ДЛСТР(B2)

или

=ЕСЛИ(ДЛСТР(A2)=ДЛСТР(B2); «Равная длина»; «Длины не равны»)

Как вы понимаете, первый возвращает логические значения ИСТИНА или ЛОЖЬ, тогда как второй выводит ваши собственные результаты:

Как показано на скриншоте выше, формулы работают как для текста, так и для чисел.

Примечание. Если две, казалось бы, одинаковые ячейки с текстом возвращают разную длину, скорее всего, проблема в начальных или конечных пробелах в одной или обеих из них. В этом случае удалите лишние пробелы с помощью функции СЖПРОБЕЛЫ. Подробное объяснение и примеры можно найти здесь: Как удалить пробелы в Excel .

Сравниваем две ячейки по вхождению определенного символа

Это последний пример в нашем руководстве по сравнению ячеек в Excel, и он показывает решение для довольно конкретной задачи. Предположим, у вас есть 2 столбца текстовых значений, которые содержат важный для вас символ. Ваша цель — проверить, содержат ли две ячейки в каждой строке одинаковое количество вхождений данного символа.

Чтобы было понятнее, рассмотрим следующий пример. Допустим, у вас есть два списка отгруженных заказов (столбец B) и полученных (столбец C). Каждая запись таблицы содержит заказы на конкретный товар, уникальный идентификатор которого включен во все идентификаторы заказа и указан в той же строке в столбце A (см. скриншот ниже). Вы хотите убедиться, что каждая строка содержит равное количество отправленных и полученных товаров с этим конкретным идентификатором.

Чтобы решить эту проблему, будем действовать следующим образом:

Во-первых, замените код заказа ничем с помощью функции ПОДСТАВИТЬ:

ПОДСТАВИТЬ(A1; код;»»)

Затем подсчитайте, сколько раз код появляется в каждой ячейке. Для этого получите длину её содержимого без кода и вычтите ее из общей первоначальной длины ячейки. Эта операция должна быть выполнена для 1 и 2 ячейки отдельно, например:

ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2; $A2;»»))

а также

ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(C2; $A2;»»))

Теперь вы приравниваете эти 2 числа, поместив знак равенства (=) между указанными выше частями.

ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2; $A2;»»)) = ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(C2; $A2;»»))

В нашем примере код находится в A2, а значения находится в B2 и C2.

Выражение возвращает ИСТИНА, если B2 и C2 содержат одинаковое количество вхождений символа из A2, в противном случае — ЛОЖЬ. Чтобы сделать результаты более понятными для ваших пользователей, вы можете встроить его в функцию ЕСЛИ:

=ЕСЛИ(ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2; $A2;»»)) = ДЛСТР(C2)-ДЛСТР(ПОДСТАВИТЬ(C2; $A2;»»)); «Совпадает»; «НЕ совпадает»)

Как вы можете видеть на скриншоте выше, всё работает отлично, несмотря на пару дополнительных сложностей:

Подсчитываемый символ (уникальный идентификатор) может появляться в любом месте ячейки.
Записи содержат переменное количество символов и разные разделители, такие как точка с запятой, запятая или пробел.

Вот как можно при помощи формул можно сравнивать ячейки в Excel. Благодарю вас за чтение.

Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…

9 способов сравнить две таблицы в Excel и найти разницу — В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить…

Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…

проверка правильности ввода Excel при помощи функции ЕСЛИ

Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ…

Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.…

3 примера, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…

Источник

Функция СОВПАД в Excel предназначена для проверки двух текстовых срок на их полное соответствие друг другу и возвращает одно из двух возможных логических значений: ИСТИНА – если строки полностью совпадают, ЛОЖЬ – если обнаружено хотя бы одно несоответствие в сравниваемых данных. При работе данной функции учитывается регистр букв. Это значит, что результат сравнения двух строк «текст» и «ТеКсТ» будет равен значению ЛОЖЬ. Распространенный вариант применения рассматриваемой функции – определение вхождения некоторого текста в документ.

Сравнение двух таблиц по функции СОВПАД в Excel

Пример 1. Есть две одинаковые (на первый взгляд) таблицы данных, которые содержат наименования продукции. Одну из них предположительно редактировал уволенный работник. Необходимо быстро сравнить имеющиеся данные и выявить несоответствия.

Вид таблицы данных:

Для сравнения двух строк используем следующую формулу массива (CTRL+SHIFT+Enter):

Описание параметров функции СОВПАД:

D3 – текущая ячейка с текстом из второй таблицы;
$B$3:$B$13 – соответствующая ячейка с текстом из второй таблицы для проверки на совпадение со значением D3.

Функция ИЛИ возвращает логическое значение ИСТИНА из массива если хотя бы одно из них совпадает с исходным значением.

Протянем данную формулу вниз до конца таблицы, чтобы Excel автоматически рассчитал значения для остальных строк:

Как видно, в сравниваемых строках были найдены несоответствия.

Выборка значений из таблицы по условию в Excel без ВПР

Пример 2. В таблице содержатся данные о продажах мобильных телефонов (наименование и стоимость). Определить самый продаваемый вид товара за день, рассчитать количество проданных единиц и общую выручку от их продажи.

Вид таблицы данных:

Поскольку товар имеет фиксированную стоимость, для определения самого продаваемого смартфона можно использовать встроенную функцию МОДА. Чтобы найти наименование наиболее продаваемого товара используем следующую запись:

Функция мода определяет наиболее часто повторяющиеся числовые данные в диапазоне цен. Функция ПОИСКПОЗ находит позицию первой ячейки из диапазона, в которой содержится цена самого популярного товара. Полученное значение выступает в качестве первого аргумента функции адрес, возвращающей ссылку на искомую ячейку (к значению прибавлено число 2, поскольку отсчет начинается с третьей строки сверху). Функция ДВССЫЛ возвращает значение, хранящееся в ячейке по ее адресу.

В результате расчетов получим:

Для определения общей прибыли от продаж iPhone 5s используем следующую запись:

Функция СУММПРИЗВ используется для расчета произведений каждого из элементов массивов, переданных в качестве первого и второго аргументов соответственно. Каждый раз, когда функция СОВПАД находит точное совпадение, значение ИСТИНА будет прямо преобразовано в число 1 (благодаря двойному отрицанию «—») с последующим умножением на значение из смежного столбца (стоимость).

Результат расчетов формулы:

Всего было куплено 4 модели iPhone 5s по цене 239 у.е., что в целом составило 956 у.е.

Правила синтаксиса и параметры функции СОВПАД в Excel

Функция СОВПАД имеет следующий вариант синтаксической записи:

текст1 – обязательный для заполнения, принимает ссылку на ячейку с текстом или текстовую строку для сравнения с данными, принимаемые вторым аргументом.
текст2 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.

Результат выполнения функции СОВПАД, принимающей на вход два имени, является код ошибки #ИМЯ? (например, СОВПАД(имя;имя)). Для корректной работы функции указываемые текстовые данные необходимо помещать в кавычки (например, («имя»;«имя»)).
Функция выполняет промежуточное преобразование числовых данных в текст. Например, результат выполнения =СОВПАД(111;111) будет логическое значение ИСТИНА. Однако, преобразование логических данных в числа текстового формата не выполняется. Например, результат выполнения =СОВПАД(ИСТИНА;1) будет логическое ЛОЖЬ.
Результат сравнения двух пустых ячеек или пустых текстовых строк с использованием функции СОВПАД — логическое ИСТИНА.

Добрый день!

Эта статья посвящена решению такого вопроса, как сравнить две таблицы в Excel, ну или, по крайней мере, два столбца. Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно. Быть может таблицу до десятка или двух, вы и сможете визуально отсортировать, но вот когда они будут превышать тысячи, тут уже вам будет необходимо дополнительные инструменты анализа.

Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы.

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2, как результат при равенстве ячеек мы получим ответ «ИСТИНА», а если совпадений нет, будет «ЛОЖЬ». Теперь простым авто копированием копируем на весь диапазон нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

И все правило применилось к нашему диапазону, где мы пытаемся проверить на похожесть две таблицы, и стало видны отличия, к которым применилось условное форматирование.

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.

Как сравнить две таблицы в Excel с помощью функции ВПР

В этом варианте мы будем использовать функцию ВПР, которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем ошибку #Н/Д.

Как сравнить две таблицы в Excel функции ЕСЛИ

Этот вариант предусматривает использования логической функции ЕСЛИ и отличие этого способа в том что для сравнения двух столбцов будет использован не весь массив целиком, а только та ее часть, которая нужна для сравнения.

Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(C2;$E$2:$E$7;0));»»;C2) и копируем ее на весь вычисляемый диапазон. Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.

Сравнить две таблицы с помощью макроса VBA

Есть много способов проверить две таблицы на схожесть, но некоторые варианты возможно только с помощью макросов VBA. Макросы для того что бы сравнить две таблицы, унифицирует этот процесс и существенно сокращает затраченное время на подготовку данных. Исходя из решаемой вами задачи и знаний макросов VBA, вы можете создавать любые варианты макросов. Ниже я привел методику, указанную на официальной страничке Microsoft. Вам нужно создать модуль для кода VBA и ввести код:

Пожалуй, каждый, кто работает с данными в Excel сталкивается с вопросом как сравнить два столбца в Excel на совпадения и различия. Существует несколько способов как это сделать. Давайте рассмотрим подробней каждый из них.

Как сравнить два столбца в Excel по строкам

Сравнивая два столбца с данными часто необходимо сравнивать данные в каждой отдельной строке на совпадения или различия. Сделать такой анализ мы можем с помощью функции ЕСЛИ . Рассмотрим как это работает на примерах ниже.

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

Для того, чтобы сравнить данные в каждой строке двух столбцов в Excel напишем простую формулу ЕСЛИ . Вставлять формулу следует в каждую строку в соседнем столбце, рядом с таблицей, в которой размещены основные данные. Создав формулу для первой строки таблицы, мы сможем ее протянуть/скопировать на остальные строки.

Для того чтобы проверить, содержат ли два столбца одной строки одинаковые данные нам потребуется формула:

Формула, определяющая различия между данными двух столбцов в одной строке будет выглядеть так:

Мы можем уместить проверку на совпадения и различия между двумя столбцами в одной строке в одной формуле:

Пример результата вычислений может выглядеть так:

Для того чтобы сравнить данные в двух столбцах одной строки с учетом регистра следует использовать формулу:

Как сравнить несколько столбцов на совпадения в одной строке Excel

В Excel есть возможность сравнить данные в нескольких столбцах одной строки по следующим критериям:

Найти строки с одинаковыми значениями во всех столбцах таблицы;
Найти строки с одинаковыми значениями в любых двух столбцах таблицы;

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

Представим, что наша таблица состоит из нескольких столбцов с данными. Наша задача найти строки в которых значения совпадают во всех столбцах. В этом нам помогут функции Excel ЕСЛИ и И . Формула для определения совпадений будет следующей:

Если в нашей таблице очень много столбцов, то более просто будет использовать функцию СЧЁТЕСЛИ в сочетании с ЕСЛИ :

В формуле в качестве “5” указано число столбцов таблицы, для которой мы создали формулу. Если в вашей таблице столбцов больше или меньше, то это значение должно быть равно количеству столбцов.

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

Представим, что наша задача выявить из таблицы с данными в несколько столбцов те строки, в которых данные совпадают или повторяются как минимум в двух столбцах. В этом нам помогут функции ЕСЛИ и ИЛИ . Напишем формулу для таблицы, состоящей из трех столбцов с данными:

В тех случаях, когда в нашей таблице слишком много столбцов – наша формула с функцией ИЛИ будет очень большой, так как в ее параметрах нам нужно указать критерии совпадения между каждым столбцом таблицы. Более простой способ, в этом случае, использовать функцию СЧЁТЕСЛИ .

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен “0” – это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат “Уникальная строка”, если нет, то “Не уникальная строка”.

Как сравнить два столбца в Excel на совпадения

Представим, что наша таблица состоит из двух столбцов с данными. Нам нужно определить повторяющиеся значения в первом и втором столбцах. Для решения задачи нам помогут функции ЕСЛИ и СЧЁТЕСЛИ .

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Эта формула проверяет значения в столбце B на совпадение с данными ячеек в столбце А.

Если ваша таблица состоит из фиксированного числа строк, вы можете указать в формуле четкий диапазон (например, $B2:$B10 ). Это позволит ускорить работу формулы.

Как сравнить два столбца в Excel на совпадения и выделить цветом

Когда мы ищем совпадения между двумя столбцами в Excel, нам может потребоваться визуализировать найденные совпадения или различия в данных, например, с помощью выделения цветом. Самый простой способ для выделения цветом совпадений и различий – использовать “Условное форматирование” в Excel. Рассмотрим как это сделать на примерах ниже.

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В тех случаях, когда нам требуется найти совпадения в нескольких столбцах, то для этого нам нужно:

Выделить столбцы с данными, в которых нужно вычислить совпадения;
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены цветом совпадения:

Поиск и выделение цветом совпадающих строк в Excel

Поиск совпадающих ячеек с данными в двух, нескольких столбцах и поиск совпадений целых строк с данными это разные понятия. Обратите внимание на две таблицы ниже:

В таблицах выше размещены одинаковые данные. Их отличие в том, что на примере слева мы искали совпадающие ячейки, а справа мы нашли целые повторяющие строчки с данными.

Рассмотрим как найти совпадающие строки в таблице:

Справа от таблицы с данными создадим вспомогательный столбец, в котором напротив каждой строки с данными проставим формулу, объединяющую все значения строки таблицы в одну ячейку:

Во вспомогательной колонке вы увидите объединенные данные таблицы:

Теперь, для определения совпадающих строк в таблице сделайте следующие шаги:

Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
На вкладке “Главная” на Панели инструментов нажимаем на пункт меню “Условное форматирование” -> “Правила выделения ячеек” -> “Повторяющиеся значения”;
Во всплывающем диалоговом окне выберите в левом выпадающем списке “Повторяющиеся”, в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку “ОК”:
После этого в выделенной колонке будут подсвечены дублирующиеся строки:

На примере выше, мы выделили строки в созданной вспомогательной колонке.

Но что, если нам нужно выделить цветом строки не во вспомогательном столбце, а сами строки в таблице с данными?

Для этого сделаем следующее:

Так же как и в примере выше создадим вспомогательный столбец, в каждой строке которого проставим следующую формулу:

Таким образом, мы получим в одной ячейке собранные данные всей строки таблицы:

Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
Затем, на вкладке “Главная” на Панели инструментов нажмем на пункт “Условное форматирование” -> “Создать правило”:

В диалоговом окне “Создание правила форматирования” кликните на пункт “Использовать формулу для определения форматируемых ячеек” и в поле “Форматировать значения, для которых следующая формула является истинной” вставьте формулу:

Не забудьте задать формат найденных дублированных строк.

Эта формула проверяет диапазон данных во вспомогательной колонке и при наличии повторяющихся строк выделяет их цветом в таблице:

Совпадение в столбцах Excel

Сегодня расскажу как искать совпадение в столбцах Excel. Разберем все тонкости на примерах.

Задача 1: Есть 6 текстов в 6 ячейках. Необходимо узнать, какие из них уникальные, а какие повторяются.

Использовать будем Условное форматирование.

Выбираем ячейки, которые необходимо сравнить;
Во вкладке Главная переходим «Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения»;

Выскакивает новое окно и в таблице начинают подсвечиваться ячейки, которые повторяются.

В этом окне вы можете выбрать две настройки: подсвечивать Повторяющиеся или Уникальные ячейки, а также какую подсветку при этом использовать — граница, текст, цвет текста, фон или своя уникальная.

Рассмотрим еще пример. Необходимо сравнить два столбца в Excel на совпадения. Есть таблица, в которой также есть совпадения, но уже числовые.

Выбираем таблицу и заходим в Повторяющиеся значения. Все совпадения будут подсвечены.

Можно, например, найти совпадения в одном столбце. Для этого достаточно перед применением опции выделить только его.

Ну и как я говорил выше, из выпадающего списка вы можете выделять не только повторяющиеся ячейки, но и уникальные.

Как сравнить два списка фамилий в excel на совпадения и выделить цветом

3 рабочих способа сравнить два столбца на совпадения в Excel

Здравствуйте постоянные читатели и случайные гости моего блога. Меня зовут Федор Лыков, и сегодня мы будем разбираться, как в эксель сравнить два столбца на совпадения. Лучшим инструментом для этого является Office от компании Microsoft.

Его часто используют бухгалтерские отделы, чтобы производить свои сложные расчёты. Чаще всего Эксель позволяет сделать грамотный анализ финансовой деятельность организации, который ляжет в основу составления прайс-листов.

Стоит отметить, что такие показатели изменяются. Поэтому необходимо подробно следить за динамикой всех изменений. Чтобы это делать просто и быстро достаточно обладать навыком сравнения значений двух массивов.

Это лишь вершина айсберга в обучении Microsoft Office Excel. Основные знания можно получить, приобретя курс “Excel + Google Таблицы с нуля до PRO”. За 4 месяца не выходя из дома, вы получите возможность делать сложные расчеты за короткое время.

Обратите внимание, что мы уже рассмотрели возможности выполнения операций с процентами. Переходите по ссылке и узнайте, как это сделать.

Как в Эксель сравнить два столбца на совпадения текста с помощью меню поиска

Воспользовавшись всего двумя инструментами программы, применить которые нужно последовательно, у вас получится сделать сортировку в двух и более столбах. Для этого делаем следующее:

Заносим в программу два массива, которые должны быть подвергнуты сравнению.

Следующим шагом необходимо найти кнопку, которая называется «Найти и выделить». Она расположена на вкладке «Главная» с правой стороны экрана.

Если вы дочитали до данного момента и решили проверить правильность предоставленной мной информации, но программы нет. Что же делать в таком случае, спросите вы меня? А тут все просто. Советую вам прочитать, как установить Эксель на свой компьютер.

Теперь нам необходимо выполнить выделение двух столбцов, подвергаемых сравнению.

Возможности программ от Microsoft безграничны. К тому же обычно они используются поверхностно. Обычный обыватель пользуется только некоторыми универсальными инструментами.

Предлагаю вам посетить курс MS Office и инструменты Google. Спешите записаться, ведь сейчас действует скидка, которую нельзя упустить.

Результатом вышеперечисленных действий будет открытие нового диалогового окна, в котором нужно поставить галочку напротив пункта «Отличия по строкам».
Подтверждаем свое решение кликом по кнопке «Ок».

В итоге вы увидите, как отличия окрасятся другим цветом.

Примите к сведению. Когда необходимо сделать так, чтобы отличия выделялись, воспользуйтесь функциями «Шрифт» и «Изменить цвет текста». Однако следите за собой, чтобы не нажать Enter. В противном случае выделение будет сброшено.

Далее переходим к сортировке. Чтобы это сделать нужно, нажать кнопку, расположенную рядом с «Найти».
После чего задаем параметры сортировки.

Перед нами откроется новое окно, в котором нужно сделать настройки сортировки.
Когда мы все это сделаем, данные будут располагаться в порядке, который нам необходим.

Когда все настройки сделаны, остается только нажать кнопку «Ок».
После этого мы сможем лицезреть на экране следующую картину.

Лето лучшее время для того, чтобы привнести в свою жизнь, что-то новое. В период с 4 июня по 3 августа будет проходить обучение по курсу “EXCEL”, который подойдет начинающим пользователям. Переходите по ссылке и забронируйте себе место.

Как в Эксель сравнить два столбца на совпадения с помощью команды форматирование

Рассмотренным ранее способом не всегда удобно пользоваться. Применяя его нельзя использовать рабочее пространство. Поэтому предлагаю рассмотреть альтернативный способ, который предполагает использование условного форматирования.

Давайте обозначим каждый шаг подробно:

Находим в верхнем меню блок «Стили». В нем будет находиться кнопка «Условное форматирование».
Тут нам необходимо найти строку «Создать правило».

В результате нашему вниманию предстанет диалоговое окно, в котором собраны все шаблоны. Для решения нам нужно будет кликнуть по последней строке в списке.
Эта команда дает возможность сравнить информацию, занесенную в строки двух столбцов.

Работы строятся на следующем принципе. Если значение соответствует «Истине», то эта ячейка будет отформатирована. Правила форматирования задаются с помощью нажатия на кнопку «Формат». Тут нам предлагается настроить шрифт и цвет ячейки.

Чтобы форматирование вступило в силу, нам понадобится дважды кликнуть по кнопке «Ок». В результате сравниваемые ячейки будут выделены в соответствии с указанными ограничениями.

Применение функции для сравнения

Такой подход используется более профессиональными пользователями. Более подробно о нем рассказывают наставники курса “Продвинутый Excel”. Выпускники смогут самостоятельно принимать решение, когда лучше использовать функцию для сравнения информации.

В Microsoft Office Excel заложена библиотека стандартных функций. Среди них есть та, которая помогает сравнить два массива. Причем после этого результат сводится в отдельный столбец. Использовать данный способ можно при сравнении цен на различные товары.

Для этого необходимо придерживаться следующей последовательности действий:

Вносим два блока, которые будут содержать название продуктов и цены. Причем один столбец будет включать в себя стандартные значения, а второй измененные.

Выбираем отдельную ячейку, в которую вводим ВПР. Тут нам нужно впечатать формулу и указать критерии сравнения.

Чтобы облегчить пользователю жизнь, разработчики сделали возможность автозаполнения последующих строк таблицы.

Обратите внимание, если появилось #Н/Д, то это будет означать, что в оригинальном массиве такая позиция отсутствует.

Заключение

Как мы успели заметить, есть разные способы получения конечного результата. Однако, чтобы не потеряться в функциях Excel нужно обладать небольшими стартовыми знаниями.

Их можно получить на курсе “Power BI & Excel PRO . Если вы зарегистрируетесь на курс до 10 мая, то получите скидку 30 %. Спешите, остался всего один день.

Это все, что мне хотелось бы вам сегодня рассказать.

Как в Excel сравнить два списка

Excel – эффективная программа для обработки данных. И один из методов анализа информации – сравнение двух списков. Если правильно осуществлять сравнение двух списков в Excel, организовать этот процесс будет очень легко. Достаточно просто следовать некоторым пунктам, о которых сегодня пойдет речь. Практическая реализация этого метода полностью зависит от потребностей человека или организации в конкретный момент. Поэтому следует рассмотреть несколько возможных случаев.

Сравнение двух списков в Excel

Конечно, можно сравнивать два списка вручную. Но это займет много времени. Excel обладает собственным интеллектуальным инструментарием, который позволит сравнивать данные не только быстро, но и получать ту информацию, которую глазами и не получить так легко. Предположим, у нас есть два столбца с координатами A и B. Некоторые значения в них повторяются.

Постановка задачи

Итак, нам нужно сравнить эти столбцы. Методика сравнения двух документов следующая:

Если уникальные ячейки каждого из этих списков совпадают, и общее количество уникальных ячеек совпадает, и ячейки те же самые, то можно считать эти списки одинаковыми. То, в каком порядке значения в этом перечне уложены, не имеет столь большого значения.
О частичном совпадении перечней можно говорить, если сами уникальные значения те же самые, но отличается количество повторов. Следовательно, в таких списках может быть и разное количество элементов.
О том, что два списка не совпадают, говорит разный набор уникальных значений.

Все эти три условия одновременно и являются условиями нашей задачи.

Решение задачи

Давайте сгенерируем два динамических диапазона, чтобы было более удобно сравнивать перечни. Каждый из них будет соответствовать каждому из перечней.

Чтобы сравнить два списка, надо выполнить следующие действия:

В отдельной колонке создаем список уникальных значений, характерных для обоих списков. Для этого используем формулу: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); “”). Сама формула должна записываться, как формула массива.
Определим, сколько раз каждое уникальное значение, встречается в массиве данных. Вот, какими формулами можно это сделать: =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5).
Если и число повторений, и количество уникальных значений одинаковое во всех перечнях, которые входят в эти диапазоны, то функция возвращает значение 0. Это говорит о том, что совпадение стопроцентное. В этом случае заголовки этих списков обретут зеленый фон.
Если все уникальное содержимое есть в обоих списках, то возвращенное формулами =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;”*?”;F5:F34;0) значение составит ноль. Если же E1 содержит не ноль, а такое значение содержится в ячейках E2 и F2, то в этом случае диапазоны будут признаны совпадающими, но только частично. В таком случае заголовки соответствующих списков станут оранжевыми.
И в случае возвращения одной из формул, описанных выше, ненулевого значения перечни будут полностью не совпадающими.

Вот и ответ на вопрос, как проанализировать столбцы на предмет совпадений с помощью формул. Как видим, с применением функций можно реализовать почти любую задачу, которая на первый взгляд с математикой не связана.

Тестирование на примере

В нашем варианте таблицы есть три вида списков каждой описанной выше разновидности. В нем есть частично и полностью совпадающие, а также не совпадающие.

Для сравнения данных мы используем диапазон A5:B19, в который мы попеременно вставляем эти пары списков. О том, какой будет итог сравнения, мы поймем по цвету исходных перечней. Если они абсолютно разные, то это будет красный фон. Если часть данных одинаковая, то желтый. В случае же полной идентичности соответствующие заголовки будут зелеными. Как же сделать цвет, зависящий от того, какой результат получился? Для этого нужно условное форматирование.

Поиск отличий в двух списках двумя способами

Давайте опишем еще два метода поиска отличий в зависимости от того, являются ли списки синхронными, или нет.

Вариант 1. Синхронные списки

Это простой вариант. Предположим, у нас такие списки.

Чтобы определить, какое количество раз значения не сошлись, можно с использованием формулы: =СУММПРОИЗВ(–(A2:A20<>B2:B20)). Если по итогу мы получили 0, это говорит о том, что два перечня одинаковые.

Вариант 2. Перемешанные списки

Если перечни не идентичны по порядку объектов, которые в них входят, нужно применить такую функцию, как условное форматирование и окрасить повторяющиеся значения. Или же воспользоваться функцией СЧЕТЕСЛИ, с использованием которой мы определяем, сколько раз элемент из одного перечня встречается во втором.

Как сравнить 2 столбца по строкам

Когда мы сравниваем две колонки, нам нередко приходится сопоставлять информацию, которая находится в разных рядах. Чтобы сделать это, нам поможет оператор ЕСЛИ. Давайте разберем принцип ее работы на практике. Для этого приведем несколько наглядных ситуаций.

Пример. Как сравнить 2 столбца на совпадения и различия в одной строке

Чтобы проанализировать, являются ли значения, находящиеся в том же самом ряду, но разных колонках, одинаковыми, запишем функцию ЕСЛИ. Формула вставляется в каждый ряд, размещенный во вспомогательном столбце, куда будут выводиться результаты обработки данных. Но вовсе не обязательно прописывать ее в каждый ряд, достаточно просто скопировать ее в оставшиеся ячейки этой колонки или же воспользоваться маркером автозаполнения.

Нам следует записать такую формулу, чтобы понять, совпадают ли значения в обеих колонках или нет: =ЕСЛИ(A2=B2; “Совпадают”; “”). Логика работы этой функции очень проста: она сопоставляет значения в ячейках A2 и B2, и если они одинаковые, выводит значение «Совпадают». Если же данные отличаются, то не возвращает никакого значения. Можно также проверить ячейки на предмет отсутствия между ними совпадения. В этом случае используемая формула следующая: =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Принцип тот же самый, сначала осуществляется проверка. Если оказывается, что ячейки удовлетворяют критерию, то выводится значение «Не совпадают».

Также возможно применение следующей формулы в поле формулы, чтобы выводить и «Совпадают» если значения одинаковые, и «Не совпадают», если они отличаются: =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”). Также вместо оператора равенства можно использовать оператор неравенства. Только порядок значений, которые будут выводиться в этом случае будет несколько другим: =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”). После использования первого варианта формулы результат получится следующим.

Этот вариант формулы не учитывает регистр значений. Поэтому если значения в одной колонке отличаются от других только тем, что они написаны большими буквами, то этой разницы программа не заметит. Чтобы при сравнении учитывался регистр, нужно в критерии использовать функцию СОВПАД. Остальные аргументы оставляем без изменений: =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”).

Как сравнить несколько столбцов на совпадения в одной строке

Есть возможность проанализировать значения в перечнях по целому набору критериев:

Найти те ряды, которые везде имеют те же значения.
Найти те ряды, где есть совпадения всего в двух списках.

Давайте рассмотрим несколько примеров, как действовать в каждом из этих случаев.

Пример. Как найти совпадения в одной строке в нескольких столбцах таблицы

Предположим, у нас есть ряд колонок, где содержится нужная нам информация. Перед нами стоит задача определить те ряды, в которых значения одинаковые. Чтобы это сделать, нужно воспользоваться следующей формулой: =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “).

Если столбцов лишком много содержится в таблице, то нужно просто применять вместе с функцией ЕСЛИ оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “). Цифра, которая используется в этой формуле, означает количество колонок, в которых нужно осуществлять проверку. Если оно отличается, то нужно написать столько, сколько справедливо для вашей ситуации.

Пример. Как найти совпадения в одной строке в любых 2 столбцах таблицы

Допустим, нам необходимо проверить, совпадают ли в одном ряду значения в двух колонках из тех, которые есть в таблице. Для этого нужно в качестве условия использовать функцию ИЛИ, где попеременно прописать равенство каждого из столбцов другому. Вот пример.

Мы используем такую формулу: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Может случиться ситуация, когда столбцов в таблице очень много. В таком случае формула будет огромной, а времени на подбор всех необходимых комбинаций может потребоваться очень много. Чтобы решить эту проблему, нужно воспользоваться функцией СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”)

Видим, что итого у нас две функции СЧЕТЕСЛИ. С помощью первой мы попеременно определяем, сколько столбцов имеют сходство с A2, а с помощью второй проверяем количество сходств со значением B2. Если в результате вычисления по этой формуле мы получаем нулевое значение, это говорит о том, что все строки в этом столбце уникальны, если же больше – есть сходства. Следовательно, если в результате вычисления по двум формулам и складывания итоговых результатов мы получаем нулевое значение, то возвращается текстовое значение «Уникальная строка», если же это число больше, записывается, что эта строка не уникальная.

Как сравнить 2 столбца в Excel на совпадения

Теперь приведем такой пример. Допустим у нас есть таблица с двумя столбцами. Необходимо проверить совпадения в них. Чтобы это сделать, необходимо применять формулу, где будут использоваться и функция ЕСЛИ, и оператор СЧЕТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”)

Больше никаких действий выполнять не нужно. После вычисления результат по этой формуле мы получаем, если значение третьего аргумента функции ЕСЛИ совпадает. Если же их нет, то содержимое второго аргумента.

Как сравнить 2 столбца в Excel на совпадения и выделить цветом

Чтобы было более просто визуально определять совпадающие столбцы, можно выделить их цветом. Для этого нужно воспользоваться функцией «Условное форматирование». Давайте разберемся на практике.

Поиск и выделение совпадений цветом в нескольких столбцах

Чтобы определить совпадения и выделить их, необходимо сначала выделить диапазон данных, в котором будет осуществляться проверка, после чего открыть на вкладке «Главная» пункт «Условное форматирование». Там выбираем в качестве правила выделения ячеек «Повторяющиеся значения».

После этого появится новое диалоговое окно, в котором в левом всплывающем перечне находим опцию «Повторяющиеся», а в правом списке выбираем цвет, каким будет осуществляться выделение. После нажатия нами кнопки «ОК», фон всех ячеек со сходствами будет выделен. Дальше просто сравнивать колонки на глаз.

Поиск и выделение цветом совпадающих строк

Методика проверки, совпадают ли строки, несколько отличается. Сначала необходимо создать дополнительную колонку, и там будем использовать объединенные значения с использованием оператора &. Для этого нужно записать формулу вида: =A2&B2&C2&D2.

Выделяем ту колонку, которая была создана и содержит объединенные значения. Далее выполняем ту же последовательность действий, которая описана выше для колонок. Повторяющиеся строки будут выделены тем цветом, который вы укажете.

Видим, что ничего сложного в том, чтобы искать повторения, нет. Excel содержит все необходимые инструменты для этого. Важно просто потренироваться перед тем, как использовать все эти знания на практике.

Источник

Методы сравнения таблиц в Microsoft Excel

Способы сравнения

Способ 1: простая формула

Способ 2: выделение групп ячеек

Способ 3: условное форматирование

Способ 4: комплексная формула

Способ 5: сравнение массивов в разных книгах

Сравнение двух массивов в excel

Сравнение массивов (сравнение макросом)

Сравнение двух одномерных массивов с разным числом элементов

Сравнение двумерных массивов (Макросы/Sub)

Как сравнить два столбца таблицы Excel на совпадения значений

Функция СОВПАД позволяет сравнить два столбца таблицы

Сравнение двух массивов чисел в эксель

Поиск отличий в двух списках

Вариант 1. Синхронные списки

Вариант 2. Перемешанные списки

Сравнение массивов (Формулы/Formulas)

Сравнение двух числовых массивов.

Как сравнить две ячейки в Excel.

Формула без учета регистра.

Формула с учетом регистра.

Как сравнить несколько ячеек в Excel

Формула без учета регистра для более чем двух ячеек

Сравниваем несколько ячеек с учетом регистра букв.

Сравните диапазон ячеек с образцом.

Совпадает ли ячейка с образцом?

Проверяем совпадение с образцом с учётом регистра букв.

Как соотнести две ячейки по длине.

Сравниваем две ячейки по вхождению определенного символа

Сравнение двух таблиц по функции СОВПАД в Excel

Выборка значений из таблицы по условию в Excel без ВПР

Правила синтаксиса и параметры функции СОВПАД в Excel

Простой способ, как сравнить две таблицы в Excel

Быстрое выделение значений, которые отличаются

Сравнить две таблицы в Excel с помощью условного форматирования

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Как сравнить две таблицы в Excel с помощью функции ВПР

Как сравнить две таблицы в Excel функции ЕСЛИ

Сравнить две таблицы с помощью макроса VBA

Как сравнить два столбца в Excel по строкам

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

Как сравнить несколько столбцов на совпадения в одной строке Excel

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

Пример 2. Как найти совпадения в одной строке в любых двух столбцах таблицы

Как сравнить два столбца в Excel на совпадения

Как сравнить два столбца в Excel на совпадения и выделить цветом

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

Поиск и выделение цветом совпадающих строк в Excel

Совпадение в столбцах Excel

Как сравнить два списка фамилий в excel на совпадения и выделить цветом

3 рабочих способа сравнить два столбца на совпадения в Excel

Как в Эксель сравнить два столбца на совпадения текста с помощью меню поиска

Как в Эксель сравнить два столбца на совпадения с помощью команды форматирование

Применение функции для сравнения

Заключение

Как в Excel сравнить два списка

Сравнение двух списков в Excel

Постановка задачи

Решение задачи

Тестирование на примере

Поиск отличий в двух списках двумя способами

Вариант 1. Синхронные списки

Вариант 2. Перемешанные списки

Как сравнить 2 столбца по строкам

Пример. Как сравнить 2 столбца на совпадения и различия в одной строке

Как сравнить несколько столбцов на совпадения в одной строке

Пример. Как найти совпадения в одной строке в нескольких столбцах таблицы

Пример. Как найти совпадения в одной строке в любых 2 столбцах таблицы

Как сравнить 2 столбца в Excel на совпадения

Как сравнить 2 столбца в Excel на совпадения и выделить цветом

Поиск и выделение совпадений цветом в нескольких столбцах

Поиск и выделение цветом совпадающих строк

А вот еще интересные статьи: