Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как проверить или найти значение в списке в Excel?

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

Sverit 2 tablici 1 8 способов как сравнить две таблицы в Excel Добрый день!

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

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

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

  • Простой способ сравнить два рабочих листа Excel;
  • Быстро выделить значения, которые отличаются;
  • Сравнить два рабочих листа Excel с помощью условного форматирования;
  • Сравнить два рабочих листа Excel с помощью функции КОНТРОЛИРУЕМЫЙ и правил;
  • Как сравнить два рабочих листа Excel с помощью функции БПФ;
  • Как сравнить два рабочих листа Excel с помощью функции ЕСЛИ;
  • Как сравнить два рабочих листа с помощью макроса VBA;
  • Как сравнить с помощью дополнения Инквер.

Проверка с помощью функции СЧЁТЕСЛИ

В этом варианте мы будем считать, сколько раз каждый продукт появляется в прайс-листе поставщика. Если BATCHESLY возвращает ноль, значит, этот продукт не существует. Формула выглядит следующим образом:

проверкас помощью СЧЁТЕСЛИ

Как это работает? Сначала BATCHESLY подсчитывает количество вхождений каждого элемента в прайс-лист. Затем Excel сравнивает это значение с нулем. Если оно больше нуля, возвращается TRUE, а если меньше нуля, возвращается FALSE. Товары, для которых формула возвращает FALSE, отсутствуют у поставщика.

Мне не нравится, когда таблица наполовину заполнена, поэтому я заменяю TRUE строкой «IN FAITH», а FALSE — «NONE». Это можно легко сделать с помощью функции IF.

Читайте так же:
Как применить разные верхние или нижние колонтитулы на каждой странице электронной таблицы Excel?

получили информативную таблицу

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

2 вариант

Можно разместить элементы выпадающего списка в диапазоне на том же листе EXCEL, а затем указать ссылку на этот диапазон в столбце Источник.

Например, названия отделов расположены в диапазоне A2:A8.

Введите абсолютную ссылку на диапазон $A$2:$A$8 в поле Source (Источник).

2 вариант заполенения поля «Источник»

заполнение выпадающего списка

Внимание. Выпадающий список должен находиться на том же листе, что и диапазон Источник.

Использование индекса и MATCH вместо ВРОТ

Существует ряд ограничений, которые относятся только к использованию функции RIGHT. Это означает, что столбец, содержащий и поиск, всегда должен находиться слева от столбца, содержащего возвращаемое значение. Если ваша таблица построена не таким образом, не используйте INSERT. Вместо этого используйте комбинацию функций INDEX и MATCH.

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

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска значения

Другие примеры использования индексов и MATCH вместо PROCESS смотрите в статье Билла Джелена, Microsoft MVP, Bill Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/.

Трюк №16. Проверка данных на основе списка на другом листе Excel

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

Читайте так же:
Как привязать ячейки формулы (изменить ссылку на ячейку на абсолютную) в Excel?

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

Способ 1. Именованные диапазоны

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

Выделите ячейку, в которой должен будет появиться раскрывающийся список, а затем выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyRange . Щелкните на кнопке ОК. Поскольку вы использовали именованный диапазон, ваш список (хотя он и находится на другом листе) теперь можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ

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

Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT(«Sheetl!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«Sheetl!$A$1:$A$8») . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

Читайте так же:
Как разбить содержимое ячейки на столбцы или строки на основе новой строки в листе Google?

Если имя листа, содержащего список, содержит пробелы, используйте следующий синтаксис функции INDIRECT: =INDIRECT(«‘Sheetl’!$A$1:$A$8») , в русской версии Excel =DIRECT(«‘Sheetl’!$A$1:$A$8») . Разница в том, что здесь один апостроф стоит после первой инвертированной запятой, а второй апостроф — перед восклицательным знаком.

Преимущества и недостатки обоих способов

Именованные диапазоны и функция INDIRECT имеют свои преимущества и недостатки. Используя именованный диапазон, вы можете вносить любые изменения в имя листа, не затрагивая контрольный лист. Использование функции INDIRECT имеет тот недостаток, что любое изменение имени листа не будет отражаться на нем автоматически. Преимущество функции INDIRECT заключается в том, что при удалении первой ячейки или строки или последней ячейки или строки из именованного диапазона, именованный диапазон вернет ошибку #REF! Это недостаток именованного диапазона — если вы удалите из него ячейки или строки, изменения не повлияют на контрольный список.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector