Похожие статьи

Excel в одной ячейке несколько значений

Таблицы Excel — весьма мощнейший инструмент. В их больше 470 укрытых функций. Сначала это стращает: кажется, на то, чтоб разобраться со всем, уйдут годы. По сути это не так. Всего 10-ка функций и жарких кнопок уже хватит для того, чтоб очень упростить для себя жизнь. Поведаем о неких из их (скоро стартует 2-ой поток курса «Мистика Excel»).

Интерфейс

Настраиваем панель резвого доступа

Начнем с самого обычного — прибавления самых нередко применяемых опций на панель резвого доступа. Чтоб создать это, входите в характеристики Excel — «Настроить ленту» — и отыскиваете в параметрах «Панель резвого доступа».

Функции, перенесенные на панель резвого доступа, будут доступны при работе со всеми вашими книжками Excel (хотя можно ее настроить и раздельно для хоть какой книжки). Так что если пользуетесь какими-то командами и инструментами повсевременно — добавляйте их туда.

Иной вариант — просто щелкнуть по инструменту на ленте правой клавишей мыши и надавить «Добавить…»:

Перемещаемся по ленте без мышки

Нажмите на Alt. На ленте инструментов возникли числа и буковкы — у всякого инструмента на панели резвого доступа и у каждой вкладки на ленте соответственно:

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

Ввод данных

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

Автозамена

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

Прогрессия

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

Протягивание

Представьте, что для вас необходимо извлечь какие-то данные из целого столбца либо переписать их в другом виде (к примеру, фамилию с инициалами заместо полных ФИО). Задайте Excel одну ячейку с прототипом — что желаете получить:

Выделите все ячейки, которые желаете заполнить по эталону, — и нажмите Ctrl+E. И мистика случится (ну, в большинстве случаев).

Проверка ошибок

Проверка данных дозволяет избежать ошибок при вводе инфы в ячейки.

Какие бывают типовые ошибки в Excel?

Текст заместо чисел
Отрицательные числа там, где их быть не может
Числа с дробной частью там, где должны быть целые
Текст заместо даты
Различные варианты написания 1-го и такого же значения. К примеру, сокращения («ЭБ» заместо «Электрическая библиотека»), излишние пробелы в конце текстового значения либо меж словами — всего этого довольно, чтоб перевоплотить текстовые значения в различные и, соответственно, чтоб они обрабатывались Excel неправильно.

Инструмент проверки данных

Чтоб применять инструмент проверки данных, необходимо выделить ячейки, к которым желаете его применить, избрать на ленте «Данные» → «Проверка данных» и настроить характеристики проверки в диалоговом окне:

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

Если же вы избрали «Предупреждение» либо «Сообщение», то при попытке ввести неправильные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.

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

Удаление пробелов

Для удаления излишних пробелов (в начале, в конце и всех не считая 1-го меж слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, обычно).

Если опосля чистки данных функцией СЖПРОБЕЛЫ либо иной обработки для вас не нужен начальный столбец, вставьте данные, приобретенные в отдельном столбце при помощи функций, как значения на пространство начальных данных, а столбец с формулой удалите:

Дата и время

За хоть какой датой в Excel прячется целое число. Датой его делает формат.

Аналогично с течением времени: одна единица — это денек, а часть единицы (число от 0 до 1) — время, другими словами часть денька.

Это не означает, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из обычных форматов — Excel сходу отформатирует их как даты:

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

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

Поиск и подстановка значений

Функция ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтоб связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (к примеру, наименованию продукта либо бренда, фамилии сотрудника либо клиента, номеру транзакции).

=ВПР (что отыскиваем; таблица с данными, где «что отыскиваем» обязано быть в первом столбце; номер столбца таблицы, из которого необходимы данные; [интервальный просмотр])

У нее есть два режима работы: интервальный просмотр и четкий поиск.

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

Почти всегда мы связываем таблицы по текстовым ключам — в таком случае необходимо непременно очевидным образом указывать крайний аргумент «интервальный_просмотр» равным нулю (либо ЛОЖЬ). Лишь тогда функция будет корректно работать с текстовыми значениями.

Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX

У ВПР есть значимый недочет: ключ (разыскиваемое значение) должен быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» нереально.

Функция ПОИСКПОЗ / MATCH описывает порядковый номер значения в спектре. Ее синтаксис:

=ПОИСКПОЗ (что отыскиваем; где отыскиваем ; 0)

На выходе — число (номер строчки либо столбца в рамках спектра, в котором находится разыскиваемое значение).

ИНДЕКС / INDEX делает другую задачку — возвращает элемент по его номеру.

=ИНДЕКС(спектр, из которого необходимы данные; порядковый номер элемента)

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

Выходит последующая система:

=ИНДЕКС(спектр, из которого необходимы данные; ПОИСКПОЗ (что отыскиваем; где отыскиваем ; 0))

Оформление

Необходимо оформить ячейки в книжке Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».

На ленте инструментов нажмите на «Стили ячеек» и изберите пригодный. Он будет использован к выделенным ячейкам:

А самое основное — если вы применили стиль ко почти всем ячейкам (к примеру, ко всем заголовкам на 20 листах книжки Excel) и захотели что-то переработать, щелкните правой клавишей мыши и нажмите «Поменять». Конфигурации будут использованы ко всем необходимым ячейкам в документе.

На курсе «Мистика Excel» будет два модуля — для новичков и продвинутых. Записывайтесь →

Применение деления в программке Microsoft Excel

В Microsoft Excel деление можно произвести как с помощью формул, так и используя функции. Делимым и делителем при всем этом выступают числа и адреса ячеек.

Интересно почитать: Excel выбор значения ячейки из списка

Метод 1: деление числа на число

Лист Эксель можно применять как типичный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (оборотная черта) – «/».

«равно»

(=)

(/)

Формула деления в Microsoft Excel

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

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

Как понятно, деление на 0 является неправильным действием. Потому при таковой попытке совершить схожий расчет в Экселе в ячейке покажется итог «#ДЕЛ/0!».

Метод 2: деление содержимого ячеек

Также в Excel можно разделять данные, находящиеся в ячейках.

Выделяем в ячейку, в которую будет выводиться итог вычисления. Ставим в ней символ «=». Дальше кликаем по месту, в котором размещено делимое. За сиим её адресок возникает в строке формул опосля знака «равно». Дальше с клавиатуры устанавливаем символ «/». Кликаем по ячейке, в которой расположен делитель. Если делителей несколько, так же как и в прошлом методе, указываем их все, а перед их адресами ставим символ деления.

Можно также сочетать, в качестве делимого либо делителя используя сразу и адреса ячеек и статические числа.

Метод 3: деление столбца на столбец

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

«=»

«/»

Как лицезреем, опосля этого деяния будет вполне выполнена процедура деления 1-го столбца на 2-ой, а итог выведен в отдельной колонке. Дело в том, что средством маркера наполнения делается копирование формулы в нижние ячейки. Но, с учетом того, что по дефлоту все ссылки относительные, а не абсолютные, то в формуле по мере перемещения вниз происходит изменение адресов ячеек относительно начальных координат. А конкретно это нам и необходимо для определенного варианта.

Метод 4: деление столбца на константу

Бывают случаи, когда необходимо поделить столбец на одно и то же неизменное число – константу, и вывести сумму деления в отдельную колонку.

«равно»

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

Метод 5: деление столбца на ячейку

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

«=»

(/)

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

Метод 6: функция ЧАСТНОЕ

Деление в Экселе можно также выполнить с помощью специальной функции, которая именуется ЧАСТНОЕ. Изюминка данной нам функции состоит в том, что она разделяет, но без остатка. Другими словами, при использовании данного метода деления итогом постоянно будет целое число. При всем этом, округление делается не по принятым математическим правилам к наиблежайшему целому, а к наименьшему по модулю. Другими словами, число 5,8 функция округлит не до 6, а до 5.

Поглядим применение данной функции на примере.

«Вставить функцию»

Опосля этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного метода деления.

Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис смотрится последующим образом:

Как лицезреем, главным методом деления в программке Microsoft Office является внедрение формул. Эмблемой деления в их является слеш – «/». В то же время, для определенных целей можно применять в процессе деления функцию ЧАСТНОЕ. Но, необходимо учитывать, что при расчете таковым методом разность выходит без остатка, целым числом. При всем этом округление делается не по принятым нормам, а к наименьшему по модулю целому числу.

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

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

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

Даже если вы годами используете функцию ВПР, то с высочайшей толикой вероятности эта статья будет для вас полезна и не оставит флегмантичным. Я, к примеру, будучи IT-специалистом, а позже и управляющим в IT, воспользовался VLOOKUP 15 лет, но разобраться со всеми аспектами довелось лишь на данный момент, когда я на проф базе стал учить людей Excel.

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само заглавие функции намекает нам, что она производит поиск в строчках таблицы (по вертикали — перебирая строчки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строчку). Нужно увидеть, что у ВПР есть сестра — противный утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, но теория Excel (ну и совершенно теория организации данных) предполагает, что ваши таблицы имеют маленькое количество столбцов и еще большее количество строк. Конкретно потому поиск по строчкам нам требуется во много раз почаще, чем по столбцам. Если вы в Excel очень нередко пользуетесь функцией ГПР, то, полностью возможно, что вы чего-то не сообразили в данной нам жизни.

Интересно почитать: Как в excel сделать одинаковый размер ячеек

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( <ЧТО> ; <ГДЕ> ; <НОМЕР_СТОЛБЦА> [;<ОТСОРТИРОВАНО>] ), здесь:

<ЧТО> — разыскиваемое значение (изредка) либо ссылка на ячейку, содержащую разыскиваемое значение (подавляющее большая часть случаев);

В Telegram-канале «Лайфхакер» лишь наилучшие тексты о разработках, отношениях, спорте, кино и многом другом. Подписывайтесь!

В нашем Pinterest лишь наилучшие тексты о отношениях, спорте, кино, здоровье и многом другом. Подписывайтесь!

<ГДЕ> — ссылка на спектр ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра <ЧТО>;

<НОМЕР_СТОЛБЦА> — номер столбца в спектре, из которого будет возвращено значение;

<ОТСОРТИРОВАНО> — это весьма принципиальный параметр, который отвечает на вопросец, а отсортирован ли по возрастанию 1-ый столбец спектра <ГДЕ>. В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) либо 1, в неприятном случае — ЛОЖЬ (FALSE) либо 0. В случае, если данный параметр опущен, он по дефлоту становится равным 1.

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

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

Как непосредственно работает формула ВПР

Вид формулы I. Если крайний параметр опущен либо указан равным 1, то ВПР подразумевает, что 1-ый столбец отсортирован по возрастанию, потому поиск останавливается на той строке, которая конкретно предшествует строке, в которой находится значение, превышающее разыскиваемое. Если таковой строчки не найдено, то ворачивается крайняя строчка спектра.
Вид формулы II. Если крайний параметр указан равным 0, то ВПР поочередно просматривает 1-ый столбец массива и сходу останавливает поиск, когда найдено 1-ое четкое соответствие с параметром <ЧТО>, в неприятном случае ворачивается код ошибки #Н/Д (#N/A).

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

Формулы можно применять для распределения значений по спектрам.
Если 1-ый столбец <ГДЕ> содержит повторяющиеся значения и верно отсортирован, то будет возвращена крайняя из строк с циклическими значениями.
Если находить значение заранее большее, чем может содержать 1-ый столбец, то можно просто отыскивать последнюю строчку таблицы, что бывает достаточно ценно.
Данный вид вернёт ошибку #Н/Д, лишь если не найдёт значения меньше либо равное разыскиваемому.
Осознать, что формула возвращает некорректные значения, в случае если ваш массив не отсортирован, достаточно проблемно.

Следствия для формул вида II

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

Производительность работы функции ВПР

Вы добрались до кульминационного места статьи. Чудилось бы, ну какая разница, укажу ли я в качестве крайнего параметра ноль либо единицу? В главном все указывают, естественно же, ноль, потому что это достаточно удобно: не нужно хлопотать о сортировке первого столбца массива, сходу видно, найдено значение либо нет. Но если у вас на листе несколько тыщ формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медлительно. При всем этом обычно все начинают мыслить:

мне нужен наиболее мощнейший комп;
мне нужна наиболее стремительная формула, к примеру, почти все знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая типо резвее на ничтожные 5–10%.

И не много кто задумывается, что стоит лишь начать применять ВПР вида I и обеспечить хоть какими методами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Этот факт я проверил полностью надёжно.

Секрет таковой резвой работы кроется в том, что на отсортированном массиве можно использовать очень действенный метод поиска, который носит заглавие бинарного поиска (способ деления напополам, способ дихотомии). Итак вот ВПР вида I его применяет, а ВПР вида II отыскивает без какой-нибудь оптимизации совершенно. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, также и к функции ПРОСМОТР (LOOKUP), которая работает лишь на отсортированных массивах и включена в Excel ради сопоставимости с Lotus 1-2-3.

Недочеты формулы

Недочеты ВПР явны: во-1-х, она отыскивает лишь в первом столбце обозначенного массива, а во-2-х, лишь справа от данного столбца. Как вы осознаете, полностью может случиться так, что столбец, содержащий нужную информацию, окажется слева от столбца, в котором мы будем находить. Этого недочета лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её более гибким решением по извлечению данных из таблиц в сопоставлении с ВПР (VLOOKUP).

Некие нюансы внедрения формулы в настоящей жизни

Диапазонный поиск

Традиционная иллюстрация к диапазонному поиску — задачка определения скидки по размеру заказа.

Поиск текстовых строк

Непременно, ВПР отыскивает не только лишь числа, да и текст. При всем этом нужно принимать во внимание, что регистр знаков формула не различает. Если применять знаки подстановки, то можно организовать нечёткий поиск. Есть два знака подстановки: «?» — подменяет один хоть какой знак в текстовой строке, «*» — подменяет хоть какое количество всех знаков.

Борьба с пробелами

Нередко поднимается вопросец, как решить делему излишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от их, то 1-ый параметр формулы ВПР не постоянно зависит от вас. Потому если риск засорения ячеек излишними пробелами находится, то можно использовать для чистки функции СЖПРОБЕЛЫ (TRIM).

Различный формат данных

Если 1-ый параметр функции ВПР ссылается на ячейку, которая содержит число, но которое хранится в ячейке в текстовом виде, а 1-ый столбец массива содержит числа в правильном формате, то поиск будет плохим. Вероятна и оборотная ситуация. Неувязка просто решается переводом параметра 1 в нужный формат:

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и напротив.

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

Двойное отрицание —D7.
Умножение на единицу D7*1.
Сложение с нулём D7+0.
Возведение в первую степень D7^1.

Перевод числа в текст делается через сцепку с пустой строчкой, которая принуждает Excel конвертировать тип данных.

Как подавить выдачу #Н/Д

Это весьма комфортно созодать с помощью функции ЕСЛИОШИБКА (IFERROR).

К примеру: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строчка), а если ошибки не вышло, то эта функция сделает вид, что её совершенно нет, а есть лишь ВПР, вернувший обычный итог.

Массив <ГДЕ>

Нередко запамятывают ссылку массива создать абсолютной, и при протягивании массив «плывёт». Помните, что заместо A2:C5 следует применять $A$2:$C$5.

Интересно почитать: Горячие клавиши excel объединить ячейки

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

Ещё наиболее неплохой мыслью будет объявление этого массива в виде именованного спектра.

Почти все юзеры при указании массива употребляют систему вида A:C, указывая столбцы полностью. Этот подход имеет право на существование, потому что вы избавлены от необходимости выслеживать тот факт, что ваш массив включает все нужные строчки. Если вы добавите строчки на лист с начальным массивом, то спектр, обозначенный как A:C, не придётся корректировать. Непременно, эта синтаксическая система принуждает Excel проводить несколько огромную работу, чем при четком указании спектра, но данными затратными расходами можно пренебречь. Речь идёт о сотых толиках секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Внедрение функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные с помощью ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит наименьшее количество строк, то в ВПР можно применять функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При всем этом все ВПР-формулы будут схожими (с поправкой на 1-ый параметр, который изменяется автоматом)! Направьте внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость находить по нескольким столбцам сразу, то нужно созодать составной ключ для поиска. Если б возвращаемое значение было не текстовым (как здесь в случае с полем «Код»), а числовым, то для этого подошла бы наиболее комфортная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы совсем.

Это моя 1-ая статья для Лайфхакера. Если для вас понравилось, то приглашаю вас посетить мой веб-сайт, также с наслаждением прочту в комментах о ваших секретах использования функции ВПР и ей схожих. Спасибо. 🙂

Exceltip

Блог о программке Microsoft Excel: приемы, хитрости, секреты, трюки

Несколько критерий ЕСЛИ в Excel

Функция ЕСЛИ в Excel дозволяет оценивать ситуацию с 2-ух точек зрения, к примеру, значение больше 0 либо меньше, и в зависимости от ответа на этот вопросец, произведи последующие расчеты по той либо другой формуле. Но, не редки ситуации, когда для вас приходится работать наиболее, чем с 2-мя критериями. В нынешней статье мы разглядим примеры сотворения формул в Excel с несколькими критериями ЕСЛИ.

До этого, чем начать учить данный урок, рекомендую прочесть статью про функцию ЕСЛИ, где описаны главные приемы работы.

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

К примеру: =ЕСЛИ(A5=0;»НОЛЬ»;ЕСЛИ(A5<0;»МЕНЬШЕ НОЛЯ»;»БОЛЬШЕ НОЛЯ»)), где функция оценивает значение ячейки A5 дважды, 1-ый, инспектирует, приравнивается ли значение нулю, и возвращает текст – НОЛЬ, если ИСТИНА. Если итог оценки возвратил значение ЛОЖЬ, происходит 2-ая оценка, функция инспектирует, является ли значение ячейки A5 меньше ноля, и возвращает текст МЕНЬШЕ НОЛЯ, если итог ИСТИНА, в неприятном случае возвращает текст БОЛЬШЕ НОЛЯ.

Таковым образом, в примере выше, формула возвратит значение МЕНЬШЕ НОЛЯ, потому что при первой оценке, итог оказался ЛОЖЬ, а при 2-ой оценке ИСТИНА.

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

Если реализации меньше либо равны 500$, комиссия составляет 7%
Если реализации больше 500$, но меньше либо равны 750%, комиссия составляет 10%
Если реализации больше 750$, но меньше либо равны 1000%, комиссия составляет 12,5%
Если реализации больше 1000$, комиссия составляет 16%

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

Реализации меньше либо равны 500$. Если ИСТИНА, рассчитываем комиссию.
Если ЛОЖЬ, то реализации меньше либо равны 750$. Если ИСТИНА, рассчитываем комиссию.
Если ЛОЖЬ, то реализации меньше либо равны 1000$. Если ИСТИНА, рассчитываем комиссию.
Если ЛОЖЬ, рассчитываем комиссию, потому что это будет означать, что реализации больше 1000$ и больше логических тестов проводить не надо.

Давайте сделаем формулу следуя данной логике для торговца Сергея. (Я выделил жирным проверку логики для наилучшего осознания).

=ЕСЛИ(B4<400;B4*7%;ЕСЛИ(B4<750;B4*10%;ЕСЛИ(B4<1000;B4*12.5%;B4*16%)))

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

Логическое выражение в первой формуле ЕСЛИ инспектирует, является ли значение в ячейке B4 меньше 400, если ИСТИНА, формула множит значение ячейки B4 на 7% и останавливает последующие вычисления. Если значение ячейки B4 больше 400, мы перебегаем к последующей функции ЕСЛИ. Так будет длиться, пока мы не достигнем крайнего значения, где значение ячейки множится на 16%. Это означает, что ни одно из критерий не удовлетворило требованиям, т.е. реализации составляют наиболее 1000$.

Ниже вы видите, как будет смотреться колонка Комиссия, когда все формулы будут введены. Также в колонке Формула отображены формулы для всякого торговца.

Можно проверить на примере Натальи корректность работы формулы. Реализации Натальи составили 844$, т.е. больше, чем 750$, но меньше чем 1000$. Соответственно, коэффициент комиссии будет приравниваться 12,5%, а сама комиссия составит 105,5$. Также принципиально отметить, о работе формулы с пограничными значениями. Представим, что сумма продаж Натальи составила 750$, какой коэффициент обязана применить формула? Коэффициент будет 12,5%, потому что для коэффициента 10% сумма продаж обязана приравниваться меньше 750. Это принципиальное замечание, потому будьте осторожны при составлении логики формулы.

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

Для вас также могут быть увлекательны последующие статьи

102 комментария

Необходимо возвратить определенное значение из ячейки и посчитать балл, т.е. к примеру в ячейке D3 быть может значение А, Б, В, Г, нужно в ячейку D4 возвратить значение в зависимости от буковкы, к примеру А=1, Б=2, В=3 и так дальше. Как создать? Можно ли через формулу ЕСЛИ?

Статья отменная, спасибо.
Но.. сначала статьи планы ставят из малого расчета 500$, а все последующие расчеты исходят из 400$.
Вроде бы нужно стараться следовать тем планам, что ставите.

Данный способ неплох, если у нас незначительно критериев (2-3), но когда их 10, то в таковой формуле позже тяжело разобратся «что и откуда». В таком случае можно (и необходимо) обойтись без ЕСЛИ.
Для этого создаем небольшую табличку с нашими аспектами: в первой строке по возрастанию заполняем аспекты (в приведенном примере это будут 0, 500, 750, 1000); во 2-ой строке под каждым аспектом заполняем соответственный процент (7, 10, 12,5, 16). Допустим, в спектре A1:D1 у нас заполнены аспекты, а в спектре A2:D2 — надлежащие проценты. В ячейке А5 имеем цифру продаж; для рассчета комиссии используем последующую формулу: =A5*ИНДЕКС($A$2:$D$2;ПОИСКПОЗ(A5;$A$1:$D$1;1)).
ПОИСКПОЗ отыскивает размещение аспекта, который меньше продаж, но больший в перечне, а ИНДЕКС по приобретенному номеру выдает нам нужный процент.