Таблица символов в эксель

Каждый символ, вводимый вами и отображаемый на экране, имеет свой собственный числовой код. Например, код для прописной буквы А — это 65, а код для амперсанда (&) — 38. Эти коды присутствуют не только для алфавитных символов, вводимых вами с клавиатуры, но и для специальных символов, которые вы можете вывести на экран, используя их коды. Список всех символов и их кодов называется таблицей кодов ASCII.

Например, ASCII-код для символа © равен 169. Для вывода данного символа необходимо ввести на клавиатуре Alt+0169, используя цифровую клавиатуру для ввода цифр. Таблица ASCII содержит коды от 1 до 255, при этом первые 31 цифра используются для непечатаемых символов, таких как возврат каретки и перевод строки.

Сочетания клавиш для галочки

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

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

После форматирования ячейки просто удерживайте клавишу Shift и нажмите P. Сочетание клавиш здесь — Shift + P.

Это вставит в ячейку символ галочки. Вы также можете вставить это в конец любой строки текста в ячейке.

Другой символ — это противоположность галочки, «x», где вы можете обозначить отрицательный ответ. Удерживая нажатой клавишу Shift, нажмите O. Сочетание клавиш здесь — Shift + O.

В ячейку будет вставлен символ «x».

Для быстрого списка, где вам нужно добавить положительный или отрицательный ответ, эти две галочки Excel помогут.

К другим кодам клавиатуры Wingdings2 относятся следующие.

  • Shift + R: поставить галочку в квадрате.
  • Shift + Q, Shift + S, Shift + T: различные стили «x» внутри поля.
  • Shift + V, Shift + U: различные стили символа «x» внутри круга.

Если вам нужно больше разнообразия, отформатируйте ячейки шрифтом Wingdings.

Это позволяет использовать четыре возможных сочетания клавиш с галочкой.

  • ALT 0252: Стандартная галочка
  • ALT 0254: галочка внутри поля
  • ALT 0251: Стандартный знак «x»
  • ALT 0253: «x» внутри коробки

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

Как видите, существует множество опций и стилей, которые можно использовать для использования флажков в Excel.

Предпочитаете вместо этого использовать функцию СИМВОЛ? Не проблема. Просто выберите ячейку, в которой вы хотите разместить символ, и введите одну из следующих функций.

  • = СИМВОЛ (252): стандартный флажок
  • = СИМВОЛ (254): галочка внутри поля
  • = СИМВОЛ (251): стандартный знак «x»
  • = СИМВОЛ (253): «x» внутри рамки

Нажмите Enter, и в этой ячейке появится галочка.

Функция КОДСИМВ()

Данная функция является противоположностью функции СИМВОЛ(число) . При вводе в нее текстового символа она возвращает код ASCII: КОДСИМВ<текст) , где текст — символ или текстовая строка. В случае ввода строки функция вернет ASCII-код первого символа. Например, следующие формулы вернут 193 как код символа Б: =КОДСИМВ("Б") или =КОДСИМВ("Баланс")

В этой статье описаны синтаксис формулы и использование функции СИМВОЛ в Microsoft Excel.

Извлечение части строки

Функции, обсуждаемые в этом разделе, полезны при базовом извлечении из строки. Для чего-то более сложного можете посмотреть раздел, как легко извлечь любую строку без использования VBA InStr.

Функция Параметры Описание Пример
Left строка, длина Вернуть
символы с
левой стороны
Left(«Иван
Петров»,4)
Right строка, длина Вернуть
символы с
правой
стороны
Right(«Иван
Петров»,5)
Mid строка, начало, длина Вернуть
символы из
середины
Mid(«Иван
Петров»,3,2)

Функции Left, Right и Mid используются для извлечения частей строки. Это очень простые в использовании функции. Left читает символы слева, Right справа и Mid от указанной вами начальной точки.

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

В следующем примере вы увидите, что строка Fullname не была изменена после использования функции Left.

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

Оператор ПРАВСИМВ

Функция ПРАВСИМВ извлекает из указанного элемента на листе то количество символов справа, которое укажет сам пользователь. Выводит конечный результат в ту ячейку, где располагается она сама. Данная функция относится к текстовой категории операторов Excel. Её синтаксис следующий:

Как видим, функция имеет всего два аргумента. Первый из них «Текст» может принимать вид, как собственно текстового выражения, так и ссылки на элемент листа, в котором оно располагается. В первом случае оператор будет извлекать указанное количество символов именно из текстового выражения, заданного в виде аргумента. Во втором случае функция будет «отщипывать» символы от текста, содержащегося в указанной ячейке.

Второй аргумент — «Число знаков» — представляет собой числовое значение, указывающее, какое именно количество символов в текстовом выражении, отсчитывая справа, нужно отобразить в целевой ячейке. Этот аргумент не является обязательным. Если его опустить, то считается, что он равен единице, то есть, в ячейку выводится только один самый крайний правый символ указанного элемента.

Пример применения

Теперь давайте рассмотрим применение функции ПРАВСИМВ на конкретном примере.

Для примера возьмем список сотрудников предприятия. В первой колонке этой таблицы находятся фамилии работников вместе с номерами телефонов. Нам нужно эти номера с помощью функции ПРАВСИМВ вынести в отдельный столбец, который так и называется «Номер телефона».

    Выделяем первую пустую ячейку столбца «Номер телефона». Щелкаем по пиктограмме «Вставить функцию», которая размещена слева от строки формул.

Происходит активация окна Мастера функций. Переходим в категорию «Текстовые». Из представленного списка наименований выделяем название «ПРАВСИМВ». Производим щелчок по кнопке «OK».

Открывается окно аргументов оператора ПРАВСИМВ. Оно содержит в себе два поля, которые соответствуют аргументам указанной функции. В поле «Текст» нужно указать ссылку на первую ячейку столбца «Имя», в котором содержится фамилия работника и номер телефона. Адрес можно указать вручную, но мы сделаем по-другому. Устанавливаем курсор в поле «Текст», а затем щелкаем левой кнопкой мыши по той ячейке, координаты которой следует внести. После этого адрес отображается в окошке аргументов.

В поле «Число знаков» вводим с клавиатуры цифру «5». Именно из пяти знаков состоит номер телефона каждого работника. К тому же, все номера телефонов расположены в конце ячеек. Поэтому, чтобы их вывести отдельно, нам нужно извлечь из этих ячеек именно по пять символов справа.

После того, как вышеуказанные данные введены, щелкаем по кнопке «OK».

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

Теперь весь столбец «Номер телефона» заполнен соответствующими значениями из колонки «Имя».

Но, если мы попытаемся убрать номера телефонов из столбца «Имя», то они начнут исчезать и из колонки «Номер телефона». Это объясняется тем, что оба эти столбца связаны формулой. Для того, чтобы удалить эту связь, выделяем все содержимое столбца «Номер телефона». Затем кликаем по пиктограмме «Копировать», которая находится на ленте во вкладке «Главная» в группе инструментов «Буфер обмена». Можно также набрать сочетание клавиш Ctrl+C.

Далее, не снимая выделения с вышеуказанной колонки, кликаем по ней правой кнопкой мыши. В контекстном меню в группе «Параметры вставки» выбираем позицию «Значения».

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА

Учет РЕгиСТра приводит к необходимости создания более сложных формул. Чаще всего используются формулы на основе функций НАЙТИ() и СОВПАД() учитывающих регистр.

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

Просуммировать значения, если соответствующие ячейки:

Критерий

Формула

Результат

Примечание

в точности равны Яблоки с учетом регистра

содержат значение Яблоки в любом месте текстовой строки с учетом регистра

= СУММ(ЕСЛИ( СОВПАД("Яблоки";A3:A13);1;0) *B3:B13)

В предыдущей статье мы рассмотрели синтаксис функции СУММЕСЛИ в Excel, теперь давайте закрепим знания на практике при помощи ряда примеров формулы СУММЕСЛИ:

СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)

Давайте рассмотрим несколько примеров формул СУММЕСЛИ, которые вы можете использовать для суммирования значений для условий больше чем, меньше чем или равно заданному значению.

Примечание . Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки ("").

Читайте также:  Расчет средневзвешенной процентной ставки портфеля в Excel

Пример формулы СУММЕСЛИ

Сумма, если больше

Суммирует значения больше 5 в ячейках A2:A10.

Сумма, если меньше

Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1.

Сумма если больше или равно

Суммирует значения, которые больше или равны 5 в диапазоне A2:A10.

Сумма если меньше или равно

Обратите внимание , что вам понадобятся разные формулы СУММЕСЛИ для точного и частичного совпадения, как показано в таблице ниже.

Пример формулы СУММЕСЛИ

Сумма, если равно

=СУММЕСЛИ(A2:A8; "бананы"; C2:C8)

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться.

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования.

Сумма, если не равно

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования.

Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются.

Для получения дополнительной информации о частичном совпадении см. пункт СУММЕСЛИ примеры формул с подстановочными знаками.

А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:

=СУММЕСЛИ(A2:A8; "<>Банан Дамский пальчик"; C2:C8)

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с проверкой на неравенство

Примечание . Как и большинство других функций Excel, СУММЕСЛИ нечувствительна к регистру, что означает, что «<> бананы», «<> Бананы» и «<> БАНАНЫ» будут давать точно такой же результат.

СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки

Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1

Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки (""), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.

Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:

Формула 1: =СУММЕСЛИ(A2:A8; "=" & F1; C2:C8)

Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)

СУММЕСЛИ примеры формул с подстановочными знаками

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

Доступны следующие подстановочные знаки:

Звездочка (*) — представляет любое количество символов

Знак вопроса (?) — представляет один символ в определенном месте

Пример 1. Суммирование значений, основанные на частичном совпадении

Предположим, вы хотите суммировать количество, относящиеся ко всем видам бананов. Следующие формулы СУММЕСЛИ будут очень эффективны в таких случаях:

=СУММЕСЛИ(A2:A8; "*бананы*";C2:C8) — критерий включает текст, заключенный в звездочки (*).

=СУММЕСЛИ(A2:A8; "*"&F1&"*"; C2:C8) — критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с подстановочными знаками для суммирования по частичному совпадению

Если вы хотите считать только те ячейки, которые начинаются или заканчиваются определенным текстом, добавьте только один * до или после текста:

=СУММЕСЛИ(A2:A8; "бананы*"; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A начинается со слова «бананы».

=СУММЕСЛИ(A2:A8; "*бананы"; C2:C8) — значения суммы в C2:C8, если соответствующая ячейка в столбце A заканчивается словом «бананы».

Функция СУММЕСЛИ в Excel с примерами – Пример использования функции СУММЕСЛИ с текстовым условием

Пример 2. Суммирование по заданному количеству символов

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

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с условием суммирования, если длина текстовой строки в шесть букв

Пример 3. Сумма ячеек, соответствующих текстовым значениям

Если ваш рабочий лист содержит разные типы данных, и вы хотите только суммировать ячейки, соответствующие текстовым значениям, пригодится следующая формула СУММЕСЛИ:

=СУММЕСЛИ(A2:A8; "?*"; C2:C8) – суммирует значения из ячеек C2:C8, если соответствующая ячейка в столбце A содержит не менее 1 символа.

=СУММЕСЛИ(A2:A8; "*"; C2:C8) – учитывает пустые ячейки, содержащие строки нулевой длины, возвращаемые некоторыми другими формулами, например ="".

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

Пример 4. Использование * или ? как обычные символы

Если вы хотите использовать либо *, либо ? для обработки в функции СУММЕСЛИ как литерала, а не подстановочного знака, то используйте перед этим знаком тильду (

). Например, следующая формула СУММЕСЛИ просуммирует значения в ячейках C2:C8, если ячейка в столбце A в той же строке содержит знак вопроса:

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием значений, соответствующие знаку вопроса в другом столбце

СУММЕСЛИ в Excel примеры с датами

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

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

Пример формулы СУММЕСЛИ

Сумма по определенной дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B равна 29.10.2017.

Сумма, если дата больше либо равна заданной в формуле дате

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше или равна 29.10.2017.

Сумма, если дата больше даты, указанной в ячейке

Суммирует значения в ячейках C2:C9, если соответствующая дата в столбце B больше даты, указанной в ячейке F1.

Если вы хотите суммировать значения на основе текущей даты, вам необходимо использовать СУММЕСЛИ в сочетании с функцией СЕГОДНЯ(), как показано ниже:

Пример формулы СУММЕСЛИ

Суммирование значений, за текущую дату

=СУММЕСЛИ(B2:B9; СЕГОДНЯ (); C2:C9)

Суммирование значений, меньше текущей даты, то есть до сегодняшнего дня.

Суммирование значений за неделю от текущей даты. (т.е. сегодня + 7 дней).

Изображение ниже показывает, как вы можете использовать последнюю формулу, чтобы найти общее количество всех продуктов, которые будут отправлены через неделю:

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ с суммированием количества продуктов, которые будут отправлены через неделю

СУММЕСЛИ в заданном диапазоне дат

Если вам необходимо суммировать значения между двумя датами, то необходимо использовать комбинацию, а точнее разницу двух функций СУММЕСЛИ. В версиях старше Excel 2007 вы можете использовать функцию СУММЕСЛИМН, которая позволяет использовать несколько условий. Эту функцию мы рассмотрим в следующей статье. А так как данная статья посвящена функции СУММЕСЛИ, то приведем пример использования СУММЕСЛИ в диапазоне дат:

Эта формула суммирует значения в ячейках C2:C9, если дата в столбце B находится между 1 ноября 2017 года и 30 ноября 2017, включительно.

Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ дата в диапазоне

Эта формула может показаться немного сложной с первого взгляда, но при более близком рассмотрении это выглядит довольно просто. Первая функция СУММЕСЛИ объединяет все ячейки в C2:C9, где соответствующая ячейка в столбце B больше или равна дате начала (в данном примере 1 ноября). Затем вам просто нужно вычесть значения, которые попадают после даты окончания (30 ноября), с помощью второй функции СУММЕСЛИ.

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

Оформление и примеры использования

Алгоритм написания логических формул в Эксель следующий:

  1. Нужно выделить пустую ячейку, в которую будет записываться формула и выводиться результат действия.
    Выделение ячейкиВписывать можно и в строке формул, после выделения ячейки.Строка формул
  2. Перед формулами в программе ставится знак «=». Поставить его.
  3. Напечатать название оператора.
  4. После этого вписываются аргументы, если они есть. Начинается запись со знака «открывающаяся круглая скобка “(“».
  5. Аргументы вводятся последовательно через знак ”;”. Также, если после ввода названия функции нажать клавиши Ctrl + A, то откроется меню аргументов и вписать их можно здесь.Ввод аргументов
  6. В конце ставится символ «закрывающаяся круглая скобка “)”». Контролировать написание можно в строке формул.Строка формул
  7. После завершения нажать кнопку ENTER. Результат появится в ячейке.

ИСТИНА, ЛОЖЬ

Пример таблицы

В качестве примера приведем решение задачи с логическими операторами ИСТИНА и ЛОЖЬ. Они обычно не используются отдельно, а только в составе других операторов. Понять принцип работы можно на примере. В таблице телефонных номеров определяются платные и бесплатные вызовы.

Читайте также:  Функция ДАТАЗНАЧ для преобразования текста в дату в Excel

Результат формулы

После применения формулы «=ЕСЛИ(ЛЕВСИМВ(В3;4)=”8800”;ИСТИНА();ЛОЖЬ())», получается:

Сравнение происходит по первым четырем цифрам номера (оператор ЛЕВСИМВ(В3;4)). Если номер начинается с 8800, то звонок бесплатный, в противном случае — нет.

Отрицание — НЕ

Пример отрицания

Функция ссылается на ячейку или аргумент, где есть логический ответ, и меняет его на противоположный. Чаще всего применяется в составе формул. Пример:

Здесь оператор «=НЕ(F2)» инвертирует аргумент в столбце F.

Применение ЕСЛИ

«ЕСЛИ» всегда включает знаки сравнения и применяется в формулах с условием. Логика при его использовании такова:

  1. Задается вопрос, содержащий элемент сравнения.
  2. Далее вписываются 2 значения. Первая величина отобразится в ячейке в случае ответа «TRUE», вторая — если ответ «FALSE».
  3. Возможно создание многоуровневых вложений «ЕСЛИ».

таблица продаж

Например, работникам компании установлен минимальный порог продаж в размере 1 млн. рублей. При выполнении плана сотрудник получит зарплату в 20 тыс. рублей и надбавку в 5%. В случае, если продано на меньшую сумму, то премия не выплачивается. Результаты деятельности работников отображены в списке.

Постановка условия

Требуется разделить сотрудников в таблице по критерию исполнения плана. Для этого в программе создается таблица с дополнительными колонками E (Выполнение плана) и F (Зарплата за месяц).

Для выделения сотрудников применяется формула =ЕСЛИ(D4>=1000000;»Молодец!»;»План не выполнен:(«). Расшифровывается так:

Используется автозаполнение

  1. D4>=1000000. Создается запрос на проверку ячейки D. В случае если показатель в D4 больше или равен 1 млн., то ответ «TRUE». Если нет, то «FALSE».
  2. «Молодец!«. При положительном ответе в ячейке E4 появится надпись «Молодец!».
  3. «План не выполнен:(«. В противном случае отобразится «План не выполнен».
  4. Нажать Enter.
  5. Применив автозаполнение к E4, можно распространить действие формулы на все строки столбца E.

Результатом будет таблица, в которой указано, выполнил менеджер план или нет.

ЕСЛИМН или УСЛОВИЯ

В предыдущем примере было одно условие. Но в большинстве случаев при составлении отчетов учитывается много факторов. Приходится составлять многоуровневые вложенные «ЕСЛИ».

Ввод формулы

Например, если требуется разделить начисление премии в зависимости от процента продаж. При выручке менее 90% от плана, дополнительное вознаграждение не выплачивается. 90-95% — премия 10%, более 95% — 20%, продажи сверх плана награждаются премией в 30%. С оператором «ЕСЛИ» формула будет выглядеть так: «=ЕСЛИ(В2 0,9;0;ЕСЛИ(В2 0,95;0,1; ЕСЛИ(В2 1;0,2;0,3)))».

Пример использования

Запись сложна при написании и проверке. Можно пропустить скобку или неверно указать порядок аргументов. Для упрощения в 2016 году была введена «ЕСЛИМН». При ее использовании не нужно писать «ЕСЛИ» для каждого условия и следить за количеством скобок. Та же задача с «ЕСЛИМН»:

Запись упростилась, указываются условия и соответствующие им значения. Последним аргументом можно указать оператор ИСТИНА и задать нужную величину. В случае если ни одно из условий не выполнилось, будет возвращен параметр функции ИСТИНА. В данном случае, если В2 >=1, то награда 30%.

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

Работа с ПЕРЕКЛЮЧ

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

Ввод формулы

Работа оператора иллюстрируется на рисунке.

Здесь вместо чисел 1, 2, 7 — нужно проставить прописью дни недели им соответствующие. Если будут другие цифры, то возвратится значение по умолчанию «Нет совпадений (No match)».

Использование ЕСЛИОШИБКА

Оператор используется для нахождения ошибки в таблице. Найдя ее, функция не пишет в ячейке какую-либо из ошибок, а возвращает указанный ответ, который может быть текстом, пустой строкой: =ЕСЛИОШИБКА(Что_проверять;Что_выводить_вместо_ошибки).

Ввод формулы

Например, нужно поделить значения в столбце А на величины в столбце В. Если по ошибке в строках стоят 0, то получится деление на 0.

Если ошибка

Применение оператора «=ЕСЛИОШИБКА(A2/B2;»»)» скрывает ошибки.

Здесь сравнивается выражение A2/B2. В случае обнаружения ошибки в ячейку ставится пустая строка, указанная пробелом в кавычках ““.

ЕСЛИОШИБКА появилась в Excel 2007. До этого использовалась функция ЕОШИБКА, которая самостоятельно не могла обработать ошибку, так как имела только один аргумент, проверяющий указанную ячейку. Для ввода ответа в случае обнаружения ошибки, нужно было использовать оператор ЕСЛИ: «ЕСЛИ(ЕОШИБКА(А2/В2);”“;А2/В2)».

Простые операторы, редко применяются без связки с другими функциями.

Формула ИЛИ

На рисунке показан принцип действия функции И.

Пример использования: «=И(A1>B1; A2<>25)». Здесь созданы два условия:

  1. Значение в ячейке А1 должно быть больше числа в В1.
  2. Число в А2 должно быть не равно 25.

Выполнение условий

При исполнении обоих получается ИСТИНА.

Отрицание

Если одно из заданий нарушено, получается ЛОЖЬ. В данном случае число в А1 меньше чем в В1.

Оператор ИЛИ

Ниже представлен алгоритм функционирования оператора ИЛИ.

Варианты использования

Пусть даны 3 выражения: A1>B1; A2>B2; A3>B3. Требуется применить к ним действие ИЛИ: «=ИЛИ(A1>B1; A2>B2; A3>B3)». Возможные варианты показаны на рисунках:

Функция ложь

Здесь конечный результат ИСТИНА, так как из трех выражений одно верно: A3>B3. На следующем изображении функция выдала ответ «ЛОЖЬ», так как на все вопросы получены аналогичные ответы.

Действие ИСКИЛИ

В программировании функция соответствует операция «сложение по модулю 2» или XOR. Если имеется больше двух аргументов, то действуют следующие правила:

Функция ИСКИЛИ

  • результат «ИСТИНА», если количество таких ответов нечетно;
  • результат «ЛОЖЬ», если количество ответов «TRUE» четно;
  • результат «ЛОЖЬ», при условии, что все «FALSE».

Даны 4 условия A1>B1; A2>B2; A3>B3; A4>B4. В зависимости от данных ячеек результат действия функции может быть различным.

Варианты использования

На рисунке ниже получен результат «ИСТИНА», так есть 3 условия с аналогичным результатом: A1>B1 (100 ); А2 В2 (100>80); А3>В3 (100>70). Число условий с ответом ИСТИНА нечетно.

Функция ложь

В следующем варианте решением будет «ЛОЖЬ», так как есть 4 ответа «ИСТИНА» — четное количество.

На последнем рисунке функция также обретет значение ЛОЖЬ, так как не выполнено ни одно условие.

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

  1. Чтобы растянуть результат на другие строки столбца наводим указатель мыши на правый нижний угол ячейки с функцией “ДЛСТР”, как только он сменит вид на небольшой плюсик, зажав левую кнопку мыши тянем его вниз до той строки, для которой мы хотим получить аналогичный результат.Растягивание формулы на другие ячейки столбца
  2. Как мы можем заметить, благодаря этому нехитрому действию нам удалось в считанные секунды получить данные по количеству символов для каждой ячейки выбранного столбца.Растягивание формулы на другие ячейки столбца

Ранее мы упоминали, что в качестве аргумента функции “ДЛСТР” можно указать только адрес одной конкретной ячейки. Поэтому, чтобы определить суммарное количество знаков во всех ячейках столбца нужно воспользоваться одним из способов подсчета суммы значений. Подробнее ознакомиться со всеми методами можно в нашей статье – “Как посчитать в Экселе сумму столбца”.

Давайте к примеру, воспользуемся одним из способов:

  1. Выделяем ячейки столбца. Далее нажимаем кнопку “Сумма” в разделе инструментов “Редактирование” (вкладка “Главная”).Кнопка для суммирования значений ячеек столбца
  2. Результат в виде суммарного количества символов во всех ячейках столбца отобразится в ячейке сразу под выделенным диапазоном.Сумма значений ячеек столбца

Вставка и настройка функции

Как мы знаем, при объединении нескольких ячеек в одну, содержимое всех элементов за исключением самой верхней левой стирается. Чтобы этого не происходило, нужно использовать функцию СЦЕПИТЬ (СЦЕП).

Вставка функции в ячейку таблицы Эксель

  1. Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
  • находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;Заполнение аргументов функции СЦЕП в Эксель
  • кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.Заполнение аргументов функции СЦЕП в Excel

Аргументы функции без разделителей

Если разделители между содержимыми ячеек не нужны, в этом случае в значении каждого аргумента сразу указываем адреса требуемых элементов.

Аргументы функции СЦЕП (СЦЕПИТЬ) в Эксель

Результат по функции СЦЕП (СЦЕПИТЬ) в ячейке Эксель

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

Указание разделителя в отдельной ячейке

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

Например, мы добавляем запятую и пробел (“, “) в ячейку B16.

Ячейка с разделителем для функции СЦЕПИТЬ в Эксель

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

Заполнение аргументов функции СЦЕП (СЦЕПИТЬ) с разделителем в Эксель

Но здесь есть один нюанс. Чтобы при копировании формулы функции на другие ячейки не произошло нежелательного сдвига адреса ячейки с разделителем, ссылку на нее нужно сделать абсолютной. Для этого выделив адрес в поле соответствующего аргумента нажимаем кнопку F4. Напротив обозначений столбца и строки появятся символы “$”. После этого можно нажимать кнопку OK.

Заполнение аргументов функции СЦЕП (СЦЕПИТЬ) с зафиксированным разделителем в Эксель

Визуально в ячейке результат никак не будет отличаться от полученного ранее.

Результат по функции СЦЕП (СЦЕПИТЬ) с разделителем в Excel

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

Корректировка разделителя в функции СЦЕП (СЦЕПИТЬ) в Excel

Как ранее было отмечено, добавить в качестве разделителя можно любую текстовую, числовую и иную информацию, которой изначально не было в таблице.

Аргументы функции СЦЕП (СЦЕПИТЬ) с разными разделителями в Эксель

Таким образом, функция СЦЕП (СЦЕПИТЬ) предлагает большую вариативность действий, что позволяет наилучшим образом представить объединенные данные.

Результат по функции СЦЕП (СЦЕПИТЬ) с разными разделителями в Эксель

Описание функции ПРАВСИМВ

Задача функции “ПРАВСИМВ” состоит в извлечении заданного количества символов из указанной пользователем ячейки. При этом отсчет символов начинается с правой стороны ячейки, т.е. с ее конца. Результат выводится в заранее выбранную ячейку, в которую вставляется соответствующий оператор или прописывается формула вручную.

Данный оператор предназначен для работы с текстовыми данными и относится к категории “текстовые”. Сама формула функции выглядит следующим образом:

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

Как вы можете заметить, в этом операторе присутствует два аргумента:

  1. Первый аргумент – “Текст”. Он отвечает за исходное значение, из которой будут извлекаться символы. Причем, это значение может быть как виде конкретного текста (в этом случае указанное количество символов будет извлекаться из этого самого текста), так и в виде координат ячейки, из которого будет происходить извлечение.
  2. Второй аргумент – “Число_знаков”. Он отвечает за то, сколько знаков требуется извлечь из исходного значения и выражается в цифровом эквиваленте. Этот аргумент не является обязательным в данной функции и его можно опустить. В случае, если аргумент не заполнен, в результирующей ячейке будет выведен только один самый последний символ справа из указанного текстового аргумента (как будто мы указали число знаков, равное цифре “1”).

Логические функции в Excel и примеры решения задач

Задача 1. Необходимо переоценить товарные остатки. Если продукт хранится на складе дольше 8 месяцев, уменьшить его цену в 2 раза.

Сформируем таблицу с исходными параметрами:

Чтобы решить поставленную задачу, воспользуемся логической функцией ЕСЛИ. Формула будет выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2).

Логическое выражение «С2>=8» построено с помощью операторов отношения «>» и «=». Результат его вычисления – логическая величина «ИСТИНА» или «ЛОЖЬ». В первом случае функция возвращает значение «В2/2». Во втором – «В2».

Усложним задачу – задействуем логическую функцию И. Теперь условие такое: если товар хранится дольше 8 месяцев, то его стоимость уменьшается в 2 раза. Если дольше 5 месяцев, но меньше 8 – в 1,5 раза.

Формула приобретает следующий вид: =8);B2/2;ЕСЛИ(И(C2>=5);B2/1,5;B2))’ >.

В функции ЕСЛИ можно использовать в качестве аргументов текстовые значения.

Задача 2. Если стоимость товара на складе после уценки стала меньше 300 р. или продукт хранится дольше 10 месяцев, его списывают.

Для решения используем логические функции ЕСЛИ и ИЛИ: =10);»списан»;»»)’ >. Условие, записанное с помощью логической операции ИЛИ, расшифровывается так: товар списывается, если число в ячейке D2 = 10.

При невыполнении условия функция ЕСЛИ возвращает пустую ячейку.

В качестве аргументов можно использовать другие функции. К примеру, математические.

Задача 3. Ученики перед поступлением в гимназию сдают математику, русский и английский языки. Проходной балл – 12. По математике для поступления нужно получить не менее 4 баллов. Составить отчет о поступлении.

Составим таблицу с исходными данными:

Нужно общее количество баллов сравнить с проходным баллом. И проверить, чтобы по математике оценка была не ниже «4». В графе «Результат» поставить «принят» или «нет».

Введем формулу вида: =4;СУММ(B3:D3)>=$B$1);»принят»;»нет»)’ >. Логический оператор «И» заставляет функцию проверять истинность двух условий. Математическая функция «СУММ» используется для подсчета итогового балла.

Функция ЕСЛИ позволяет решать многочисленные задачи, поэтому используется чаще всего.

Создание условных формул

​Если значение в ячейке​На листе выделите ячейку​=И(A2>A3; A2​НЕ​в поле ячейки​ знак «не равно»​​ используется для вычислений,​​ в противном случае​​ как для оператора​​ противном случае возвращается​​ функции НЕ может​​ они намного чаще​​: =ЕСЛИ(И(A1=5;А2=5);1;"не имеет никакого​​ 255, определяющее нужный​

​ в статье «Диаграмма​​?​​Можно в формулу​ A2 не меньше​

​Определяет, выполняется ли следующее​, а также операторы​

​ вставлен успешно.​ сначала кликаем по​

​ а второй исключительно​ не выполнять никаких​

​ ИЛИ требуется, чтобы​ значение ЛОЖЬ. В​ быть только одно​ встречаются в сочетании​

В этой статье

​ значения")​ знак. Знаки выбираются​ в Excel план-факт».​). В формуле он​

​Excel вставить символ, подстановочные​ и не равно​ клавиши CTRL+V.​ условие: значение в​ (см. пример ниже).​

Создание условной формулы с результатом, являющимся логическим значением (ИСТИНА или ЛОЖЬ)

​Мы выяснили, что в​ элементу​​ для графического отображения.​​ действий. В этом​​ один из аргументов​​ этом случае значение​​ условие.​​ с функцией ЕСЛИ.​Михаил С.​

Пример

​ из набора знаков​Примечание:​ означает один любой​ знаки​

​Важно:​ ячейке A2 больше​

​Чтобы этот пример проще​​ Эксель существует два​«, затем нажимаем на​

​ случае A5 больше​

​ был истинным, формула​ не больше чем​

​Ниже приведены формулы с​Используйте функцию ЕСЛИ вместе​: >​

​ компьютера.​​Мы стараемся как​ знак. Например, нам​. Эти символы можно​ в противном случае​

​ Чтобы пример заработал должным​ значения A3 и​ было понять, скопируйте​ вида символов​ кнопку​«<>»​​ B2, поэтому формула​​ возвращает значение ИСТИНА.​​ 50, поэтому формула​​ расшифровкой их логики.​​ с функциями И,​​И(A1=A2;A1<>0)​

​Примечание:​ можно оперативнее обеспечивать​ нужно найти все​ применить для поиска,​

​ образом, его необходимо​

​ меньше значения A4​

​применяется в логических​


​ возвращает значение ЛОЖЬ.​


​ Если вы используете​


​ возвращает значение ИСТИНА.​

​ Excel Online поддерживает только​

​ вас актуальными справочными​

​ слова, которые начинаются​

​ в условном форматировании,​

​ вставить в ячейку​

​ формулах Эксель, когда​ Если изменить формулу​ мастер вычисления формул​=ЕСЛИ(НЕ(A7="красный");ИСТИНА;ЛОЖЬ)​Описание​ чтобы оценивать несколько​

​ значения СИМВОЛ(9), СИМВОЛ(10),​ материалами на вашем​ с буквы «с»​ др. Существую​Если значение в ячейке​ A1 листа.​

​Копирование примера​ состоит из знаков​ этого жмем​ нужно показать неравенство​ на​

​Если значение A7 ("синий")​=ЕСЛИ(И(A2>0;B2​ условий.​"Меньше или равно"​ СИМВОЛ(13), СИМВОЛ(32) и​

​ языке. Эта страница​

​ и заканчиваются на​подстановочные знаки в​ A5 не равно​Чтобы переключиться между просмотром​Определяет, выполняется ли следующее​Выделите пример, приведенный в​«меньше»​

​«>»​ аргументов. Впрочем, его​=НЕ(B2>A5)​ вы увидите, как​ НЕ равно "красный",​

​Если A2 (25) больше​

Создание условной формулы с результатом в виде других вычислений или значений, отличных от ИСТИНА или ЛОЖЬ.

​Синтаксис​ — "Не ровняется"​​ выше.​​ переведена автоматически, поэтому​​ буквы «ла» (сЕла,​​Excel​​ строке «Винты», возвращается​​ результатов и просмотром​ условие: значение в​

Пример

​ этой статье.​и​и опять на​ можно применять и​

​, она вернет значение​

​ Excel вычисляет формулу.​ возвращается значение ИСТИНА,​

​ нуля и B2​​ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …),​ — <>​

​Скопируйте образец данных из​

​ сИла). В пустой​– это символы,​

​ «ОК», в противном​ формул, возвращающих эти​ ячейке A2 больше​

​Важно:​​«больше»​ кнопку​ для визуального обозначения,​ ИСТИНА, а ячейка​

​=ЕСЛИ(НЕ(A5>B2);ИСТИНА;ЛОЖЬ)​ в противном случае​ (75) меньше 100,​ значение_если_истина; [значение_если_ложь]))​Шит​ следующей таблицы и​​ содержать неточности и​​ ячейке (В4) напишем​​ которые обозначают определенные​​ случае — «Неверно»​​ результаты, нажмите клавиши​​ значения A3 или​

​ Не выделяйте заголовки строк​, и используется для​«Вставить»​ так как он​

​Если A5 не больше​

​ возвращается значение ЛОЖЬ.​

​ возвращается значение ИСТИНА,​

​ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …),​


​ грамматические ошибки. Для​


​ такую формулу. =СЧЁТЕСЛИ(A1:A10;"с?ла")​

В Excel часто необходимо использовать при вычислениях значения, которые повязанных с символами, знаком числа и типом.

Для этого используются следующие функции:

Функция СИМВОЛ дает возможность получить знак с заданным его кодом. Функция используется, чтоб преобразовать числовые коды символов, которые получены с других компьютеров, в символы данного компьютера.

Функция ТИП определяет типы данных ячейки, возвращая соответствующее число.

Функция ЗНАК возвращает знак числа и возвращает значение 1, если оно положительное, 0, если равно 0, и -1, когда – отрицательное.

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Вставка символа без клавиатуры

В Word для вставки символа приблизительно равно можно воспользоваться функцией вставки. На вкладке «Вставка» открываем окно «Другие символы».

вставка символов

Выбираем шрифт «обычный текст» и набор «математические операторы». В первых рядах будет нужный знак.

приблизительно равно

Ссылка на основную публикацию