Как сравнить данные двух таблиц в Excel

Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.

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

Формула сравнения нескольких чисел

Если A меньше 20, то ячейка принимает значение "Категория 1", Иначе проверяем, находится ли A в диапазоне между 20 и 50, если да, то ячейка принимает значение "Категория 2", иначе проверяем, лежит ли число A в диапазоне между 50 и 65, если да, то конечная ячейка принимает значение "Категория 3", иначе (в случае если число больше 65ти) то конечная ячейка принимает значение формулы A*1,5+90 (исходное число A умножить на полтора и прибавить 90)

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

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

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция «Мастер подстановок» больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «Иванов» или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.

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

В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).

Четвертый аргумент пуст, поэтому функция возвращает приближенное соответствие. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

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

Использование функций индекс и MATCH вместо функции ВПР

Существуют определенные ограничения с использованием функции ВПР — функция ВПР может искать значение слева направо. Это означает, что столбец с искомым значением всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Если электронная таблица не создана таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций индекс и ПОИСКПОЗ.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

Пример функции ВПР на работе

Скопируйте указанные ниже данные в пустой лист.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладка Главная , группа Выравнивание ).

ТОП-24 популярных формул в Excel

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

Объединение текста

Чтобы соединить текст, используются разные формулы. Например, =СЦЕПИТЬ(А3;В3) позволит создать единый текст, написанный без пробела. Такой же функцией обладает и знак &, в таком случае пишут =А3&В3.

Чтобы разделить слова, нужно в конце каждой строчки ставить пробел самостоятельно или писать более сложную формулировку: =СЦЕПИТЬ(А3;» «;В3). Но это неудобно при работе с большим объемом данных.

СЦЕПИТЬ и & Excel

Еще одна команда =ОБЪЕДИНИТЬ(» «;ИСТИНА;D3:F3) включает:

  • символ разделения, указанный первым в кавычках;
  • истина/ложь обозначают, что пустые не обязательно/необходимо учитывать соответственно;
  • список объединяемых зон, среди которых вводят и обычные слова в кавычках.

Это часто требуется для объединения ФИО, если написано в разных колонках или строчках. Появляется строчка с полным содержимым.

ОБЪЕДИНИТЬ Excel

ИЛИ

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

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

ИЛИ Эксель

Нахождение по горизонтали и вертикали

Для этого вводят =ГПР, при этом в диапазон можно выделить как одну, так и несколько строк. Расшифровывается ГПР, как горизонтальный просмотр.

Вводится оператор в виде =ГПР, дальше в скобках:

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

Большее распространение получило при подсчитывании бонусов там, где предусмотрена оплата труда с процентом от выручки.

Здесь показано, что действие осуществляется по первой строчке в пределах D1:G2, численного обозначения, максимально приближенного к Е5. Появится параметр со второй строки этого же столбца.

ГПР Эксель

Для поиска по вертикали прописывают ВПР, в итоге поиск производится по столбцу, а итог берется из строки. Таким образом, Е4 сравнивается с ячейками I, и данные выводятся из соответствующей ей части столбца J.

ВПР Эксель

ЕСЛИ

Проверяет конкретное условие и выводит заданный результат. Например, чтобы сравнить собственные расходы и доходы за месяц, вводят =ЕСЛИ(В2>А2;»Долги»;»Отпуск»). Ее можно совмещать с =ИЛИ. В этом случае количество вложений ограничивается 64. О чем речь: =ЕСЛИ(А4=0; “ДА”;ЕСЛИ(А4=1; “Нет”; “Возможно”)), и так может продолжаться 64 раза, но в этом случае найти ошибку будет проблематично. Ответом будет номер или текст. Чтобы далее использовать эту функцию, можно будет менять только первоначальное содержимое.

ЕСЛИ эксель

РАНГ

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

Для этого прописывают =РАНГ(В2;$B$2:$B$6;0) – это позволит отсортировать по уменьшению. Чтобы отсортировать их по увеличению, впишите последней единицу вместо нуля.

РАНГ эксель

МАКС

Это самая простая и полезная фишка – позволяет найти максимум из 255 чисел максимум. Поэтому можно сравнивать расходы не только за последний месяц, но и год. Для этого прописывают =МАКС(дают диапазон).

МАКС Эксель

МИН

Это противоположное показание – минимум, оценивается идентично, но оператор представлен =МИН(диапазон).

МИН EXCEL

СРЗНАЧ

Чтобы получить среднее арифметическое, указывают =СРЗНАЧ. Ограничиться можно именами или вводить сами числа.

СРЗНАЧ

СУММ

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

Другой интересной ее интерпретацией является =СУММЕСЛИ, в таком случае можно суммировать их, если они соответствуют каким-либо условиям.

Например, если все прошедшие во 2 тур олимпиады сдали по 20 рублей на расходники, они могут приступить к выполнению заданий. Организатор контролирует сумму через условие =СУММЕСЛИ(G4:G6;»да»;H4:H6).

СУММЕСЛИ

Еще одна вариация =СУММЕСЛИМН подходит, если условий несколько, имеет более сложную структуру.

СЧЕТЕСЛИ

Определяет количество заполненных квадратов, которые будут учтены в расчете после прохождения проверки по =если. Здесь задается диапазон и критерий, который проверяют на актуальность.

Если вы хотите увидеть, какие статьи семейного бюджета тянут 10000 рублей в месяц и более, задайте =СЧЁТЕСЛИ(B2:B6;»>=10000″).

СЧЕТЕСЛИ

ЕСЛИОШИБКА

Позволяет уведомить пользователя, если в формуле ошибка. Если значение корректное, оно выводится в качестве результата. Вводится в виде =ЕСЛИОШИБКА(В2/С2;»Ошибка при вычислении»). Используется, если среди числовых есть текстовые наполнения.

ЕСЛИОШИБКА

ЛЕВСИМВ

Выделяет необходимое число символов слева, подходит для текстовых форматов. Выглядит, как =ЛЕВСИМВ(А5;60). Полезно для копирайтеров, которые проверяют, поместится ли заголовок в выдачу поисковой системы.

ЛЕВСИМВ

ПСТР

Здесь можно сохранить определенное количество знаков, начиная с любой точки отсчета. Это обусловлено особенностями формулы =ПСТР(А3;9;100). Это указывает, что в качестве основы берется содержимое А3, копирование начинается с 9 символа, выдержка содержит 100 знаков.

ПСТР

Регистр

Чтобы изменить регистр, используется оператор =ПРОПИСН и =СТРОЧН, далее вводится текст или дается ссылка на ячейку. =ПРОПНАЧ – замена строчных прописными и наоборот.

ПОИСКПОЗ

Расшифровывается, как поиск по значению. При этом можно искать точное совпадение или приблизительное соответствие. Для этого вводят =ПОИСКПОЗ(900;В2:В6;1), где 900 – искомое значение, далее указан изучаемый массив, последнее – тип сопоставления (1 – меньше или равно, 0 – точное, -1 – равно или больше).

ПОИСКПОЗ

ДЛСТР

Это длина строки в символах, которую можно узнать из =ДЛСТР(А1). Подходит для копирайтеров, контентщиков, менеджеров.

ДЛСТР

ПЕЧСИМВ

Ввод ее позволит убрать все невидимые знаки из статьи.

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

Если вам нравится узнавать что-то новое, рекомендуем вам ознакомиться с 26 полезными функциями Яндекс.Вебмастера.

Как посмотреть старую версию страницы?

Как дать ссылку на определенную часть текста в Chrome?

Как делегировать Google Мой Бизнес на другой аккаунт?

Специалисты компании «Третий Путь» готовы ответить на все ваши вопросы по продвижению сайта, увеличению звонков и заявок на сайте.
Закажи звонок эксперта — мы свяжемся и ответим на все вопросы.

© Третий Путь – продвижение сайтов в поисковых системах, 2021

Заполняя любую форму на сайте, вы соглашаетесь
с политикой конфиденциальности

1.1. В настоящей Политике конфиденциальности используются следующие термины:

1.1.1. «Администрация сайта» – уполномоченные сотрудники на управления сайтом, действующие от имени ООО «Третий Путь», которые организуют и (или) осуществляет обработку персональных данных, а также определяет цели обработки персональных данных, состав персональных данных, подлежащих обработке, действия (операции), совершаемые с персональными данными.

1.1.2. «Персональные данные» — любая информация, относящаяся прямо или косвенно к определяемому физическому лицу (субъекту персональных данных).

1.1.3. «Обработка персональных данных» — любое действие (операция) или совокупность действий (операций), совершаемых с использованием средств автоматизации или без использования таких средств с персональными данными, включая сбор, запись, систематизацию, накопление, хранение, уточнение (обновление, изменение), извлечение, использование, передачу (распространение, предоставление, доступ), обезличивание, блокирование, удаление, уничтожение персональных данных.

1.1.4. «Конфиденциальность персональных данных» — обязательное для соблюдения Организацией или иным получившим доступ к персональным данным лицом требование не допускать их распространения без согласия субъекта персональных данных или наличия иного законного основания.

1.1.5. «Пользователь сайта (далее Пользователь)» – лицо, имеющее доступ к Сайту, посредством сети Интернет и использующее Сайт Организации.

1.1.6. «IP-адрес» — уникальный сетевой адрес узла в компьютерной сети, построенной по протоколу IP.

1.1.7. «Cookies» — небольшой фрагмент данных, отправленный веб-сервером и хранимый на компьютере пользователя, который веб-клиент или веб-браузер каждый раз пересылает веб-серверу в HTTP-запросе при попытке открыть страницу соответствующего сайта.

2.1. Порядок ввода в действие и изменения Политики конфиденциальности:

2.1.1. Настоящая Политика конфиденциальности (далее – Политика конфиденциальности) вступает в силу с момента его утверждения приказом Руководителей Организации и действует бессрочно, до замены его новой Политикой конфиденциальности.

2.1.2. Изменения в Политику конфиденциальности вносятся на основании Приказов Руководителей Организации.

2.1.3. Политика конфиденциальности персональных данных действует в отношении информации, которую ООО «Третий Путь» (далее – Организация) являясь владельцем сайтов, находящихся по адресам: 3put.ru, а также их поддоменах (далее – Сайт и/или Сайты), может получить от Пользователя Сайта при заполнении Пользователем любой формы на Сайте Организации. Администрация сайта не контролирует и не несет ответственность за сайты третьих лиц, на которые Пользователь может перейти по ссылкам, доступным на Сайтах.

2.1.4. Администрация сайта не проверяет достоверность персональных данных, предоставляемых Пользователем.

2.2. Порядок получения согласия на обработку персональных данных и их обработки:

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

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

2.2.3. Согласие Пользователя на использование его персональных данных может храниться в Организации в бумажном и/или электронном виде.

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

2.2.5. Обработка персональных данных Пользователя без их согласия осуществляется в следующих случаях:

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

2.2.6. Кроме персональных данных при посещении Сайта собираются данные, не являющиеся персональными, так как их сбор происходит автоматически веб-сервером, на котором расположен сайт, средствами CMS (системы управления сайтом), скриптами сторонних организаций, установленными на сайте. К данным, собираемым автоматически, относятся: IP адрес и страна его регистрации, имя домена, с которого Пользователь осуществил перехода на сайты организации, переходы посетителей с одной страницы сайта на другую, информация, которую браузер Посетителя предоставляет добровольно при посещении сайта, cookies (куки), фиксируются посещения, иные данные, собираемые счетчиками аналитики сторонних организаций, установленными на сайте. Эти данные носят неперсонифицированный характер и направлены на улучшение обслуживания Пользователя, улучшения удобства использования сайта, анализа посещаемости. Эти данные собираются автоматически, отправку этих данных Пользователь может запретить, отключив cookies (куки) в браузере, в котором открывается сайт.

2.2.7. Порядок обработки персональных данных:

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

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

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

3.2.1. фамилию, имя, отчество Пользователя.

3.2.2. контактный телефон Пользователя.

3.2.3. адрес электронной почты (e-mail).

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

4.1. Персональные данные Пользователя Администрация сайта может использовать в целях:

4.1.1. Установления с Пользователем обратной связи, включая направление уведомлений, запросов, касающихся использования Сайта, оказания услуг, обработка запросов и заявок от Пользователя.

4.1.2. Осуществления рекламной деятельности с согласия Пользователя.

4.1.3. Регистрации Пользователя на Сайтах Организации для получения индивидуальных сервисов и услуг.

4.1.4. Совершения иных сделок, не запрещенных законодательством, а также комплекс действий с персональными данными, необходимых для исполнения данных сделок.

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

5.2. При утрате или разглашении персональных данных Администрация сайта информирует Пользователя об утрате или разглашении персональных данных.

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

6.1. Пользователь обязан:

6.1.1. Предоставить информацию о персональных данных, необходимую для пользования Сайтом.

6.1.2. Обновить, дополнить предоставленную информацию о персональных данных в случае изменения данной информации.

6.2. Администрация сайта обязана:

6.2.1. Использовать полученную информацию исключительно для целей, указанных в п. 4 настоящей Политики конфиденциальности.

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

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

6.2.4. Осуществить блокирование и/или удаления персональных данных, относящихся к соответствующему Пользователю, с момента обращения или запроса Пользователя или его законного представителя либо уполномоченного органа по защите прав субъектов персональных.

7.1. Администрация сайта, не исполнившая свои обязательства, несёт ответственность в соответствии с действующим законодательством Российской Федерации, за исключением случаев, предусмотренных п.2.5. и 7.2. настоящей Политики

7.2. В случае утраты или разглашения Конфиденциальной информации Администрация сайта не несёт ответственность, если данная конфиденциальная информация:

7.2.1. Стала публичным достоянием до её утраты или разглашения.

7.2.2. Была получена от третьей стороны до момента её получения Администрацией сайта.

7.2.3. Была разглашена с согласия Пользователя.

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

8.2. Получатель претензии в течение 30 календарных дней со дня получения претензии, письменно уведомляет заявителя претензии о результатах рассмотрения претензии.

8.3. При не достижении соглашения спор будет передан на рассмотрение в судебный орган в соответствии с действующим законодательством Российской Федерации.

8.4. К настоящей Политике конфиденциальности и отношениям между Пользователем и Администрацией сайта применяется действующее законодательство Российской Федерации.

9.1. Администрация сайта вправе вносить изменения в настоящую Политику конфиденциальности без согласия Пользователя.

9.2. Новая Политика конфиденциальности вступает в силу с момента ее размещения на Сайте, если иное не предусмотрено новой редакцией Политики конфиденциальности.

Читайте также:  Заполнение ячеек в Excel знаками после запятой

Excel как универсальный инструмент сравнения списков.

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

  • Список покупок, занесенных в табличный процессор (Excel, LibreOffice Calc, Google Docs и т.п.);
  • Список файлов, полученный копированием из Total Commander;

Вариантов огромное количество, цель одна – получить список отличий одного списка от другого.

Для решения такой задачи отлично подходят две программы: Excel или LibreOffice Calc.

В качестве «сравнителя» используется функция ВПР. Причем синтаксис, используемой функции настолько идентичен, что нет никакого различия какую программу использовать.

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

Для получения списка файлов из папки я буду использовать поиск в Total Commander. Да, есть такая классная фича в Total Commander.

Открываю первую папку с фото и открываю стандартное окно поиска по ALT+F7, задаю маску поиска *.jpg для поиска только файлов фото.

В результате поиска в папке «Фото» было найдено 49 файлов. Чтобы скопировать список найденных файлов в буфер обмена нажимаем стандартную комбинацию клавиш CTRL+C.

Запускаем Excel. По умолчанию открывается пустая книга. На первом листе становимся в ячейку A1 и нажимаем стандартную комбинацию CTRL+V, чтобы вставить скопированный список.

Теперь нужно сделать поиск файлов во второй папке «Фото1». В результате поиска найдено 199 файлов. Копируем список через CTRL+C.

Скопированный список из второй папки вставляем в ячейку A1 на другой лист, в моем случае это «Лист2»

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

Итак, на листе «Лист1» я выделяю в любой ячейке кусочек строки «d:\Фото\», копирую его в буфер и нажатием ESC выхожу из режима редактирования.

Для групповой замены нажимаю CTRL+H. В стандартном окне замены в поле «Найти:» вставляю скопированный кусок строки. Поле «Заменить на:» оставляю пустым.

Нажимаю кнопку «Заменить все».

Контролирую процесс замены, сравнивая количество изначальных строк с количеством замен. В моем случае все отлично 49 строк и 49 замен.

Тоже самое проделываю и на втором листе.

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

Для сравнения используем функцию «ВПР».

В ячейке B1 листа «Лист1» нажимаю «=» и начинаю писать имя функции ВПР, открываю скобку «(«. Теперь Excel подсказывает мне синтаксис аргументов функции. Первым параметром мне нужно указать строку, которую я буду искать в другой области(«искомое_значение»). Я указываю ячейку A1.

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

Опять ставлю точку с запятой и указываю параметр «номер_столбца». Все столбцы в области поиска условно нумеруются от 1. Так как у меня в области поиска всего один столбец в параметре я ставлю 1.

Последний параметр, который я указываю, это «интервальный_просмотр». Суть его сводится к тому, как будет происходить поиск: точно или приблизительно. Для нашего сравнения обязательно нужно выбрать точное совпадение. Выбираю «ЛОЖЬ» — Точное совпадение.

Итоговая формула выглядит вот так

В нашем случае файл с таким именем есть во втором списке и функция ВПР нашла его и вернула значение из первого столбца области поиска, т.е. само имя файла.

Теперь осталось скопировать формулу для всех остальных строк на первом листе.

Следующим шагом будет установка фильтра по столбцу B на листе «Лист1». Выделяем весь столбец «B»

Затем нажимаем «Сортировка и фильтр» и выбираем пункт «Фильтр»

В результате получается вот такой вид на листе «Лист1»

Чтобы увидеть список файлов, которых нет во втором списке, надо раскрыть список фильтра и выделить только значение с типом «Н/Д»

В нашем случае таких файлов нет. Т.е. во втором списке есть все файлы из первого списка. Осталось установить функцию ВПР для всех строк на втором листе.
Обратите внимание на то, что в качестве области поиска указывается столбец «A» на «Лист1». Формулу копируем для всех строк листа «Лист2»

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

Устанавливаем галочку на «Н/Д» и нажимаем «ОК». Все остальные галочки в фильтре снимаем.

В результате фильтрации мы видим список уникальных файлов второго списка, которых нет в первом.

Собственно, цель сравнения достигнута. Что делать с этим списком уже другой вопрос.

При сравнении в LibreOffice Calc формула ВПР выглядит абсолютно аналогично Excel

Автофильтр устанавливаем через меню «Данные»«Автофильтр»

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив . Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел , получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив .

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

Формирование списка с помощью формул Excel

Хочу сделать список должников документов в excel. Как сделать так чтобы строка в которой есть слово «нет» (т.е у этого контрагента нет документов), автоматически копировалась на отдельный лист.

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

формирование списка с помощью формул

Итак, посмотрите на рисунок выше, к чему мы должны прийти. Список должников должен автоматически пополняться фамилиями, если в столбце «B» будет появляться слово «Нет». Нам потребуется добавить вспомогательный столбец. Для удобства мы добавим его самым первым, чтобы можно было использовать функцию ВПР (чтобы искомое значение находилось левее возвращаемого значения), можно было бы использовать функцию ИНДЕКС, в этом случаем вспомогательный столбец можно было разместить где угодно.

В данном столбце мы пропишем такую формулу

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

Протягиваем формулу на все строки.

формируем список с помощь формул - строка

В итоге мы получили номер строки только в тех ячейках, где напротив в столбце «B» есть слово «Нет».

Далее переходим в столбец «Е», это тоже будет вспомогательный столбец, здесь мы воспользуемся функцией НАИМЕНЬШИЙ, функция выглядит следующим образом

где k — это порядок старшинства. Например, если указать 2, то будет выбрано второе наименьшее число из столбца «A». Нам же нужно, чтобы сначала было самое маленькое число и потом оно должно увеличиваться, то есть k должно начинаться с единицы и с каждой ячейке должно увеличиваться. Для этого мы можем сделать еще один дополнительный столбец и прописать пункты по порядку 1, 2, 3 и так далее и сделать ссылку на эти ячейки, но мы попробуем обойтись без вспомогательного столбца. Вместо k мы можем так же воспользоваться функцией СТРОКА(), которая при протягивании вниз как раз будет увеличиваться на единицу, но так как формулу мы начинаем со второй строки, а нам нужна первая k равная 1, то мы можем прописать СТРОКА()-1, в итоге формула примет вид в ячейке «E2», эту формулу протянем вниз с запасом.

Единственное, что когда все числа из столбца «A» закончатся, то формула будет выдавать ошибку «#ЧИСЛО», поэтому с помощью функции ЕСЛИОШИБКА, сделаем проверку и если возвращается ошибка, то будем указывать пустую ячейку.

Вот теперь, всё как необходимо. Осталось последнее действие. С помощью функции ВПР, по искомому значению из столбца «E», возвращаем соответствующие фамилии.

Сделаем так же проверку, если в столбце «E» будет пустая ячейка, то будет возвращать пусто, если ячейка не пустая, то будем выполнять функцию. Пропишем итоговую формулу в ячейке «F2» и протянем формулу вниз так же с запасом.

формируем список без формул

Все, теперь ФИО будет автоматически появляться, когда напротив фамилии будет появляться слово «Нет».

Сравнение 2 столбцов в Excel

​ на экран.​ вариант. Он установлен​ вправо и заполняем​ значения, которые имеются​«СЧЁТЕСЛИ»​«Правила выделения ячеек»​. Из выпадающего списка​ которые расположены на​ строчкам, как видим,​
​ выдает показатель ИСТИНА,​

​объединить таблицы Excel​​Здесь расхождение дат в​http://hammga.ru/blogs?id=2​ ячеек C3, O3​
​ форматированием?​Главная — Удалить -​ аргумента (например, Таблица_2).​Автор: Максим Тютюшев​ по умолчанию, так​
​ колонку номерами по​ во второй таблице,​
​. После его выделения​
​. В следующем меню​

​ переходим по пункту​​ других листах, отличных​ формула сравнения выдала​ а если нет,​с отчетами. Тогда​ ячейках строк второй​

​На VBA безграничные​​ и AC3. Проблема​Надеюсь на помощь.​ Удалить строки с​ Если количество раз​У нас имеется две​ что в данном​ порядку, начиная от​ но отсутствуют в​ щелкаем по кнопке​ делаем выбор позиции​«Управление правилами»​

​ от того, где​​ показатель​ то – ЛОЖЬ.​

​ все одинаковые данные​​ и третьей.​ возможности.​
​ в том, что​
​ikki​ листа (Home -​

Сравнение с помощью надстройки Inquire

Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire , которая позволит проанализировать и сравнить два файла Excel. Этот способ хорош, когда у вас есть необходимость сравнить два файла, в случае, когда ваш коллега работал над книгой и ввел некоторые изменения. Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire.

Ну вот мы и рассмотрели 8 способов как сравнить две таблицы в Excel, эти варианты помогут вам решить свои аналитические задачи и упростят вашу работу.

Был рад вам помочь!

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

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

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

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

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

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

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

=ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “)

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

=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “)

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

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

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

=ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “)

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

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

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

Мануал по решению типизированных задач в Microsoft Excel

Время от времени некоторым (а может и более, чем некоторым) из нас приходится сталкиваться с задачами по обработке небольших массивов данных, начиная от составления и анализа домашнего бюджета и заканчивая какими-либо расчетами по работе, учебе и т.д. Пожалуй, наиболее подходящим инструментом для этого является Microsoft Excel (или возможно иные его аналоги, но они менее распространены).

Поиск выдал мне всего одну статью на Хабре по схожей тематике — «Талмуд по формулам в Google SpreadSheet». В ней дано хорошее описание базовых вещей для работы в excel (хотя он и не 100% про сам excel).

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

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

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

Кейс_1: Логические функции и функции поиска совпадений
«У меня есть набор значений в табличке и необходимо что бы при выполнении определенного условия/набора условий выводилось определенное значение» (с) Пользователь

Данные, как правило, представлены в табличной форме:

image

  • если значение в столбце «Количество» больше 5,
  • то нужно вывести в колонке «Результат» значение «Заказ не требуется»,
  • Лог_выражение — выражение, дающее в результате значение ИСТИНА или ЛОЖЬ.
  • Значение_если_истина — значение, которое выводится, если логическое выражение истинно
  • Значение_если_ложь — значение, которое выводится, если логическое выражение ложно

На выходе получаем результат:

image

  • если значение в столбце «Количество» больше 5, а значение в колонке «Тип» равно «А»
  • то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».
  • Логическое_значение1-2 и т.д. — проверяемое условие, вычисление которого дает значение ИСТИНА или ЛОЖЬ

Таким образом, используя сочетание 2-х формул, мы находим решение нашей задачи и получаем результат:

image

  • если значение в столбце «Количество» равно 10, а значение в колонке «Тип» равно «А»
  • или же значение в столбце «Количество» больше или равно 5, а значение «Тип» равен «Б»
  • то нужно вывести в колонке «Результат» значение «1», в обратном случае «0».

Как видно из записи, в формулу «ЕСЛИ» включено одно условие «ИЛИ» (OR) и два условия с использованием формулы «И», включенных в него. Если хотя бы одно из условий 2-го уровня имеет значение «ИСТИНА», то в колонку «Результат» будет выведен результат «1», в противном случае будет «0».
Результат:

image

  • 1 = А
  • 2 = Б
  • 3 = В
  • 4 = Г

image

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

  • Номер_индекса — номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.
  • Значение1, значение2,… — значение от 1 до 254 аргументов-значений, из которых функция «ВЫБОР», используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.
  • 1 = А
  • 2 = Б
  • 3 = В
  • 4 = Г
  • 1 = А
  • 2 = Б
  • 3 = В
  • 5 = Г

Как видно, значение «4» в формуле нам приходится оставить пустым и перенести результат «Г» на порядковый номер «5».

  • Искомое_значение – значение, поиск которого осуществляется функцией.
  • Таблица – диапазон ячеек, содержащий данные. Именно в этих ячейках будет происходить поиск. Значения могут быть текстовыми, числовыми или логическими.
  • Номер_столбца — номер столбца в аргументе «Таблица», из которого будет выводиться значение в случае совпадения. Важно понимать, что отсчет столбцов происходит не по общей сетке листа (A.B,C,D и т.д.), а внутри массива, указанного в аргументе «Таблица».
  • Интервальный_просмотр — определяет, какое совпадение должна найти функция — точное или приблизительное.

image

Затем в рабочей части таблицы уже прописывается формула со ссылкой на справочник, заполненный ранее. Т.е. в справочнике в столбце «D» происходит поиск значения из столбца «А» и при нахождении соответствия выводится значение из столбца «Е» в столбец «В».
Синтаксис формулы:
Вывод результата в ячейку B2:

image

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

image

Видно, что строки в столбцах «Продукт» обеих таблиц не совпадают, однако, это не является препятствием для использования функции «ВПР».
Вывод результата в ячейку B2:

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

Вместо числа «1» можно использовать любое число (а также не только вычитать его, но и прибавлять к полученному значению), для получения желаемого результата, если нет желания ссылаться на определенную ячейку в столбце с нужным нам номером.
Получившийся результат:

Читайте также:  Как или чем заменить функцию ЕСЛИ в формулах Excel

image

  • Если в столбце «Справочник» указано число 1, данные должны тянуться из таблицы «Справочник_1», если число 2, то из таблицы «Справочник_2» в соответствии с указанным месяцем

image

Вариант решения, который сразу приходит на ум, следующий:
Вывод результата в ячейку C3:

Плюсы: формула может включать до 254 наименований справочников, минусы – их наименование должно быть строго числовым.
Результат для формулы с использованием функции «ВЫБОР»:

image

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

image

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

В функции «ВПР» в качестве аргумента «искомое_значение» используем все ту же связку трех признаков (наименование_класс_фасовка), но берем ее уже в таблице для заполнения и заносим непосредственно в аргумент (как вариант, можно было бы выделить значение для аргумента в дополнительный столбец в таблице для заполнения, но это действие будет излишним).
Напоминаю, что использование функции «ЕСЛИОШИБКА» (IFERROR) необходимо, если искомое значение так и не будет найдено, и функция «ВПР» выведет нам значение «#Н/Д» (об этом ниже).
Результат на картинке ниже:

image

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

Кейс_3 Поиск значения в массиве, или когда ВПР не в силах нам помочь

  • в столбце «Условие поиска» указано значение и необходимо определить, присутствует ли оно в столбце «Массив для поиска»

image

Как мы видим, функция «ВПР» тут бессильна, т.к. мы ищем не точное совпадение, а именно наличие в ячейке нужного нам значения.
Для решения задачи необходимо использовать комбинацию нескольких функций, а именно:
«ЕСЛИ»
«ЕСЛИОШИБКА»
«СТРОЧН»
«НАЙТИ»

По порядку обо всех, «ЕСЛИ» мы уже разобрали ранее, потому перейдем к функции «ЕСЛИОШИБКА» (IFERROR)

  • Значение — аргумент, проверяемый на возникновение ошибок.
  • Значение_при_ошибке — значение, возвращаемое при ошибке при вычислении по формуле. Возможны следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!.
  • Текст — текст, преобразуемый в нижний регистр.

Теперь подробнее о синтаксисе функции «НАЙТИ» (FIND).

  • Искомый_текст — текст, который необходимо найти.
  • Просматриваемый_текст — текст, в котором нужно найти искомый текст.
  • Нач_позиция — знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер не указан, он по умолчанию считается равным 1.
  1. СТРОЧН(A2) – преобразует аргумент «Искомый_текст» в ячейке в А2 в текст с нижним регистром
  2. Функция «НАЙТИ» начинает поиск преобразованного аргумента «Искомый_текст» в массиве «Просматриваемый_текст», который преобразовывается функцией «СТРОЧН(E2)», также в текст с нижним регистром.
  3. В случае если, функция находит совпадение, т.е. возвращает порядковый номер первого символа совпадающего слова/значения, срабатывает условие ИСТИНА в формуле «ЕСЛИ», т.к. полученное значение не равно нулю. Как результат, в столбце «Результат» будет выведено значение «Bingo!»
  4. Если же, функция не находит совпадение т.е. порядковый номер первого символа совпадающего слова/значения не указывается и вместо значения возвращается ошибка, срабатывает условие, заложенное в формулу «ЕСЛИОШИБКА» и возвращается значение равное «0», что соответствует условию ЛОЖЬ в формуле «ЕСЛИ», т.к. полученное значение равно «0». Как результат, в столбце «Результат» будет выведено значение «fail».

image

Как видно из рисунка выше, благодаря функциям «СТРОЧН» и «НАЙТИ» мы находим искомые значения вне зависимости от регистра символов, и места нахождения в ячейке, но необходимо обратить внимание на строку 5.
Условие поиска задано как «111», но в массиве поиска указано значение «1111111 печенюшки», однако формула выдает результат «Bingo!». Это происходит потому, что значение «111» входит в ряд значений «1111111», как следствие находится совпадение. В обратном случае данное условие не сработает.

Кейс_4 Поиск значения в массиве по нескольким условиям, или когда ВПР тем более не в силах нам помочь

Представим ситуацию, когда необходимо найти значение из «Таблица с результатом» в двумерном массиве «Справочник» по нескольким условиям, а именно по значению «Наименование» и «Месяц».
Табличная форма задания будет иметь следующий вид:

image

  • В таблицу с результатом необходимо подтянуть данные в соответствии с совпадением условий «Наименование» и «Месяц».

Синтаксис функции «ИНДЕКС» (INDEX)

  • Массив — диапазон ячеек, из которого будут показываться значения в случае совпадения условий их поиска.
  • Если массив содержит только одну строку или один столбец, аргумент «номер_строки» или «номер_столбца» соответственно не является обязательным.
  • Если массив занимает больше одной строки и одного столбца, а из аргументов «номер_строки» и «номер_столбца» задан только один, то функция «ИНДЕКС» возвращает массив, состоящий из целой строки или целого столбца аргумента «массив».
  • Номер_строки — номер строки в массиве, из которой требуется возвратить значение.
  • Номер_столбца — номер столбца в массиве, из которого требуется возвратить значение.

Синтаксис функции «ПОИСКПОЗ» (MATCH)

  • Искомое_значение — значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.
  • Просматриваемый_массив — диапазон ячеек, в которых производится поиск.
  • Тип_сопоставления — необязательный аргумент. Число -1, 0 или 1.

возвращает значение #Н/Д, т.к. значение «1» отсутствует в просматриваемом диапазоне.

Таким образом, мы получили координаты точки (1; #Н/Д) которые функция «ИНДЕКС» использует для поиска в аргументе «Массив».
Полностью написанная функция для ячейки B4 будет иметь следующий вид:

Поскольку, аргумент «Номер_столбца» имеет значение «#Н/Д», то результат для ячейки «B4» будет соответствующий.
Как видно из получившегося результата не все значения в таблице с результатом находят совпадение со справочником и в итоге мы видим, что часть значений в таблице выводится в виде «#Н/Д», что затрудняет использование данных для дальнейших расчетов.
Результат:

image

Что бы нейтрализовать этот негативный эффект используем функцию «ЕСЛИОШИБКА», о которой мы читали ранее, и заменяем значение, возвращающееся при ошибке на «0», тогда формула будет иметь вид:
Вывод результата в ячейку B4:

image

Как видно на картинке, значения «#Н/Д» более не мешают нам в последующих вычислениях с использованием значений в таблице с результатом.

Кейс_5 Поиск значения в диапазоне чисел

  • От 0 до 1000 = А
  • От 1001 до 1500 = Б
  • От 1501 до 2000 = В
  • От 2001 до 2500 = Г
  • Более 2501 = Д

image

Функция ПРОСМОТР (LOOKUP) возвращает значение из строки, столбца или массива. Функция имеет две синтаксических формы: векторную и форму массива.

  • Искомое_значение — значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
  • Просматриваемый_вектор — диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
  • Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
  • Вектор_результатов — диапазон, состоящий из одной строки или столбца. Вектор_результатов должен иметь тот же размер, что и просматриваемый_вектор.

Вывод результата в ячейку B3:

image

Аргументы «Просматриваемый_вектор» и «Вектор_результата» можно записать в форме массива – в этом случае не придется выводить их в отдельную таблицу на листе Excel.
В этом случае функция будет выглядеть следующим образом:
Вывод результата в ячейку B3:
=ПРОСМОТР(E3;<0;1001;1501;2001;2501>;<«А»;«Б»;«В»;«Г»;«Д»>)

Кейс_6 Суммирование чисел по признакам

Для суммирования чисел по определенным признакам можно использовать три разных функции:
СУММЕСЛИ (SUMIF) – суммирует только по одному признаку
СУММЕСЛИМН (SUMIFS) – суммирует по множеству признаков
СУММПРОИЗВ (SUMPRODUCT) – суммирует по множеству признаков
Существует также вариант с использованием «СУММ» (SUM) и функции формулы массивов, когда формула «СУММ» возводится в массив:
(<=СУММ(()*())>
но такой подход довольно неудобен и полностью перекрывается по функционалу формулой «СУММПРОИЗВ»
Теперь подробнее по синтаксису «СУММПРОИЗВ»:

  • Массив1 — первый массив, компоненты которого нужно перемножить, а затем сложить результаты.
  • Массив2, массив3… — от 2 до 255 массивов, компоненты которых нужно перемножить, а затем сложить результаты.
  • Найти общую сумму по стоимости отгрузок по каждому из продуктов за определенный период:

image

Как видно из таблицы с данными, что бы посчитать стоимость необходимо цену умножить на количество, а полученное значение, применив условия отбора переносить в таблица с результатом.
Однако, формула «СУММПРОИЗ» позволяет проводить такие расчеты внутри формулы.
Вывод результата в ячейку B4:

– перемножаем столбцы «Количество» и «Цена» в таблице с данными.
Несомненным плюсом данной функции является свободный порядок записи условий, их можно записывать в любом порядке, на результат это не повлияет.
Результат:

image

Теперь усложним условие и добавим требование, что бы отбор по наименованию «печеньки» происходил только по классам «малые» и «большие», а по наименованию «булки» все, кроме по классу «с джемом»:

Вывод результата в ячейку B4:

– на самом деле, в данной формуле можно было написать условие отбора также как и при отборе по печенькам, но тогда, пришлось бы перечислять три условия в формуле, в данном случае, проще написать исключение – не равно «с джемом» для этого используем значение «<>».
Вообще, если группы признаков/классов заранее известны, то лучше объединять их в эти группы, создавая справочники, чем записывать все условия в функцию, раздувая ее.
Результат:

image

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

Логические операторы в Excel

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

Ниже приведены наиболее часто используемые логические операторы в Excel –

Мистер Нет. Символ логического оператора в Excel Имя оператора Описание
1 знак равно Равно Сравнивает одно значение с другим значением
2 > Лучше чем Проверяет, больше ли значение определенного значения или нет
3 Меньше, чем Проверяет, меньше ли значение определенного значения или нет
4 > = Больше или равно Проверяет, действительно ли значение больше или равно определенному значению или нет
5 Меньше или равно Проверяет, действительно ли значение меньше или равно определенному значению или нет
6 <> Не равно Проверяет, действительно ли значение не равно определенному значению или нет

Теперь мы подробно рассмотрим каждый из них.

# 1 Знак равенства (=) для сравнения двух значений

Мы можем использовать знак равенства (=), чтобы сравнить одно значение ячейки со значением другой ячейки. Мы можем сравнивать все типы значений, используя знак равенства. Предположим, у нас есть следующие значения от ячейки A1 до B5.

Логические операторы Excel - шаг 1

Теперь я хочу проверить, равно ли значение в ячейке A1 значению ячейки B1.

  • Шаг 1: Чтобы выбрать значение от A1 до B1, откроем формулу со знаком равенства.

Логические операторы Excel - шаг 2

  • Шаг 2: Выберите ячейку A1 сейчас.

Логические операторы Excel - шаг 3

  • Шаг 3: Теперь введите еще один символ логического оператора – знак равенства (=).

Логические операторы Excel - шаг 4

  • Шаг 4: Теперь выберите вторую сравниваемую ячейку, то есть ячейку B2.

Логические операторы Excel - шаг 5

  • Шаг 5: Хорошо, мы закончили. Нажмите клавишу ввода, чтобы закрыть формулу. Скопируйте и вставьте его в другие ячейки.

Логические операторы Excel - шаг 6

Таким образом, мы получили ИСТИНА в результате, если значение ячейки 1 равно ячейке 2, иначе в результате мы получили ЛОЖЬ.

# 2 Знак “больше” (>) для сравнения числовых значений

В отличие от знака равенства (=) знак больше (>) может проверять только числовые значения, но не текстовые значения. Например, если ваши значения в ячейках от A1 до A5 и вы хотите проверить, превышают ли эти значения (>) значение 40 или нет.

Больше, чем шаг 1

  • Шаг 1: Откройте формулу в ячейке B2 и выберите ячейку A2 в качестве ссылки на ячейку.

Больше, чем шаг 2

  • Шаг 2: Поскольку мы проводим тестирование, значение больше, чем символ упоминания> и применяется условие как 40.

Больше, чем шаг 3

  • Шаг 3: Закройте формулу и примените ее к оставшимся ячейкам.

Больше, чем шаг 4

Только одно значение> 40, т. Е. Значение ячейки A3.

В ячейке A6 значение 40; поскольку мы применили логический оператор> в качестве возвращаемой формулы критерия, результатом будет ЛОЖЬ. Мы увидим, как решить эту проблему в следующем примере.

# 3 Знак “больше” или “равно” (> =) для сравнения числовых значений

В предыдущем примере мы видели, что формула возвращает значение ИСТИНА только для тех значений, которые больше значения критерия. Но если значение критерия также должно быть включено в формулу, тогда нам нужно использовать символ> =.

Больше или равно

Предыдущая формула исключила значение 40, но эта формула включила.

# 4 Меньше знака ( # 5 Знак “меньше или равно” ( = включал значение критерия в формулу аналогично, №6 Знак не равно (<>) для сравнения числовых значений

Комбинация знаков больше (>) и меньше ( . Это работает полностью противоположно знаку равенства. Знак равенства (=) проверяет, равно ли одно значение другому значению и возвращает значение ИСТИНА, тогда как знак «Не равно» <> возвращает ИСТИНА, если одно значение не равно другому значению, и возвращает ЛОЖЬ, если одно значение равно другому. один.

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

Логический оператор в Excel с формулами

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

# 1 – ЕСЛИ со знаком равенства

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

ЕСЛИ со знаком равенства 1

Формула возвращает Одна и та же если значение ячейки A2 равно значению B2; если нет, он вернется Другой.

ЕСЛИ со знаком равенства 2

# 2 – ЕСЛИ со знаком “больше”

Мы можем проверить определенные числовые значения и получить результаты, если условие ИСТИНА, и вернуть другой результат, если условие ЛОЖНО.

ЕСЛИ с большим, чем

# 3 – ЕСЛИ со знаком меньше

Приведенная ниже формула покажет логику применения if с меньшим числом логических операторов.

Быстрое сравнение значений в двух диапазонах Excel

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

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

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

kak sravnit dva stolbtsa v excel

Надстройка позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. находить элементы диапазона №1, которых нет в диапазоне №2;

3. находить элементы диапазона №2, которых нет в диапазоне №1;

4. находить элементы диапазона №1, которые есть в диапазоне №2;

5. находить элементы диапазона №2, которые есть в диапазоне №1;

6. выбирать один из девяти цветов заливки для ячеек с искомыми значениями;

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

8. вместо сравнения числовых значений использовать сравнение текстовых значений при помощи опции «Сравнить числа как текст»;

9. сравнивать значения в ячейках диапазона, не учитывая лишние пробелы;

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

vlookup ваш друг!

расположите столбец, по одному значению на строку, в столбце A каждой электронной таблицы. в столбце B большего листа введите

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

где результат формулы равен FALSE, эти данные не находятся на другом листе.

Это может показаться взломом, но я лично предпочитаю копировать ячейки как текст (или экспортировать как CSV) в WinMerge лежит или любой другой инструмент diff. Предполагая, что два листа содержат в основном идентичные данные, Winmerge покажет разницы немедленно.

LibreOffice предоставляет функцию сравнения книги: Изменить — > Сравнить Документ

Excel имеет это встроенное Если у вас есть версия excel с запрос надстройки.

этой ссылке С веб-страницы office описывает процесс включения надстройки, если она не активирована, и как сравнить две сравнить две книги — среди прочего.

простой способ: используйте 3-й лист для проверки.

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

  1. перейдите к листу 3, ячейка A1, введите =IF(Sheet2!A1<>Sheet1!A1,»difference»,»») .
  2. затем выберите все ячейки листа 3, заполнения, заполнения справа.
  3. клетки, которые отличаются между листом 1 и листом 2 теперь будет сказано «разница» в листе 3.

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

вы должны попробовать этот бесплатный онлайн инструмент — www.cloudyexcel.com/compare-excel/

работает хорошо большую часть времени, иногда результаты немного.

плюс это также дает хороший визуальный выход

enter image description here

вы также можете загрузить результаты в формате excel. (вам нужно зарегистрироваться для этого)

COUNTIF хорошо работает для быстрой проверки разницы. И это легче запомнить и проще работать с чем VLOOKUP .

даст вам столбец, показывающий 1, Если есть совпадение и ноль, если нет совпадения (с бонусом показа >1 для дубликатов в самом списке).

Если у вас есть Microsoft Office Professional Plus 2013, Вы можете использовать Сравнение Электронных Таблиц Microsoft выполнить отчет о различиях между двумя книгами.

Запустить Сравнение Электронных Таблиц:

в Windows 7: в меню Пуск Windows в разделе Средства Office 2013 выберите сравнение электронных таблиц.

в Windows 8: на начальном экране нажмите сравнить таблицы. Если вы не видите плитку сравнения электронных таблиц, начните вводить слова Сравните таблицу, а затем выберите ее плитку.

сравните две книги Excel:

  1. Нажмите Главная > Сравнить Файлы.
  2. a. Щелкните синий значок папки рядом с полем сравнить, чтобы перейти к расположению более ранней версии книги. (Помимо файлов, сохраненных на компьютере или в сети, можно ввести веб-адрес сайта, на котором сохранены книги.)
  3. b. Щелкните значок зеленой папки рядом с Чтобы выбрать расположение книги, которую требуется сравнить с более ранней версией, и нажмите кнопку ОК. (Подсказки можно сравнить два файла с одинаковым именем, если они сохранены в разных папках.)
  4. в левой панели выберите параметры, которые вы хотите видеть в результатах сравнения книги, установив или сняв флажок параметры, такие как Формулы, макросы или формат ячейки. Или просто выберите Все.
Читайте также:  Фигуры в диаграммах и графиках для презентации отчета в Excel

Я думаю, что ваш лучший вариант-бесплатное приложение под названием Compare IT! . абсолютно блестящая утилита и dead проста в использовании. http://www.grigsoft.com/wincmp3.htm

Так на самом деле, что вы используете excel означает, что вы можете использовать Таблица Сравнения из Microsoft. Он доступен из Office 2013. Да я знаю, что этот вопрос старше 6 лет. Но кто знает, может быть, кому-то нужна эта информация сегодня.

может быть, этот повтор слишком поздно. Но надежда поможет кому-то, кто ищет решение

то, что я сделал, я сохранил оба файла excel как CSV-файл и сравнивал с Windiff.

ExcelDiff экспортирует HTML-отчет в разделенном (бок о бок) или Объединенном (наложении) виде, выделяя различия, а также строку и столбец.

плагин Notepad++ compare отлично работает для этого. Просто сохраните свои простыни как .csv файлы и сравнить их в Notepad++. Notepad++ дает вам хороший визуальный diff.

Я использовал сравнение Excel. Это payware, но у них есть 15-дневная пробная версия. Он будет сообщать об измененных строках, добавленных строках и удаленных строках. Он будет соответствовать на основе имени рабочего листа (в качестве опции):

используйте Условное форматирование, чтобы выделить различия в excel.

только с одним столбцом данных в каждом Для сравнения сводной таблицы может предоставить гораздо больше информации. На изображении ниже ColumnA находится в Sheet1 (с копией в Sheet2 для изображения) и ColumnC в Sheet2. На каждом листе добавлен флаг источника (столбцы B и D на изображении). PT был создан с помощью несколько диапазонов консолидации ( Sheet1!$A:$B и Sheet2!$C:$D ):

SO1500153 exaple

левый числовой столбец показывает что присутствует в Sheet1 (включая q дважды) и правильный какой в Лист2 (опять же с дубликатами – из c и d ). d-l находятся в Sheet1, но не Sheet2 и w и z находятся в Sheet2 (исключая те, которые там только для изображения), но не Sheet1. Добавить дисплей Show grand totals for columns и появятся контрольные итоги.

Как сравнить две таблицы в Excel?

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

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

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

Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3», мы получим результат «ИСТИНА», если содержимое ячеек одинаковое, и «ЛОЖ», если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.

Простое сравнение упорядоченных значений

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

Быстрое выделение отличий в столбцах

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

Создаем правило для условного форматирования

В диспетчере правил выбираем пункт «Создать правило», а в создании правил выбираем «Использовать формулу для определения форматируемых ячеек». Теперь мы можем задать формулу «=$B3<>$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат».

Диспетчер правил условного форматирования

«Использовать формулу для определения форматируемых ячеек»

Выбираем форматирование

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

Появилось правило в диспетчере правил условного форматирования

Результат условного форматирования

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

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

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

Создание правила форматирования

Задаем условия для форматирования

В нашей формуле используется функция «СЧЁТЕСЛИ», которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12», которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.

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

Результат условного форматирования в Экселе

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

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

Необходимая нам формула будет выглядеть следующим образом: «=ВПР(B18;$B$3:$C$10;2;ЛОЖЬ)». Первое значение «B18» соответствует первой ячейке искомого наименования товара. Второе значение «$B$3:$C$10» означает постоянный адрес диапазона таблицы старого прайса, значения из которой нам понадобятся. Третье значение «2» означает номер столбца из выделенного диапазона, в ячейке которого мы и будем брать старую цену товара. И последнее значение «ЛОЖЬ» задает поиск только по точному совпадению значений. После протаскивания формулы по всему столбцу новой таблицы мы получим в этом столбце старые значения цен по каждой позиции, имеющейся в новой таблице. Напротив наименования последнего товара формула выводит сообщение ошибки «#Н/Д», что свидетельствует об отсутствии данного наименования в старом прайсе.

Сравнение неупорядоченных значений и получение значения из нужного столбца в строке

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

Эта функция сравнивает ячейки в столбцах вертикально.
Аналогично можно применить функцию ГПР для сравнения значений в строках.
Этот способ, в отличие от ранее описанного, хорош тем, что позволяет отфильтровать (выбрать) неверные данные.

  1. Для сравнения данных в эталонном и проверяемым столбцах следует перенести обе таблицы в один документ.
  2. После чего необходимо добавить столбец или выбрать уже существующий в непосредственной близости от проверяемого столбца. В этот столбец запишем функцию ВПР(), которая послужит маркером отличий от эталонного столбца.
  3. Функция выглядит следующим образом:
    =ВПР(B3;A:A;1;0) , где B3- проверяемая ячейка, A:A — диапазон с эталонным списком, 1 — номер столбца (в данном случае равен «1»), 0- количество допускаемых ошибок в ячейке.Сравнение значений в Excel при помощи ВПР
  4. После того, когда мы протянем функцию по длине проверяемого списка.
  5. Напротив ячеек с отличиями от эталонных значений будут стоять буквы «Н/Д».
  6. Такие ячейки можно отфильтровать стандартным фильтром и обработать отклонения.фильтр ошибочных значений

Этот способ довольно прост и не требует особых навыков владения программой «Excel»

Сравнение фамилий

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

  • вручную отредактировать каждую ячейку диапазона С2:С11;
  • заставить Excel проделать эту работу с помощью формул.

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

  • сосчитать количество символов в ячейке С2 (где находится фамилия и инициалы сотрудника анализируемой нами строки) до первого пробела;
  • извлечь из ячейки С2 количество символов, расположенных слева от первого пробела.

Для определения количества символов, предшествующих первому пробелу, можно применить текстовую функцию НАЙТИ. Синтаксис ее следующий:

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

Данная функция находит вхождение одной текстовой строки (искомый_текст) в другую (просматриваемый_текст) и начальное положение искомого текста относительно крайнего левого символа просматриваемого текста.

Первый символ в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он считается равным 1.

Вызвав панель функции НАЙТИ (рис. 5.19), в поле Найти_текст введите с клавиатуры пробел. В поле Внутр_текст введите ссылку на ячейку С2, а третий аргумент оставьте незаполненным. В ячейке Р2 появится следующая формула:

Применив далее функцию ЛЕВСИМВ

вы получите в ячейке Q2 фамилию из ячейки С2, но без инициалов. (Панель функции ЛЕВСИМВ приведена в главе 4, на рис. 4.4.)

Первым аргументом функции ЛЕВСИМВ является адрес ячейки, где расположена фамилия с инициалами. Вторым аргументом должно быть число символов, из которого состоит фамилия. Поэтому, если определено положение пробела, следующего после фамилии, то число символов фамилии будет равно номеру позиции этого пробела минус 1. Таким образом, во втором аргументе функции ЛЕВСИМВ из значения ячейки Р2, определенного функцией НАЙТИ, следует вычесть значение 1.

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

В ячейке R2 должна использоваться такая формула:

Если фамилии в обоих списках совпадут, то в ячейке R11 появится значение ИСТИНА, если не совпадут — значение ЛОЖЬ.

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

где логическое_значение 1, логическое_значение2, . — это проверяемые условия (их может быть от 1 до 30), которые имеют либо значение ИСТИНА, либо значение ЛОЖЬ.

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

В нашем случае результаты сравнений находятся в ячейках N2, О2 и R2. Вызвав панель функции И (рис. 5.20), введите в качестве ее аргументов ссылки на эти ячейки. В ячейке S2 появится новая формула:

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

Использование макроса VBA

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

Sub Find_Matches()
Dim CompareRange As Variant , x As Variant , y As Variant
» Установка переменной CompareRangeравной сравниваемому диапазону
Set CompareRange = Range(«B1:B11» )
» Если сравниваемый диапазон находится на другом листе или книге,
» используйте следующий синтаксис
» Set CompareRange = Workbooks(«Книга2»). _
» Worksheets(«Лист2»).Range(«B1:B11»)
»
» Сравнение каждого элемента в выделенном диапазоне с каждым элементом
» переменной CompareRange
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 2) = x
Next y
Next x
End Sub

В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.

Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_ Matches и щелкните кнопку выполнить.

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

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

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

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

Очистка излишнего форматирования

Данный инструмент позволяет очистить излишнее форматирование ячеек в книге, к примеру, ячеек, которые отформатированы, но не содержат значений. Инструмент Clean Excess Cell Formatting поможет «любителям» заливать цветом всю строку рабочей книги, вместо заливки определенных строк таблицы.

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

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

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

Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3» , мы получим результат «ИСТИНА» , если содержимое ячеек одинаковое, и «ЛОЖ» , если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.

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

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

В диспетчере правил выбираем пункт «Создать правило» , а в создании правил выбираем . Теперь мы можем задать формулу «=$B3<>$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат» .

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

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

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

Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1» , и в меню «Условное форматирование» выбираем пункт «Создать правило…» . В появившемся окошке выбираем , вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.

В нашей формуле используется функция «СЧЁТЕСЛИ» , которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12» , которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.

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

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

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

Необходимая нам формула будет выглядеть следующим образом: «=ВПР(B18;$B$3:$C$10;2;ЛОЖЬ)» . Первое значение «B18» соответствует первой ячейке искомого наименования товара. Второе значение «$B$3:$C$10» означает постоянный адрес диапазона таблицы старого прайса, значения из которой нам понадобятся. Третье значение «2» означает номер столбца из выделенного диапазона, в ячейке которого мы и будем брать старую цену товара. И последнее значение «ЛОЖЬ» задает поиск только по точному совпадению значений. После протаскивания формулы по всему столбцу новой таблицы мы получим в этом столбце старые значения цен по каждой позиции, имеющейся в новой таблице. Напротив наименования последнего товара формула выводит сообщение ошибки «#Н/Д» , что свидетельствует об отсутствии данного наименования в старом прайсе.

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

Ссылка на основную публикацию