Как написано в Microsoft Excel, ссылка на диапазон ячеек, выделенных на изображении sdo

Обновлено: 21.11.2024

В Excel есть множество функций поиска (например, ВПР, ПРОСМОТР, ИНДЕКС/ПОИСКПОЗ, ССПР), которые могут перейти и выбрать значение из списка.

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

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

Но это не значит, что это невозможно.

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

Это просто, но вы будете выглядеть как волшебник Excel (все, что вам нужно, это это руководство и ловкость рук на клавиатуре).

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

<Р>

Относительные ссылки

По умолчанию все ссылки на ячейки являются относительными.При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

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

Абсолютные ссылки

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.

В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.

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

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В нашем примере мы будем использовать ставку налога с продаж в размере 7,5 % в ячейке E1 для расчета налога с продаж для всех товаров в столбце D. Нам потребуется использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

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

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

Использование ссылок на ячейки с несколькими листами

Большинство программ для работы с электронными таблицами позволяют ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на конкретное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.

Обратите внимание: если имя рабочего листа содержит пробел, вам нужно будет заключить имя в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.

Чтобы ссылаться на ячейки на листах:

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

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

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

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

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

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

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

Формула совпадений

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

Формула различий

Чтобы найти ячейки в одной строке с разными значениями, просто замените знак равенства на знак отсутствия равенства (<>):

Совпадения и различия

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

Результат может выглядеть примерно так:

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

Пример 2.Сравните два списка совпадений с учетом регистра в одной строке

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

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "")

Чтобы найти различия в одной и той же строке с учетом регистра, введите соответствующий текст ("Уникальный" в данном примере) в 3-й аргумент функции ЕСЛИ, например:

=ЕСЛИ(ТОЧНО(A2, B2), "Соответствует", "Уникальный")

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

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

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

Пример 1. Поиск совпадений во всех ячейках одной строки

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

=ЕСЛИ(И(A2=B2, A2=C2), "Полное совпадение", "")

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

=ЕСЛИ(СЧЁТЕСЛИ($A2:$E2, $A2)=5, "Полное совпадение", "")

Где 5 — количество сравниваемых столбцов.

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

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

=ЕСЛИ(ИЛИ(A2=B2, B2=C2, A2=C2), "Соответствие", "")

Если нужно сравнить много столбцов, оператор ИЛИ может стать слишком большим. В этом случае лучшим решением будет добавление нескольких функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько столбцов имеют то же значение, что и в 1-м столбце, второй СЧЁТЕСЛИ подсчитывает, сколько из оставшихся столбцов равны 2-му столбцу, и так далее. Если счетчик равен 0, формула возвращает «Уникальный», в противном случае — «Совпадение». Например:

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2,A2)+СЧЁТЕСЛИ(C2:D2,B2)+(C2=D2)=0,"Уникальный","Совпадение")

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

Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые строки), которые находятся в столбце A, но не в столбце B.

Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B, $A2)=0 в логическую проверку ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (как минимум 1 совпадение). найдено).

Например, следующая формула ЕСЛИ/СЧЕТЕСЛИ ищет по всему столбцу B значение в ячейке A2. Если совпадений не найдено, формула возвращает «Нет совпадений в B», в противном случае — пустую строку:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "")

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

Того же результата можно добиться, используя формулу ЕСЛИ со встроенными функциями ЕОШИБКА и ПОИСКПОЗ:

=IF(ISERROR(MATCH($A2,$B$2:$B$10,0)),"Нет совпадений в B","")

Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

=IF(SUM(--($B$2:$B$10=$A2))=0, "Нет совпадений в B", "")

Если вы хотите, чтобы одна формула определяла как совпадения (дубликаты), так и различия (уникальные значения), поместите некоторый текст для совпадений в пустые двойные кавычки ("") в любой из приведенных выше формул. Например:

=ЕСЛИ(СЧЁТЕСЛИ($B:$B, $A2)=0, "Нет совпадений в B", "Соответствует в B")

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

Иногда может потребоваться не только сопоставить два столбца в двух разных таблицах, но и извлечь совпадающие записи из таблицы поиска. В Microsoft Excel для этого предусмотрена специальная функция — функция ВПР. В качестве альтернативы вы можете использовать более мощную и универсальную формулу INDEX MATCH. Пользователи Excel 2021 и Excel 365 могут выполнить эту задачу с помощью функции XLOOKUP.

=ВПР(D2, $A$2:$B$6, 2, ЛОЖЬ)

=ИНДЕКС($B$2:$B$6, ПОИСКПОЗ($D2, $A$2:$A$6, 0))

Если вам не очень удобно работать с формулами, вы можете выполнить эту работу с помощью быстрого и интуитивно понятного решения — Мастера объединения таблиц.

Сравните два списка и выделите совпадения и различия

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

Пример 1. Выделение совпадений и различий в каждой строке

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

Выберите ячейки, которые хотите выделить (вы можете выбрать ячейки в одном столбце или в нескольких столбцах, если хотите закрасить целые строки).
Нажмите Условное форматирование > Новое правило… > Используйте формулу, чтобы определить, какие ячейки нужно отформатировать.
Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок столбца). Убедитесь, что вы используете относительную ссылку на строку (без знака $), как в приведенной выше формуле.

Чтобы выделить различия между столбцами A и B, создайте правило со следующей формулой:

=$B2<>$A2

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

Пример 2. Выделение уникальных записей в каждом списке

Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

Элементы, находящиеся только в первом списке (уникальные)
Элементы, находящиеся только во втором списке (уникальные)
Элементы, которые есть в обоих списках (дубликаты) — показано в следующем примере.

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

Предположим, что ваш список 1 находится в столбце A (A2:A6), а список 2 — в столбце C (C2:C5). Вы создаете правила условного форматирования со следующими формулами:

Выделить уникальные значения в списке 1 (столбец A):

Выделить уникальные значения в списке 2 (столбец C):

И получите следующий результат:

Пример 3. Выделение совпадений (дубликатов) между двумя столбцами

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

Выделить совпадения в списке 1 (столбец A):

Выделить совпадения в списке 2 (столбец C):

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

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

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

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

Где A2, B2 и C2 — самые верхние ячейки, а 3 — количество столбцов для сравнения.

Конечно, ни формула И, ни СЧЁТЕСЛИ не ограничиваются сравнением только 3 столбцов, вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбцах.

Пример 2. Сравните несколько столбцов и выделите различия в строках

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

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

Чтобы изменить столбец сравнения, используйте либо клавишу Tab для перемещения по выбранным ячейкам слева направо, либо клавишу Enter для перемещения сверху вниз.

Совет. Чтобы выбрать несмежные столбцы, выберите первый столбец, нажмите и удерживайте клавишу Ctrl , а затем выберите другие столбцы. Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу Tab или Enter, как описано выше.

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

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

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

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

Способ сравнения двух столбцов/списков в Excel без формул

Теперь, когда вы знакомы с предложениями Excel для сравнения и сопоставления столбцов, позвольте мне показать вам наше собственное решение для этой задачи. Этот инструмент называется «Сравнить две таблицы» и входит в состав Ultimate Suite.

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

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

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

Повторяющиеся значения (совпадения) — элементы, существующие в обоих списках.
Уникальные значения (различия) — элементы, которые присутствуют в списке 1, но отсутствуют в списке 2.

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

Здесь доступно несколько различных вариантов. Для наших целей эти два наиболее полезны:

Выделение цветом: оттенки совпадают или различаются в выбранном цвете (например, условное форматирование в Excel).
Идентифицировать в столбце "Статус" — вставляет столбец Статус с метками "Дубликат" или "Уникальный" (как в формулах ЕСЛИ).

Для этого примера я решил выделить дубликаты следующим цветом:

И через мгновение получил следующий результат:

Для столбца Статус результат будет выглядеть следующим образом:

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

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

Читайте также: