СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»&

=СЧЕТЕСЛИ(диапазон; значение)>0

Связанные формулы

Диапазон содержит определенный текст
Спектр содержит одну из многих значений
Спектр содержит один из множества подстрок
Ячейка содержит определенный текст
Ячейка содержит одну из многих вещей

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

В показанном примере, формула в Д5:

=СЧЕТЕСЛИ(гсч,B5)>0

Функция СЧЕТЕСЛИ подсчитывает Количество ячеек, которые удовлетворяют заданным условиям возвращает число найденных вхождений. Если нет клетки, отвечающие критериям, СЧЕТЕСЛИ возвращает ноль. Любое положительное число означает значение было найдено. Это означает, что вы можете использовать оператор «больше» ( > ):

=СЧЕТЕСЛИ(диапазон; значения)>0

Вы также можете обернуть формулы внутри оператора ЕСЛИ, чтобы получить конкретный результат. Например, чтобы получить «да» или «нет», используйте:

=Если(СЧЕТЕСЛИ(диапазон,значение)>0,»Да»,»Нет»)

Это работает, поскольку если считает любое число > 0 как истина, когда она появляется как логический аргумент теста.

Частичное совпадение

Если вы хотите проверить диапазон, чтобы увидеть, если он содержит определенный текст (подстрока или частичный текст), вы можете добавить подстановочные знаки в Формуле. Например, если у вас есть значение поискать в ячейке B5, а вы хотите, чтобы проверить диапазон $F$5:$F$11, вы можете настроить СЧЕТЕСЛИ искать это значение в любом месте в клетке, объединяя звездочки:

=ЕСЛИ(СЧЁТЕСЛИ($F$5:$F$11;»*»&B5&»*»);»ок»;»ПРОВЕРИТЬ»)

Звездочка (*) является подстановочным знаком для одного или нескольких персонажей. Объединяя звездочки перед и после значения в B5 формула будет подсчитывать подстроки в B5 везде, где он появляется в каждой ячейке диапазона.

Файл СЧЕТЕСЛИ поиск в диапазоне конкретных значений и совпадений + ЕСЛИ + «*»& xlsx

Построить гиперссылку с ВПР

=Гиперссылка(ВПР(название,Таблица,столбец 0),название)

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

В показанном примере, формула в F5:

=Гиперссылка(ВПР(E5,диапазон для поиска,2,0),E5)

Как эта формула работает

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

Работая изнутри, ВПР ищет и возвращает значение по ссылке из столбца 2 именованного диапазона «таблица ссылок» (В5:В8). Искомое значение исходит из столбца E, и ВПР настроен на Точное «0» совпадение.

Результат поступает в гиперссылку как ссылка, и текст в столбце e используется для наименование ссылки.

Гиперссылка возвращает рабочую ссылку.

Файл Построить гиперссылку с ВПР xlsx

Общее число столбцов и строк в диапазоне Excel

=ЧИСЛСТОЛБ(диапазон) Колонка

=ЧСТРОК(диапазон) Строка

Связанные формулы

Всего строк в диапазоне
Общее количество ячеек в диапазоне

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

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

=ЧИСЛСТОЛБ(D5:E12)

Вот как эта формула работает:

ЧИСЛСТОЛБ подсчитывает количество столбцов и возвращает результат в виде числа.

Файл Общее число столбцов и строк в диапазоне xls

Транспонировать таблицу без нулей Excel

=ТРАНСП(ЕСЛИ(диапазон=»»;»»;диапазон))

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

В примере показано, формула массива в F4 является:

=ТРАНСП(ЕСЛИ(C4:D11=»»;»»;C4:D11))

Обратите внимание: это формула массива, который должен быть введен с помощью Control + Shift + ввод через весь диапазон F4:M5.

Как эта формула работает

ТРАНСП функция автоматически переносит значения в горизонтальной ориентации на вертикальную ориентацию и наоборот.

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

Да, еще раз, перед тем как воспользоваться «Control + Shift + ввод» перейдите в ячейку и помощью Shift выделите диапазон от первой ячейки вниз или в право, после этого нажимайте Control + Shift + ввод.

Файл Транспонировать таблицу без нулей xls

Подсчет ячеек, содержащих ошибки Excel

=СУММПРОИЗВ(—ЕОШ(диапазон))

Если вам нужно подсчитать количество ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ вместе с функцией СУММПРОИЗВ. В общем виде формулу представляет собой диапазон ячеек, в котором вы подсчитываете ошибки.

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

=СУММПРОИЗВ(—ЕОШ(D5:D11))

Как работает формула

Функция СУММПРОИЗВ принимает один или более массивов и возвращает сумму произведений соответствующих чисел. Если подается только один массив, он просто суммирует элементы массива.

Функция ЕОШ вычисляется для каждой ячейки есть ли ошибка.  Результатом является массив значений Истина или Ложь:

{ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}

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

{1;1;1;0;0;0;0}

Тогда функция sumproduct суммы элементов в массиве и возвращает общее, что в примере число 2.

Примечание: ЕОШ подсчитывает все ошибки, кроме #Н/д. если вы хотите тоже посчитайте #н/д, использовать функцию ЕОШИБКА вместо ЕОШ.

Вы также можете использовать функцию СУММ для подсчета ошибок. Структура формула такая же, но ее необходимо вводить как формулу массива (нажмите Control + Shift + ввод, а не просто ввод). После ввода формулы будет выглядеть следующим образом:

{=Сумм(—ЕОШ(D5:D11))}

Файл Подсчет ячеек, содержащих ошибки xls