СИМВОЛ – функция в Excel с примерами – Справочник функций Excel

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

Использование в функциях

Предположим, что стоит задача подсчета ячеек, содержащих все склонения слова молоток (например, молотку, молотком, молотка и пр.). Для решения этой задачи проще всего использовать критерии отбора с подстановочным знаком * (звездочка). Идея заключается в следующем: для условий отбора текстовых значений можно задать в качестве критерия лишь часть текстового значения (для нашего случая, это – молот) , а другую часть заменить подстановочными знаками. В нашем случае, критерий будет выглядеть так – молот*.

Предполагая, что текстовые значения (склонения слова молот ) находятся в диапазоне А2:А10 , запишем формулу =СЧЁТЕСЛИ(A2:A10;”молот*”)) . В результате будут подсчитаны все склонения слова молоток в диапазоне A2:A10 .

Нижеследующие функции позволяют использовать подстановочные знаки:

  • СЧЁТЕСЛИ() (см. статью Подсчет текстовых значений с единственным критерием ) и СЧЁТЕСЛИМН()
  • СУММЕСЛИ() и СУММЕСЛИМН()
  • СРЗНАЧЕСЛИ()
  • В таблице условий функций БСЧЁТ() (см. статью Подсчет значений с множественными критериями ), а также функций БСЧЁТА() , БИЗВЛЕЧЬ() , ДМАКС() , ДМИН() , БДСУММ() , ДСРЗНАЧ()
  • ПОИСК()
  • ВПР() и ГПР()
  • ПОИСКПОЗ()

Описание применения подстановочных знаков в вышеуказанных функциях описано соответствующих статьях .

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

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

Функция ВПР()

19-syntax-vlookup-formula.pngПредположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

19-vlookup-formula-dont-work.pngНо случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

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

Функция ИНДЕКС()

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

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; [номер_столбца]).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

19-index-formula.png

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; [тип_сопоставления]).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

19-match-formula-description.png

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

19-5-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D0%B0-%D0%B8%D0%BD%D0%B4%D0%B5%D0%BA%D1%81-%D0%B8-%D0%BF%D0%BE%D0%B8%D1%81%D0%BA%D0%BF%D0%BE%D0%B7.png

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

СУММ

Формула:

=СУММ(число1; число2)

=СУММ(адрес_ячейки1; адрес_ячейки2)

=СУММ(адрес_ячейки1:адрес_ячейки6)

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки.

С помощью формулы вы можете:

  • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
  • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
  • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

СЧЁТ

Формула: =СЧЁТ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

Примеры использования подстановочных символов в Эксель

Звездочка (*)

Поможет в создании конструкции «содержит».

Пример использования подстановочного знака * (звездочка) в Excel

Вопросительный знак (?)

Поможет найти слово или словосочетание, даже если есть неизвестные символы.

Пример использования подстановочного знака ? (вопросительный знак) в Excel

Знаки подстановки для оператора Like

Знаки в шаблоне Совпадения в сравниваемой строке
? Знак вопроса обозначает любой отдельный символ, в том числе любую цифру
* Звездочка обозначает любое количество любых символов, в том числе ноль символов
# Решетка обозначает любую цифру (0-9)
[СписокСимволов] Любой отдельный знак, входящий в СписокСимволов
[!СписокСимволов] Любой отдельный знак, не входящий в СписокСимволов

Знаки ?, *, # и [ (открывающая скобка) обозначают сами себя внутри квадратных скобок, а ] (закрывающая скобка) — вне квадратных скобок. Более подробно о знаках подстановки для оператора Like можно прочитать здесь.

Как написать

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

Обозначение <>

Наиболее простой способ поставит не равно — использовать два знака: меньше (<) и больше (>). Они набираются путем перехода на английский шрифт с последующим нажатием Shift и зажатием букв «Б» и «Ю». Отметим, что знак не равно в Excel в формуле должен иметь именно такой вид <>, ведь программа обычные буквы не распознает.

Существует еще один способ, как найти этот знак:

  • Выделите ячейку, куда необходимо внедрить этот символ.
  • Зайдите во вкладку «Вставка».
  • Найдите на ленте в блоке инструментов «Символы» клавишу с названием «Символ».

  • В параметр «Набор» проверьте, чтобы был установлен пункт «Основная латиница».
  • Осмотрите в центральной части окна доступные элементы. В их числе можно найти множество обозначений, которые отсутствуют на клавиатуре компьютера / ноутбука.
  • Для записи условия не равно в Экселе необходимо сначала нажать на <  и «Вставить, а потом на > и «Вставить».
  • Закройте окно со вставкой путем нажатия на крестик на белом фоне.

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

Обозначение ≠

Рассматривая варианты, как написать знак не равно в Экселе в формуле, можно использовать основной вариант обозначения. Он обозначается как перечеркнутый символ равно ≠. Для его установки сделайте следующие шаги:

  1. Жмите на ячейку куда надо записать условие Excel не равно.
  2. Перейдите в раздел «Вставка».
  3. Кликните на уже привычную клавишу «Символ».
  4. В появившемся окне в параметре «Набор» укажите «Математические операторы».
  5. Найдите ≠ и жмите по нему.
  6. Кликните на кнопку «Вставить».
  7. Закройте окно этим же способом, что и в прошлый раз, путем нажатия на крестик.

Существует еще один способ, как написать не равно в Excel. Для этих целей можно использовать так называемый Alt-код. Знание таких обозначений позволяет ввести любой символ с помощью клавиатуры. Так, если речь идет ≠, для его установки с клавиатуры можно набрать комбинацию Alt+8800. При этом набора Alt-кодов срабатывает только при использовании блока с цифровыми кнопками. Для активации необходимо включить на клавиатуре опцию NumLock.

Какой метод лучше

Зная, как поставить не равно в Excel, нужно определиться, какой из предложенных вариантов лучше и удобней. Если исходить из комментариев пользователей, более продвинутый вариант — применение клавиатуры. Этот способ одновременно быстрый и позволяет с удобством решить вопрос. В случае установки знака не равно в Excel через вкладку «Символы» приходится тратить больше времени. Такой вариант чаще всего применяется для однократной вставки и в редких случаях.

Если говорить о визуальном оформлении документа с применением не равно, иных способов ввода ≠ не предусмотрено (только с помощью клавиатуры или библиотеки). Применение Alt-кодов более удобно, ведь позволяет ускорить процесс набора без вызова дополнительных параметров Excel. Сложнее обстоит ситуация, когда цифрового блока вообще нет.

Использование символов подстановки в Excel

Ниже приведены некоторые примеры использования подстановочных знаков Excel:

  • ? соответствует любому одному символу, такому как «A», «z», «1»,
  • ?? соответствует любым двум символам, таким как «A9», «dL», «1m»,
  • ??? соответствует любым трем символам, таким как «a0l», «mE4», «c: 4»,
  • * соответствует любой символьной строке, включая пустую строку, например , abcd, a3 $ B,
  • * abc соответствует любой строке, заканчивающейся на abc, такой как «1abc», «Mabc»,
  • abc * соответствует любой строке, начинающейся с abc, например abc1, abcM,
  • ? * соответствует любой строке с одним или несколькими символами, например, «1», «Abc»,
  • ??, ??? соответствует любой строке с 2 символами, одной запятой и 3 символами после, например «12,35 $» или «ab, cde»,
  • * ~? соответствует любому вопросу, оканчивающемуся на вопросительный знак, например «Действительно?», «Как дела?»,
  • * abc * будет соответствовать любой строке, содержащей данную строку abc, например, abc, 1abcm.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

Формула:

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

ЕСЛИ

Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”)

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...