Дополнительные параметры поиска слов и фраз
Поиск в массиве при помощи ИНДЕКС ПОИСКПОЗ
Сравнение нескольких ячеек в табличном редакторе
Теперь поговорим детально о том, как произвести сравнение текстовой информации более, чем в двух ячейках. Эти методы практически идентичны тем, что были рассмотрены выше.
Применение специальной формулы без учета регистра для нескольких ячеек
Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:
- Первый вариант формулы выглядит так: =И(А1=В1; А1=С1).
- Второй вариант формулы выглядит так: =ЕСЛИ(И(А1=В1; А1=С1); “Совпадает”; “НЕ совпадает”).
Функция «И» реализует возврат значения ИСТИНА в том случае, если все поля таблички имеют совпадающие значения. Возврат значения ЛОЖЬ происходит, если какое-либо из значений не совпадает. Оператор «ЕСЛИ» позволяет отобразить на экране те показатели, которые ввел пользователь в формулу.
На нижеприведенной картинке прекрасно видно, как две рассмотренные формулы справляются с процедурой сравнения не только текстовой информации, но и с числовыми показателями, а также с данными формата «Дата».
3
Применение специальной формулы с учетом регистра для нескольких ячеек
Здесь, как и в ранее рассмотренных методах, применяется две вариации формулы:
- Первый вариант формулы выглядит так: =И(СОВПАД(А1;В1); СОВПАД(А1; С2)).
- Второй вариант формулы выглядит так: =ЕСЛИ(И(СОВПАД(А1;В1);СОВПАД(А1; С1));”Точное совпадение”; “НЕ совпадает”).
Как и в предыдущем примере, функция «И» реализует возврат значения ИСТИНА в том случае, если все поля таблички имеют совпадающие значения. Возврат значения ЛОЖЬ происходит, если какое-либо из значений не совпадает. Оператор «ЕСЛИ» позволяет отобразить на экране те показатели, которые ввел пользователь в формулу. Результат преобразований выглядит так:
4
Если пользователю необходимо, чтобы формула вывела наличие соответствий хотя бы 2-х показателей, то ему необходимо использовать следующую формулу: =ЕСЛИ(ИЛИ(СОВПАД(А1;В1);СОВПАД(А1; С1);СОВПАД(В1;С1)); “Не менее двух точных соответствий”; “Соответствий нет”). Иными словами, в этом случае наличие хотя бы 2-х соответствий в 3-х случаях, выведет положительное значение. Результат преобразований выглядит так:
5
Если же пользователю необходимо произвести сравнение в табличке, в которой присутствует огромное количество ячеек, то вышеописанная формула будет не удобной в использовании, так как она станет слишком длинной. Здесь целесообразнее использовать математический оператор «СЧЕТЕСЛИ». Подсчитать количество соответствий в большом диапазоне ячеек можно при помощи следующей формулы: {=СЧЕТЕСЛИ(А2:D2;А2:D2)}. Результат преобразований выглядит так:
6
Примеры использования функции ПОИСКПОЗ в Excel
Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).
Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» — «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.
Например, массив <«виноград»;»яблоко»;»груша»;»слива»>содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;<«виноград»;»яблоко»;»груша»;»слива»>;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения. Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения “Воронеж” в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), MVP корпорации Майкрософт.
Поиск и подстановка по нескольким условиям
Формула СУММПРОИЗВ для поиска по строке и столбцу
Поиск точного совпадения с помощью ВПР
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив -или- Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай -или- Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Формулы массивов и функции Excel
Вы сможете употреблять почти все интегрированные функции Excel, такие как SUM , AVERAGE и COUNT , в формуле массива. Есть также несколько функций, таковых как TRANSPOSE , которые постоянно должны быть формулой массива, чтоб работать верно. (Функция TRANSPOSE копирует данные из строчки в столбец либо напротив.)
Вы также сможете расширить полезность почти всех функций, таковых как INDEX и MATCH либо MAX и IF , используя их вкупе в формуле массива.
Сделать ординарную формулу массива из одной ячейки
Формулы массива с одной ячейкой обычно поначалу делают вычисление с несколькими ячейками, а потом употребляют функцию, такую как AVERAGE либо SUM, чтоб соединить выходные данные массива в один итог.
Игнорировать значения ошибок при поиске данных
Эта формула массива употребляет функции AVERAGE , IF и ISNUMBER, чтоб отыскать среднее значение для имеющихся данных , игнорируя значения ошибок, такие как # DIV / 0! и #NAME ?
Подсчет ячеек данных
Используйте SUM и ПЧ функции в формуле массива для подсчета клеток данных , которые соответствуют одному из нескольких критерий; Этот способ различается от использования функции COUNTIFS в Excel , которая просит, чтоб все установленные условия были выполнены до того, как он подсчитает ячейку.
Отыскать наибольшее положительное либо отрицательное число
Этот пример соединяет в для себя MAX функции и ПЧ функции в формуле массива , который будет отыскать наибольшее либо наибольшее значение для спектра данных , когда она отвечает определенным аспектам. Тут наибольшее значение представляет самое неспешное время.
Найдите меньшее положительное либо отрицательное число
Как и в примере выше, вы сможете соединить функции MIN и IF в формуле массива, чтоб отыскать меньшее либо малое значение для спектра данных, когда оно соответствует определенным аспектам.
Отыскать среднее либо среднее значение
Функция MEDIAN в Excel находит среднее значение для перечня данных. Сочитая его с функцией IF в формуле массива, вы сможете отыскать среднее значение для различных групп связанных данных.
Эта формула массива включает в себя вложение функций MATCH и INDEX для поиска определенной инфы в базе данных.
Создать левую формулу поиска
ВПР функция обычно лишь поиск данных , расположенных в колонках справа, но, сочитая его с ВЫБРАТЬ функции , вы сможете сделать левую формулу подстановки , которая будет находить столбцы данных слева от Lookup_Value аргумента.
При вводе массива в качестве аргумента для функции, которая обычно содержит лишь одно значение либо ссылку на ячейку, вы сможете вводить фигурные скобки впрямую, а не употреблять комбинацию кнопок Ctrl + Shift + Enter , как в приведенном выше примере.
Пример использования поиска решений
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:
Вкратце опишу что делают строчки данного кода: 1-я строка: Выбираем в книге лист «Данные»; 2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell; 3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What — Строка с текстом, который ищем или любой другой тип данных Excel
After — Ячейка, после которой начать поиск
Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее
Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).
Заполняем аргументы функции
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
- одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
- двумерные (НЕСКОЛЬКО строк и столбцов, матрица).
Одномерные массивы бывают:
- горизонтальными (данные – в строке);
- вертикальными (данные – в столбце).
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
- подсчитать количество знаков в определенном диапазоне;
- суммировать только те числа, которые соответствуют заданному условию;
- суммировать все n-ные значения в определенном диапазоне.
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Классика жанра – товарный чек
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
- выделяем ячейку С7
- вводим с клавиатуры =СУММ(
- выделяем диапазон B2:B5
- вводим знак умножения (звездочка)
- выделяем диапазон C2:C5 и закрываем скобку функции СУММ – в итоге должно получиться так:
- чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter
Вуаля!
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter
Разрешите Вас… транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
- Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
- вводим функцию транспонирования =ТРАНСП(
- в качестве аргумента функции выделяем наш массив ячеек A1:B8
жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:
Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах
Как в экселе найти нужное слово по ячейкам
Примеры поиска функцией Find
Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле
Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)
Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)
Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.
Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом
Свойство FindFormat можно задавать разными способами, например, так:
Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т.ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.
При поиске можно так же использовать шаблоны, чтобы найти текст по маске, следующий пример это демонстрирует.
Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.
Для поиска функцией Find по маске (шаблону) можно применять символы: * — для обозначения любого количества любых символов; ? — для обозначения одного любого символа;
— для обозначения символов *, ? и
. (т.е. чтобы искать в тексте вопросительный знак, нужно написать
?, чтобы искать именно звездочку (*), нужно написать
* и наконец, чтобы найти в тексте тильду, необходимо написать
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
- Тип данных Date в VBA представляется в виде #//#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
- В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
Пример 8: Найти 1 марта 2018 г.
Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
Поиск ТЕКСТовых значений в Excel. Бесплатные примеры и статьи.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Есть ли слово в списке MS EXCEL
Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.
Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск
Найдем текстовые значения, удовлетворяющие заданному пользователем критерию. Поиск будем осуществлять в диапазоне с повторяющимися значениями. При наличии повторов, можно ожидать, что критерию будет соответствовать несколько значений. Для их вывода в отдельный диапазон удобно использовать формулы массива.
Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца
Для нахождения позиции значения в столбце, с последующим выводом соответствующего значения из соседнего столбца в EXCEL, существует специальная функция ВПР() , но для ее решения можно использовать также и другие функции. Рассмотрим задачу в случае текстовых значений.
Поиск позиции ТЕКСТа с учетом РЕгиСТра в MS EXCEL и выводом значения из соседнего столбца
Здесь развиваются идеи статьи Поиск позиции ТЕКСТового значения с выводом соответствующего значения из соседнего столбца. Для нахождения позиции значения с учетом РЕгиСТра, с последующим выводом соответствующего значения из соседнего столбца, функция ВПР() применена быть не может, т.к. она не чувствительна к регистру. Задача может быть решена с помощи функций БИЗВЛЕЧЬ() , НАЙТИ() или СОВПАД() .
Поиск в MS EXCEL текстового значения первого или последнего по алфавиту
Найдем текстовое значение, которое при сортировке диапазона по возрастанию будет выведено первым, т.е. первое по алфавиту. Также найдем последнее значение по алфавиту.
Разнесение в MS EXCEL ТЕКСТовых значений и ЧИСЕЛ по разным спискам
Если исходный список, содержит и текст и числа, то с помощью формул массива можно в один список отобрать все текстовые значения, а в другой – числовые.
Поиск в MS EXCEL третьего (n-го) повтора в списках с ТЕКСТом
Рассмотрим поиск текстовых значений в списке с повторами. Задав в качестве критерия для поиска нужное текстовое значение и номер его повтора в списке, найдем номер строки, в которой содержится этот повтор, а также выделим его и другие повторы с помощью Условного форматирования.
О классификации задач Поиска в EXCEL
В этой статье изложены попытки классификации задач по поиску значений в MS EXCEL. Статья может быть интересна для продвинутых пользователей EXCEL.
Поиск нескольких значений с максимальной длиной строки в MS EXCEL
Найдем в исходном списке текстовые значения, которые имеют максимальную длину строки, и выведем их в отдельный диапазон.
Как узнать, что ячейка Эксель содержит латиницу
Точное или приближенное совпадение в функции ВПР
Как найти значение в Excel
ПОИСК в эксель
Все так же наша фраза «Здравствуйте я ваша тётя!» на своем месте в ячейке B3, установим курсор в ячейку B7 и нажмем на иконку Fx в строке формул.
Откроется мастер формул, в категории Текстовые выберем функцию ПОИСК.
В поле Искомый_текст введем слова «ваша тётя», Текст_для_поиска B3, Нач_позиция 1. Результатом станет число 16, оно означает, что наш текст находится на 16 символов правее начала строки.
Функцию ПОИСК чаще всего не используют самостоятельно, а в сочетании с другими, например с функцией ЗАМЕНИТЬ.
В этом видео подробно показано применение функции ПОИСК в excel:
Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!
Новые статьи
- YouTube все — ютубКапут — (видео) — 20/03/2022 11:22
- Обращение к макросу через кнопку, как получить и передать данные на другой лист в книге excel — 26/09/2021 12:53
- Опять разбиваем текст в ячейке — 12/09/2021 08:05
- Как разбить ячейку в эксель, как сделать нормальную таблицу в Excel — 12/06/2021 18:30
- Исправляем ошибку VBA № 5854 слишком длинный строковый параметр в шаблоне word из таблицы excel 255 символов — 21/02/2021 08:54
- База данных из JavaScript для веб страницы из Excel на VBA модуле — 30/11/2019 09:15
- Листы в Excel из списка по шаблону — 02/06/2019 15:42
- Печать верхней строки на каждой странице в Excel — 04/06/2017 17:05
- Создание диаграммы, гистограммы в Excel — 04/06/2017 15:12
- Функция СИМВОЛ в Excel или как верстать HTML в Excel — 03/06/2017 17:32
- Функция ЕСЛИОШИБКА в excel, пример использования — 20/05/2017 11:39
- Как использовать функцию МИН в excel — 20/05/2017 11:36
- Как использовать функцию МАКС в excel — 20/05/2017 11:33
- Как использовать функцию ПРОПИСН в excel — 20/05/2017 11:31
- Как использовать функцию СТРОЧН в excel — 20/05/2017 11:29
- Как использовать функцию СЧЕТЕСЛИ в excel — 20/05/2017 11:26
- Как использовать функцию Функция СЧЁТ в excel — 20/05/2017 11:09
Предыдущие статьи
- Как использовать функцию СЦЕПИТЬ в эксель — 10/03/2017 20:41
- Как использовать функцию ПРАВСИМВ в excel — 10/03/2017 20:35
- Как использовать функцию ЛЕВСИМВ в excel — 06/03/2017 16:04
- Как использовать функцию ЗАМЕНИТЬ в excel — 28/02/2017 18:44
- Как использовать функцию ДЛСТР в эксель — 25/02/2017 15:07
- Как использовать функцию ЕСЛИ в эксель — 24/02/2017 19:37
- Как использовать функцию СУММЕСЛИ в Excel — 22/02/2017 19:08
- Как использовать функцию СУММ в эксель — 20/02/2017 19:54
- Печать документа в Excel и настройка печати — 16/02/2017 19:15
- Условное форматирование в ячейках таблицы Excel — 16/06/2016 17:38
- Объединить строку и дату в Excel в одной ячейке — 16/06/2016 17:33
- Горячие клавиши в Microsoft Office Excel — 04/06/2016 14:57
- Как использовать эксель в качестве фотошопа — 04/06/2016 09:01
- Как разделить текст по столбцам, как разделить ячейки в Excel — 14/04/2016 16:19
- Как применить функцию ВПР в Excel для поиска данных на листе — 08/01/2016 23:40
- Как создать таблицу в Excel, оформление таблицы — 06/01/2016 20:29
- Работа в эксель, как начать пользоваться Excel — 26/12/2015 15:48
Как посчитать количество пустых и непустых ячеек?
Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.
Непустые.
В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:
СЧЕТЕСЛИ(диапазон;”*”)
Но дело в том, что приведенное выше выражение подсчитывает только клетки, содержащие любые текстовые значения. А это означает, что те из них, что включают даты и числа, будут обрабатываться как пустые (игнорироваться) и не войдут в общий итог!
Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:
СЧЕТЕСЛИ(диапазон;”<>” & “”)
Это корректно работает со всеми типами значений – текстом, датами и числами – как вы можете видеть на рисунке ниже.
Также непустые ячейки в диапазоне можно подсчитать:
=СЧЁТЗ(E2:E22).
Пустые.
Если вы хотите сосчитать пустые позиции в определенном диапазоне, вы должны придерживаться того же подхода – используйте в условиях символ подстановки для текстовых значений и параметр “” для подсчета всех пустых ячеек.
Считаем клетки, не содержащие текст:
СЧЕТЕСЛИ( диапазон; “<>” & “*”)
Поскольку звездочка (*) соответствует любой последовательности текстовых символов, в расчет принимаются клетки, не равные *, т.е. не содержащие текста в указанном диапазоне.
Для подсчета пустых клеток (все типы значений):
=СЧЁТЕСЛИ(E2:E22;””)
Конечно, для таких случаев есть и специальная функция
=СЧИТАТЬПУСТОТЫ(E2:E22)
Но не все знают о ее существовании. Но вы теперь в курсе …
Особенности использования функции ПОИСКПОЗ в Excel
Функция ПОИСКПОЗ в Excel
При этом регистр не учитывается.=ПОИСКПОЗ(«е»; ; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.=ПОИСКПОЗ(«е»; ; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.=ПОИСКПОЗ(«в»; ; -1) – результат 3.=ПОИСКПОЗ(«д»; ; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.
Для текстовых значений функция допускает использование подстановочных символов «*» и «?».
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.
У Вас недостаточно прав для комментирования.