Функция если в 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.

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

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

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

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

Функция СОВПАД в 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 – обязательный для заполнения, принимает ссылку на ячейку или текст, с которым сравниваются данные, переданные в виде первого аргумента.

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

Добрый день!

Эта статья посвящена решению такого вопроса, как сравнить две таблицы в 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. Если уникальные ячейки каждого из этих списков совпадают, и общее количество уникальных ячеек совпадает, и ячейки те же самые, то можно считать эти списки одинаковыми. То, в каком порядке значения в этом перечне уложены, не имеет столь большого значения.
2. О частичном совпадении перечней можно говорить, если сами уникальные значения те же самые, но отличается количество повторов. Следовательно, в таких списках может быть и разное количество элементов.
3. О том, что два списка не совпадают, говорит разный набор уникальных значений.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, у нас есть ряд колонок, где содержится нужная нам информация. Перед нами стоит задача определить те ряды, в которых значения одинаковые. Чтобы это сделать, нужно воспользоваться следующей формулой: =ЕСЛИ(И(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 содержит все необходимые инструменты для этого. Важно просто потренироваться перед тем, как использовать все эти знания на практике.