Топ-5 функций Excel, которые сберегут ваше время

Сегодня желаю поделиться своим возлюбленным набором функций в Excel. Эти 5 функций мне весьма помогают в работе. Рекомендую направить на их внимание, если ранее не употребляли. Они приметно сократят время на обработку данных в Excel.

Сегодня желаю поделиться своим возлюбленным набором функций в Excel.

1. ЕСЛИ

– относится к логическим функциям и дозволяет созодать расчеты для 1-го либо нескольких критерий.

К примеру, условие выплаты премии: если процент выполнения KPI больше 80%, то сотрудник получает премию в размере 10%, если меньше либо равно 80%, то 0.

Функция Если может дополняться иными функциями, к примеру:

— СУММЕСЛИ – расчет суммы в зависимости от критерий

— СРЗНАЧЕСЛИ – расчет среднего в зависимости от критерий

— СЧЁТЕСЛИ – подсчет частей в зависимости от критерий.

Также еще есть функции СУММЕСЛИМН, СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН. Это функции используются, если у нас есть несколько критерий.

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

В решении данной задачки поможет функция СУММЕСЛИМН, потому что у нас несколько критерий: 1 – регион, 2 – Рейтинг результативности.

2. СУММПРОИЗВ

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

Есть условия: эти наборы данных должны быть идиентично размещены (другими словами горизонтальными или вертикальными) и содержать однообразное количество значений.

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

При помощи функции СУММПРОИЗВ мы можем просто посчитать этот процент.

3. ВПР

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

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

К примеру, нам необходимо из таблицы 2 подтянуть процент мотивированной премии в таблицу 1 по табельному номеру.

При помощи функции ВПР это можно создать за 5 секунд.

4. ГПР

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

5. Сочетание функций ИНДЕКС+ПОИСКПОЗ

– сочетание этих 2-ух функций дает огромные способности подставить значения из матрицы, чего же не в состоянии сделать функции ВПР/ГПР.

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

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

Интересно почитать: Функция в excel еслиошибка в excel

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

Эти 5 функций мне весьма помогают в работе. Рекомендую направить на их внимание, если ранее не употребляли. Они приметно сократят время на обработку данных в Excel.

Вложение функций И, ИЛИ и IF в Excel

Снимок экрана Microsoft Excel с обзором условных операторов с использованием функций IF, AND и OR

Вложенные функции в Excel относятся к размещению одной функции снутри иной. Вложенная функция действует как один из аргументов главный функции . AND, OR и IF функция является один из более узнаваемых Ехс логических функций , которые обычно употребляется совместно.

Аннотации в данной статье относятся к Excel 2019, 2016, 2013, 2010, 2007; Excel для Office 365, Excel Online и Excel для Mac.

Сделайте заявление Excel IF

При использовании функций IF, AND и OR одно либо все условия должны быть настоящими, чтоб функция возвращала ИСТИННЫЙ ответ. Если нет, функция возвращает FALSE в качестве значения.

Для функции ИЛИ (см. Строчку 2 на рисунке ниже), если одно из этих критерий производится, функция возвращает значение ИСТИНА. Для функции AND (см. Строчку 3) все три условия должны быть настоящими, чтоб функция возвращала значение TRUE.

На изображении ниже строчки с 4 по 6 содержат формулы, в которых функции AND и OR вложены в функцию IF.

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

В этом примере три условия проверяются по формулам в строчках 2 и 3:

Значение в ячейке A2 меньше 50?
Значение в ячейке A3 не равно 75?
Значение в ячейке A4 больше либо равно 100?

Не считая того, во всех примерах вложенная функция выступает в качестве первого аргумента функции IF . Этот 1-ый элемент известен как аргумент Logical_test.

Поменять вывод формулы

Во всех формулах в строчках 4–6 функции «И» и «ИЛИ» схожи своим аналогам в строчках 2 и 3 в том, что они инспектируют данные в ячейках с А2 по А4, чтоб найти, удовлетворяет ли это требуемому условию.

Функция IF употребляется для управления выводом формулы на базе того, что введено для второго и третьего аргументов функции. Примерами этого вывода могут быть текст, отображаемый в строке 4, число, отображаемое в строке 5, вывод из формулы либо пустая ячейка.

В случае формулы IF / AND в ячейке B5, так как не все три ячейки в спектре от A2 до A4 являются настоящими — значение в ячейке A4 не больше либо равно 100 — функция AND возвращает значение FALSE. Функция IF употребляет это значение и возвращает собственный аргумент Value_if_false — текущую дату, предоставленную функцией TODAY .

С иной стороны, формула IF / OR в четвертой строке возвращает текстовую аннотацию Data Correct по одной из 2-ух обстоятельств:

Интересно почитать: Сложные функции в excel

Значение OR возвратило значение TRUE — значение в ячейке A3 не равно 75.

Потом функция IF употребляла этот итог для возврата аргумента Value_if_false: Data Correct.

Используйте оператор IF в Excel

Последующие шаги обрисовывают, как ввести формулу IF / OR, расположенную в ячейке B4 из примера. Эти же шаги можно применять для ввода хоть какой формулы IF в этих примерах.

Есть два метода ввода формул в Excel. Или введите формулу в строке формул, или используйте диалоговое окно « Аргументы функции » . Диалоговое окно хлопочет о синтаксисе, таком как размещение разделителей запятых меж аргументами и окружающими текстовыми записями в кавычках.

Как посчитать количество дней в Эксель меж 2-мя датами, рабочие деньки в Excel

При работе с документами в программке Excel нередко возникает необходимость посчитать количество дней меж 2-мя датами.

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

К счастью, для решения схожих задач в Excel есть много готовых инструментов, потому делать подобные расчеты вручную нет необходимости. На данный момент вы узнаете как это делается в современных версиях Excel, таковых как Excel 2007, 2010, 2013 и 2016.

Подготовка ячеек для работы с датами

Перед тем как начинать работать с датами в Excel, нужно убедиться, что ячейки, в которых будут храниться даты, отформатированы в формат «Дата». Обычно Excel автоматом присваивает таковой формат при вводе данных схожих на дату, но если создать это вручную, то это дозволит избежать вероятных заморочек.

Для того чтоб поменять формат ячеек их нужно выделить мышкой, кликнуть по ним правой клавишей и в открывшемся меню избрать пункт «Формат ячеек».

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

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

Подсчет количества дней при помощи обычный формулы

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

Опосля этого нужно избрать ячейку, в которой будет находиться итог вычислений, и присвоить ей формат «Общий». Формат «Дата» для ячейки с результатом не подступает, так как в данной ячейке будет находиться количество дней (число), а не определенная дата.

Интересно почитать: Функция в excel поиск решения

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

Опосля ввода формулы нужно надавить на кнопку Enter, и вы получите итог.

Как лицезреем, в данном случае количество дней меж 2-мя датами составляет 41.

Подсчет количества дней меж 2-мя датами

Не считая этого, в Excel есть особая функция для подсчета количества дней меж 2-мя датами. Данная функция именуется «РАЗНДАТ» и имеет вот таковой синтаксис:

=РАЗНДАТ(начальная_дата;конечная_дата;единица)

При всем этом значение «Единица» — это обозначение возвращаемых данных.

Единица	Возвращаемое значение
«Y»	Количество полных лет в периоде.
«M»	Количество полных месяцев в периоде.
«D»	Количество дней в периоде.
«MD»	Разница в деньках меж исходной и конечной датой. Месяцы и годы дат не учитываются.
«YM»	Разница в месяцах меж исходной и конечной датой. Деньки и годы дат не учитываются.
«YD»	Разница в деньках меж исходной и конечной датой. Годы дат не учитываются.

Для того чтоб пользоваться данной функцией нужно выделить ячейку, в которой должен находиться итог, присвоить ей тип данных «Общий» и ввести формулу. В этом случае ввод формулы чуток труднее, нужно ввести знак «=», позже заглавие функции «РАЗНДАТ», а позже открыть круглые скобки и ввести адреса ячеек с датами и единицу возвращаемых значений.

Направьте внимание, при использовании функции «РАЗНДАТ» поначалу нужно вводить адресок ячейки с наиболее ранешней датой, а позже с наиболее поздней. Опосля ввода формулы нужно надавить на кнопку Enter, и вы получите итог.

Подсчет количества рабочих дней меж 2-мя датами

Еще одна обычная задачка – это подсчет количества рабочих дней меж 2-мя датами. Для ее решения в Excel также есть особая функция, которая именуется «ЧИСТРАБНИ». Синтаксис данной функции последующий:

=ЧИСТРАБДНИ(начальная_дата;конечная_дата;[праздники])

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

Для того чтоб пользоваться данной функцией нужно выделить ячейку, в которой должен находиться итог, присвоить ей тип данных «Общий» и ввести формулу. Для ввода формулы нужно ввести знак «=», позже заглавие функции «ЧИСТРАБДНИ», позже открыть круглые скобки и ввести адреса ячеек с датами.