Evolcom.ru

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

Как подсчитать совпадение ячеек с X или Y в Excel?

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

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

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

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

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

  • Простой способ сравнения двух рабочих листов Excel;
  • Быстро отметить значения, которые отличаются;
  • Сравнить два рабочих листа Excel с помощью условного форматирования;
  • Сравнить два рабочих листа Excel с помощью функций и правил CONTROL;
  • Сравнить два рабочих листа Excel с помощью функции БПФ;
  • Сравнить два рабочих листа Excel с помощью функции IF;
  • Сравнить два рабочих листа Excel с помощью макроса VBA;
  • Сравнить с помощью надстройки Inquire.

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

Это самый простой и базовый способ сравнения двух таблиц. Таким образом можно сравнивать числовые и текстовые значения. Например, сравнивая два диапазона числовых значений, просто набрав в следующей ячейке формулу их равенства =C2=E2, в результате равенства ячеек мы получим ответ «TRUE», а при несовпадении — «FALSE». Теперь, просто скопировав нашу формулу через диапазон для сравнения двух столбцов в Excel, мы увидим разницу.

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

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

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

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

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

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

Читайте так же:
Как очистить ячейки, если они кажутся пустыми, но их нет в Excel?

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

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

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

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

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

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

В этой настройке мы будем использовать функцию VRP, которая позволяет сравнивать две таблицы на предмет совпадения. Чтобы сравнить два столбца, введите формулу =VRP(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула запускает последовательную проверку, чтобы увидеть, повторяется ли значение в столбце A в столбце B, и возвращает значение элемента, если оно там найдено; если значение не найдено, выводится ошибка #N/D.

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

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

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

Например, вы можете сравнить два столбца A и B в электронной таблице, а затем ввести формулу в соседний столбец C: = IF(ERROR(SEARCH(C2;$E$2:$E$7;0));»»;C2) и скопируйте его на всю область вычислений. Эта формула последовательно проверяет, встречаются ли определенные элементы из указанного столбца A в столбце B, и возвращает значение, если этот элемент встречается в столбце B.

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

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

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

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

Как посчитать количество ячеек содержащих определенное значение в Exel

Для подсчета количества ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, TXT представляет собой текст, который должны содержать ячейки, и «*» является подстановочным символом, соответствующим любому количеству символов.

Количество ячеек, которые содержат определенный текст

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

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.

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

X или Y

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

Количество ячеек, которые содержат x или y

Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».

Единый формульный раствор.

Чтобы решить задачу с помощью одной формулы, можно использовать комбинацию SUMMproV с NUMBER + Find. Формула в ячейке E4 будет иметь вид :

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

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.

Читайте так же:
Как посчитать количество вхождений в столбец в листе Google?

Складывая эти массивы (+), мы можем создать новый массив чисел. Число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах. В приведенном примере массив выглядит следующим образом:

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

Затем функция SUMMPROIZE суммирует числа.

Вспомогательный столбец решений

Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как «бесконечное множество»). Формула:

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).

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

Ошибки

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

Количество ячеек, которые содержат ошибки

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

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

Функция EOSH вычисляется для каждой ячейки в rng. Результатом является массив значений true/false:

(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:

Затем S UMPROIZE суммирует элементы этого массива и возвращает результат, который в данном примере равен 2.

Примечание: EOS учитывает все ошибки, кроме #N/A. Если вы также хотите рассчитать # N/A, используйте функцию EOSH вместо EOSH.

Вычислите ошибки также с помощью SUMM. Как и предыдущая формула, она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). При вводе формула выглядит следующим образом:

Пять символов

Для подсчета количества ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, а «?» соответствует любому одному символу.

Читайте так же:
Как повторить последнее или предыдущее действие в Excel?

Количество ячеек, содержащих 5 символов

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

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

Положительные числа

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

Количество ячеек, содержащих положительные значения

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

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

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

Отрицательные числа

Чтобы подсчитать количество ячеек, содержащих отрицательные числа в диапазоне ячеек, можно использовать функцию СКАН. В общем виде формулы (см. выше) rng — это диапазон ячеек, содержащих числа.

Количество ячеек, содержащих отрицательные значения

В примере активная ячейка содержит следующую формулу

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

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

Для включения значения в другую ячейку критерия используйте символ амперсанда (&) следующим образом:

Если в ячейке А1 находится значение «-5», критерии будут «<-5» после конъюнкции.

Цифры

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

количество ячеек, которые содержат числа

Здесь приведена формула активной ячейки

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

Нечетные числа

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

Количество ячеек, которые содержат нечетные числа

В примере формула в ячейке E4 имеет вид:

Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).

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

Функция SUMPROIZE работает напрямую с матрицами.

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

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

Остаток от деления вычисляется с помощью O STAT. В данном случае делитель равен 2, поэтому OSTAT вычисляет остаток 1 для любого нечетного числа и остаток 0 для четных чисел.

В функции SUMPROIZE этот тест выполняется в каждой ячейке B5:B10, а результатом является массив истинных/ложных значений:

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

Затем программа UMPROIZE складывает эти числа и рассчитывает 4.

Текст

Для подсчета количества ячеек, содержащих текст (т.е. не цифры, не ошибки и не пустые), можете использовать функцию СЧЁТЕСЛИ и подстановочные знаки. В общей форме формулы (выше), rng является диапазон ячеек, а «*» является подстановочным знаком, который соответствует любому количеству символов.

Количество ячеек, которые содержат текст

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

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.

  • Логические значения истина и ложь не учитываются, как текст
  • Числа не подсчитываются «*», если они не будут введены в виде текста
  • Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.

Вы также можете использовать SUMMPROIZE для вычисления текстовых значений вместе с функцией ETEXT:

Двойное тире приводит результат ETEXT булевского значения TRUE или FALSE к 1 и 0. Затем SUMMPROIZE складывает эти значения вместе, чтобы получить результат.

Версия с учётом регистра

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

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

Где text — искомый текст, а rng — диапазон ячеек, которые нужно подсчитать. Нет необходимости использовать групповые символы, так как HERE вернет число, если текст находится в любом месте ячейки.

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