Как составить в экселе формулу: Как составить формулу в Excel: гид по Excel таблицам

Содержание

Excel-helper.ru – Excel с нуля

Содержит основные команды для работы с данными. Именно на этой вкладке Вы будете проводить основную часть работы с Excel.

Группа Буфер обмена

Cодержит команды для копирования данных и форматов.

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

Группа Шрифт

Содержит основные команды для преобразования внешнего вида таблицы: размер шрифта, цвет шрифта, цвет ячейки, прорисовка границ таблицы и т.д.

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

Группа Выравнивание

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

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

Группа Число

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

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

Группа Стили

Содержит 3 раздела:
Условное форматирование
Форматировать как таблицу
Стили ячеек

Группа Ячейки

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

Группа Редактирование

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

Автор AlfiОпубликовано Рубрики ЛентаДобавить комментарий к записи Вкладка Главная

Допустим у нас есть таблица с данными. Нужно посчитать количество пустых ячеек.

Для этого используем функцию СЧИТАТЬПУСТОТЫ.

Нажимаем на кнопочку Вставить функцию (слева от строки формул).
В появившемся окне в поле Категория выбираем Статистические.
В поле Выберете функцию выбираем СЧИТАТЬПУСТОТЫ.
Нажимаем ОК.

В появившемся окне мышкой встаем в поле Диапазон и мышкой выделяем данные в которых нужно посчитать пустые ячейки. Нажимаем ОК.

Функция СЧИТАТЬПУСТОТЫ подсчитывает не только пустые ячейки, но и ячейки выдающие как результат пустую строку.

Добавим еще один столбец и пропишем в нем формулу, которая выдает пустую ячейку.

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

Лог_выражение: В2>0

Значение_если_истина: В2

Значение_если_ложь: “”

Протянем нашу формулу на следующие ячейки. Наша табличка примет вид.

Формулу СЧИТАТЬПУСТОТЫ протянем на следующий столбец. Результат будет тот же.

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

Для подсчета только пустых ячеек используем функции СУММПРОИЗВ и ЕПУСТО.

Для начала вызовем функцию СУММПРОИЗВ. Для этого нажмем на кнопочку Вставить функцию (Fx). В появившемся окне выберем категорию Математические, в поле Выберете функцию, выбрать СУММПРОИЗВ. Нажать ОК.

Можно в строке формул написать =СУММПРОИЗВ(, встать на название функции курсором и нажать Вставить функцию (Fx). Появится окно с аргументами функции.

В поле Массив1 вписываем: –ЕПУСТО(В2:В10)

Функция ЕПУСТО определяет пустая ячейка или нет и возвращает логическое значение ИСТИНА или ЛОЖЬ.

— (два минуса) преобразуют функцию ЕПУСТО в математические значения 1 или 0. Если поставить один минус, формула вернет отрицательное значение. Два минуса дадут положительное значение.

При нажатии на ОК формула выдаст такой же результат, что и функция СЧИТАТЬПУСТОТЫ.

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

Добавим в данные пустую строку. Результат изменится на 1.

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

Диапазон в формуле автоматически изменится.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как посчитать пустые ячейки

Функция СЧЕТЗ подсчитывает количество непустых ячеек с любыми данными в любых форматах, в том числе ячейки содержащие формулу с пустой строкой (=””), формулы результаты которых отображают ноль, и формулы содержащие ошибку.

Синтаксис
СЧЕТЗ(значение1;значение2 и т.д.)

Значение1 – обязательный аргумент, содержащий ссылку на ячейку или диапазон ячеек с любыми данными.
Значение2 и т.д. – необязательный аргумент. Всего может быть использовано до 255 аргументов.

Для того, чтобы вставить функцию, нажмите на кнопочку Вставить функцию (слева от строки формул). В появившемся окне выберете категорию Статистические. В поле Выберете функцию, выбрать СЧЕТЗ. Нажать ОК.

В появившемся окне встаньте мышкой в поле Значение1 и выделите нужный диапазон (можно ввести вручную). Нажмите ОК.

В нашем случае Excel не возьмет только 1 пустую ячейку.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как посчитать количество непустых ячеек

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

Синтаксис
СЧЕТ(значение 1;значение2…)

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

Функция СЧЕТ будет учитывать:
В указанном диапазоне – только ячейки содержащие числа, даты и время.
В списке аргументов – текстовые значения чисел, логические выражения.
Никогда – текст, пустые ячейки и значения ошибок.

Функцию СЧЕТ можно найти, вызвав мастер функций.

  1. Для этого нажмите на кнопочку Вставить функцию (слева от строки формул).
  2. В появившемся окне выбрать категорию Статистические.
  3. В поле Выберете функцию, найдите функцию СЧЕТ.
  4. Нажмите ОК.

В появившемся окне в поле Значение1 выделите мышкой диапазон (или введите вручную), в котором нужно посчитать количество значений. Нажмите ОК.

В результате отобразится число 3, Excel учтет только первые 3 ячейки.

Для того, чтобы учитывались текстовое представление числа и логические выражения внесем их непосредственно в значения функции (в поля Значение2 и Значение3).
Максимально можно ввести до 255 значений.

Попробуем в аргументы ввести любой текст и ошибку. В результате получится число 3. Excel возьмет из диапазона Значения1 3 первые ячейки, а аргументы Значение2 и Значение3 учитываться не будут.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как посчитать количество ячеек, содержащие числовые значения

Допустим нам надо применить прогрессивную скидку в
зависимости от суммы заказа.

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

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

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

Для построения формулы встанем в нужную ячейку и нажмем на кнопочку Вставить функцию (слева от строки формул). В появившемся окне в поле Категория выбрать Ссылки и массивы. В поле Выберете функцию выбрать ВПР.

Появится окно с аргументами функции ВПР.

Искомое значение: Сумма заказа по которой будет определятся скидка. (В2)

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

  1. Таблица должна быть отсортирована по возрастанию.
  2. По 1-му столбцу таблицы будет определяться искомое значение.
  3. Для того, чтобы протягивать формулы на следующие заказы, диапазон таблицы не должен изменяться, поэтому для этого поля делаем ссылки абсолютными.
    Для этого мышкой выделяем диапазон таблицы (F2:G8) и начинаем нажимать клавишу F4, пока диапазон не изменится на $F$2:$G$8. Если у вас ноутбук, нужно одновременно нажать на клавиши Fn+F4. Знак доллара можно ввести и вручную.

Номер столбца: Столбец из которого необходимо вернуть значения. Отсчет будет вестись, начиная с первого столбца, по которому будет определяться искомое значение.

Интервальный просмотр: Логическое значение по которому будет определяться точно(ЛОЖЬ или 0) или приблизительно (ИСТИНА или 1) должен производится поиск в первом столбце. Если этот аргумент отсутствует, Excel будет определять приблизительные значения.

В нашем случае нужны приблизительные значения, так как первый столбец указывает не точную сумму, а диапазон (от 15 000 до 20 000, от 20 000 до 25 000 и т.д.). Поэтому это поле можно не заполнять или написать ИСТИНА или 1.

Далее протягиваем формулу для следующих заказов.

Мы вывели скидку в отдельную колонку. Также можно функцию ВПР сразу использовать в формуле и выводить уже готовый результат.

Для этого в ячейке пропишем

=B2*(100-ВПР(B2;$G$2:$H$8;2))/100

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Функция ВПР как замена нескольких условий функции ЕСЛИ

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

Синтаксис

=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение – любое значение, формула или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Значение_если_истина – значение, которое возвращается, если Лог_выражение имеет значение ИСТИНА.

Значение_если_ложь – значение, которое возвращается, если Лог_выражение имеет значение ЛОЖЬ.

Например:
=ЕСЛИ(A1<>””;1;0)
<> – не равно,
“” – пустая.
То есть, если ячейка А1 не пустая, возвращается 1, если пустая, то 0.
При этом не важно будет ли в ячейке А1 число, текст или любые другие знаки.

Допустим нам нужно, за заказ свыше 15000 руб применить скидку 10%.
В поле Лог_выражение напишем условие: сумма заказа(В2) больше 15000. Пропишем в Значениe_если_истина В2*0,9, а в Значениe_если_ложь вернем сумму заказа (В2).

Протянем формулу для остальных заказов.

Неправильное построение вложенных функций ЕСЛИ

Часто бывает, что необходимо применить более 1 условия.
Допустим нам надо при сумме заказа свыше 15000 руб, сделать скидку 10%, а свыше 20000 руб 15%.

Excel воспринимает до 7 уровней вложения функций и проверяет условия начиная с первого.

Например, мы напишем формулу неправильно:
=ЕСЛИ(B2>15000;B20,9;ЕСЛИ(B2>20000;B20,85;B2))

То есть если мы напишем первым условием В2>15000, то для суммы свыше 20000, скидка 15% применяться не будет.

Например, для ячейки В4 проверится первое Лог_выражение (B4>15000), Excel увидит, что это выражение верно и применит скидку 10%.

Поэтому начинать будем с суммы заказа больше 20%.

Как построить вложенные функции ЕСЛИ

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

Лог_выражение напишем В2>20000
Значение_если_истина – В2*0,85
В пустое поле Значение_если_ложь необходимо встать курсором и в поле имени (слева от строки формул) выбрать функцию ЕСЛИ.

При этом откроется новое окно, но начало формулы не пропадет.

В новом окне пропишем следующее условие:

Лог_выражение – В2>15000

Значение_если_истина – В2*0,9

Значение_если_ложь – В2.

Протянем формулу для остальных заказов.

Функция ИЛИ (результат изменяется при выполнении одного из нескольких условий)

Добавим к нашему примеру еще одно условие:
Если заказ оплачен более чем на 50%, предоставляется скидка тоже 10%.
Для наглядности занесем все наши условия в таблицу

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

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

Появятся аргументы второго условия ЕСЛИ.
Стереть поле Лог_выражение (В2>15000)

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию ИЛИ.

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

В появившемся окне прописываем наши 2 условия:

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

Протянем формулу для остальных заказов.

Функция И (результат изменяется при выполнении нескольких условий)

Скидки за заказ оставим на прежнем уровне:
Заказ более 20000 – 15%
Заказ более 15000 – 10%

Но если заказ более 15000 и оплачен более чем на 50% – скидка 20%

Для наглядности занесем все наши условия в таблицу

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

Встаем курсором на вторую функцию ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В появившемся окне стираем Лог_выражение: B2>15000.

Встать курсором в пустое поле Лог_выражение и в Поле имени выбрать функцию И.

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

В появившемся окне вписываем 2 наших условия

Логическое_значение 1: B2>15000

Логическое_значение 2: C2*100/B2>50

Когда начинаем прописывать 2 строку автоматически появляется 3-я. Оставляем ее пустой и нажимаем ОК.

При нажатии на ОК окно закроется.

Мы прописали только первую строчку (Лог_выражение) в нашем втором условии ЕСЛИ. Снова встаем на 2-ую ЕСЛИ и нажимаем на кнопочку Вставить функцию (слева от строки формул).

В первой строке (Лог_выражение) уже прописаны 2 наших условия.
Значение_если_истина исправляем 0,9 на 0,8, чтобы при соблюдении 2-х условий Excel считал скидку 20%
Значение_если_ложь стираем B2 (будем прописывать в нее 3-е условие).

Встаем курсором на Значение_если_ложь и в поле имени выбираем функцию ЕСЛИ.

Появится окно в которое мы будем прописывать 3-е условие.

Лог_выражение: B2>15000

Значение_если_истина: B2*0.9

Значение_если_ложь: B2

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

Обратите внимание в какой последовательности мы применяем вложенность функций ЕСЛИ.

Если мы пропишем 2-м условием Заказ более 15000 – 10%, а 3-м выполнение 2-х условий, Excel определит 2-е условие как истина и 3-е условие проверять не будет.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Функция ЕСЛИ в Excel

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

  1. Вычесть из конечной ячейки, начальную.
    Важно: Все ячейки должны быть в формате времени. При введении времени через двоеточие Excel обычно сам форматирует ячейки в нужный формат (3:00).
    Через Формат ячеек в формате Время можно выбрать Тип, необходимый для отображения. Как открыть формат ячеек.

При суммировании отработанного времени Excel покажет неправильный результат.

Для того, чтобы Excel просуммировал все ячейки с отработанным временем, в формате Время выберете Тип: 37:30:55.

2. Разницу во времени можно посчитать в десятичных долях.
Для этого разницу во времени необходимо умножить на 24. Например =(С3-В3)*24.

При суммировании отработанного времени проблем не возникнет.

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

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

Для формата записи ч:мм

Лог_выражение: Дата окончания больше даты начала

Значение_если_истина: К разнице во времени прибавляем 1.

Значение_если_ложь: От времени окончания отнимаем время начала.

Для формата записи ч,мм также к разнице во времени прибавляем 1, если дата окончания больше даты начала.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как посчитать разницу в часах (сложить часы).

Функция ЧАС

Функция ЧАС возвращает час заданного времени, определяется как целое число в интервале от 0 до 23.
Синтаксис
ЧАС(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть часы. Может быть представлено:

Функция МИНУТЫ

Функция МИНУТЫ возвращает минуты заданного времени, определяется как целое число в интервале от 0 до 59.
Синтаксис
МИНУТЫ(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть минуты. Значение аргумента такое же, как для функции ЧАС, например, =МИНУТЫ(“15:25”) вернет результат 25 и т. д.

Функция СЕКУНДЫ

Функция СЕКУНДЫ возвращает секунды заданного времени, определяется как целое число в интервале от 0 до 59.
Синтаксис
СЕКУНДЫ(время в числовом формате)
Время в числовом формате – время из которого нужно вернуть секунды. Значение аргумента такое же, как для функции ЧАС, например, =СЕКУНДЫ(“15:25:30”) вернет результат 30 и т.д.

Употребление функций ЧАС, МИНУТЫ, СЕКУНДЫ с другими функциями

Для примера рассчитаем время расписания занятий.
Сначала с помощью функции ВРЕМЯ напишем время начала 1 урока.

Время окончания: В мастере функций выберем функцию ВРЕМЯ. В аргументах функции пропишем, чтобы часы, минуты и секунды брались из ячейки D2, но в минутах прибавим длительность урока (ячейку B2).

Аналогично пропишем начало 2 урока: в аргументах функции ВРЕМЯ пропишем функции ЧАС, МИНУТЫ, СЕКУНДЫ со ссылками на время окончания 1 урока. В минутах прибавим время перерыва (С2).

Далее формулы можно просто продлить.

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

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как из времени извлечь часы (минуты, секунды)

Синтаксис

ВРЕМЯ(часы;минуты;секунды;)

Часы – число в интервале от 0 до 32767, задающее часы. Если
значение больше 23, оно будет делится на 24, а остаток будет соответствовать значению
часов. Например, ВРЕМЯ(24;0;0) = ВРЕМЯ (0;0;0) = 0:00, а ВРЕМЯ(49;0;0) =
ВРЕМЯ(1;0;0) = 1:00.

Минуты – число в интервале от 0 до 32767, задающее минуты.
Если значение больше 59, оно разделится на 60 и будет пересчитано в часы и
минуты. Например, ВРЕМЯ(0;60;0) = ВРЕМЯ(1;0;0) = 1:00, а ВРЕМЯ(0;125;0) =
ВРЕМЯ(2;05;0) = 2:05.

Секунды – число в интервале от 0 до 32767, задающее секунды.
Если значение больше 59, оно будет пересчитано на часы, минуты и секунды.
Например, ВРЕМЯ(0;0;70) = ВРЕМЯ(0;01;10) = 0:01:10, а ВРЕМЯ(0;0;10000) =
ВРЕМЯ(2;46;40) = 2:46:40.

Все аргументы функции ВРЕМЯ обязательные и должны быть прямо
указаны. Если минуты и секунды равны 0, то запись может быть либо ВРЕМЯ(1;0;0),
либо ВРЕМЯ(1;;).

Аргументы функции ВРЕМЯ могут быть самостоятельным числом,
ссылкой на ячейку или формулой.

Например, соберем время из трех разных ячеек.

Нужный вариант отображения времени можно выбрать в формате Время в поле Тип.

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как собрать время из отдельных ячеек

Часто при расчетах необходимо учитывать только рабочие дни. Для этого существует несколько функций:

РАБДЕНЬ – для определения даты отстоящей на заданное количество рабочих дней до или после начальной даты.

РАБДЕНЬ.МЕЖД – для определения даты отстоящей на заданное количество рабочих дней до или после начальной даты при нестандартной рабочей неделе (работает, начиная с Excel 2010).

ЧИСТРАБДНИ – для определения количества рабочих дней между датами.

ЧИСТРАБДНИ.МЕЖД – для определения количества рабочих дней между датами при нестандартной рабочей неделе (работает, начиная с Excel 2010).

РАБДЕНЬ

Синтаксис
РАБДЕНЬ(нач дата;количество дней;праздники)

Нач дата (обязательный аргумент) – дата от которой будет производится отсчет.

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

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

Важно: все даты могут быть или самостоятельной датой или результатом формул и функций. Даты должны быть в формате дата или числовом, но не в текстовом. Иначе результат формулы вернет ошибку.
Даты будут отсчитываться только те, которые находятся в диапазоне с 01. 01.1900г по 31.12.9999 г. Подробнее Как Excel обрабатывает дату и время

РАБДЕНЬ.МЕЖД

Синтаксис
РАБДЕНЬ.МЕЖД(нач дата;количество дней;выходные;праздники)

Нач дата (обязательный аргумент) – дата от которой будет производится отсчет.

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

Выходные (необязательный аргумент) – указывает какие дни недели будут считаться выходными. Могут быть выражены строкой, состоящей из 7 знаков, каждый из которых будет обозначать день недели. В строке можно использовать только 0 и 1, где 0 – рабочий день, 1 не рабочий день (например, 0000011 будет обозначать что выходные сб и вс) или номером выходного дня (см ниже). Строка 1111111 не допустима.

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

Все даты должны находится в промежутке с 01.01.1900 г по 31.12.9999г и быть в общем формате или в формате даты, но не в текстовом.

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

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

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

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

ЧИСТРАБДНИ

Синтаксис
ЧИСТРАБДНИ(нач дата;кон дата;праздники)

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

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

ЧИСТРАБДНИ.МЕЖД

Синтаксис
ЧИСТРАБДНИ.МЕЖД(нач дата;кон дата;выходные;праздники)

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

Выходные (необязательный аргумент) – указывает какие дни недели будут считаться выходными. Могут быть выражены строкой, состоящей из 7 знаков, каждый из которых будет обозначать день недели. В строке можно использовать только 0 и 1, где 0 – рабочий день, 1 не рабочий день (например, 0000011 будет обозначать что выходные сб и вс) или номером выходного дня (см ниже). Строка 1111111 не допустима.
Выходные должны быть в тех же форматах, что и для функции РАБДЕНЬ.МЕЖД.

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

Автор AlfiОпубликовано Рубрики ФункцииДобавить комментарий к записи Как посчитать рабочие дни

Как создать формулы в Excel

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

Эта статья относится к Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel для Office 365.

Основы формулы Excel

Написание формулы электронной таблицы отличается от написания уравнения в математическом классе. Наиболее заметным отличием является то, что формулы Excel начинаются со знака равенства ( = ), а не заканчиваются им.

Формулы Excel выглядят как = 3 + 2 вместо 3 + 2 = .

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

Например, если вы введете приведенную выше формулу = 3 + 2 в ячейку и нажмете Enter , результат 5 появится в ячейке. Формула все еще там, но она не появляется в вашей таблице. Если вы выберете ячейку, формула появится в строке формул в верхней части экрана Excel.

Улучшите формулы с помощью ссылок на ячейки

Формулы Excel также могут быть разработаны с использованием ссылок на ячейки . Продолжая наш пример, вы не будете вводить числа 3 и 2 , а вместо этого назовете ячейки, в которые были введены эти числа (см. Использование ссылок на ячейки ниже для получения дополнительной информации об именовании ячеек). Когда вы пишете формулу таким образом, ячейка формулы всегда показывает сумму чисел в этих ячейках, даже если числа меняются.

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

Использование ссылок на ячейки

Каждая ячейка в Excel является частью строки и столбца. Строки обозначены номерами (1, 2, 3 и т. Д.), Показанными вдоль левой стороны электронной таблицы, а столбцы обозначены буквами (A, B, C и т. Д.), Показанными вверху. Чтобы сослаться на ячейку, используйте вместе букву столбца и номер строки, например, A1 или W22 (буква столбца всегда стоит первой). Если у вас выбрана ячейка, вы можете увидеть ее ссылку в верхней части экрана в поле « Имя» рядом со строкой формулы.

На изображении выше обратите внимание на ссылки на ячейки в строке формул: E2 , I2 , M2 и Q2 . Они ссылаются на квартальные цифры продаж для продавца по имени Джин. Формула добавляет эти цифры вместе , чтобы прийти с годовым количеством продаж. Если вы обновите числа в одной или нескольких из этих ячеек, Excel пересчитает их, и результат все равно будет суммой чисел в указанных ячейках.

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

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

  1. Во-первых, вы должны заполнить электронную таблицу данными. Откройте новый файл Excel и выберите ячейку C1, чтобы сделать ее активной ячейкой.

  2. Введите 3 в ячейке, затем нажмите Enter на клавиатуре.

  3. Ячейка C2 должна быть выбрана. Если это не так, выберите ячейку C2 . Введите 2 в ячейке и нажмите Enter на клавиатуре.

  4. Теперь создайте формулу. Выберите ячейку D1 и введите = C1 + C2 . Обратите внимание, что при вводе каждой ссылки на ячейку эта ячейка подсвечивается.

  5. Нажмите Enter, чтобы завершить формулу. Ответ 5 появляется в ячейке D1 .

Если вы снова выберете ячейку D1 , полная формула = C1 + C2 появится в строке формул над рабочим листом.

Введите ссылки на ячейки с указанием

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

  1. Выберите ячейку E1, чтобы сделать ее активной, и введите знак равенства ( = ).

  2. Используйте указатель, чтобы выбрать ячейку C1, чтобы ввести ссылку на ячейку в формуле.

  3. Введите знак плюс ( + ), затем с помощью указателя выберите C2, чтобы ввести ссылку на вторую ячейку в формулу.

  4. Нажмите Enter, чтобы завершить формулу. Результат появится в ячейке E1 .

  5. Чтобы увидеть, как изменение одного из значений формулы влияет на результат, измените данные в ячейке C1 с 3 на 6 и нажмите Enter на клавиатуре. Обратите внимание, что результаты в ячейках D1 и E1 изменяются с 5 на 8 , хотя формулы остаются неизменными.

Математические операторы и порядок операций

Теперь мы переходим к операциям помимо сложения, включая вычитание, деление, умножение и возведение в степень. Математические операторы, используемые в формулах Excel, аналогичны тем, которые вы, возможно, помните из математического класса:

  • Вычитание — знак минус ( — )
  • Дополнение — знак плюс ( + )
  • Разделение — слеш ( / )
  • Умножение — звездочка ( * )
  • Экспонирование — карета ( ^ )

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

  • B ракетки
  • E xponents
  • D IVISION
  • M ultiplication
  • ddition
  • S ubtraction

Excel на самом деле считает деление и умножение одинаково важными. Он выполняет эти операции в порядке их выполнения слева направо. То же самое верно для сложения и вычитания .

Вот простой пример порядка использования операций. В формуле = 2 * (3 + 2) первая операция, выполняемая Excel, выполняется в скобках ( 3 + 2 ) с результатом 5 . Затем он выполняет операцию умножения 2 * 5 с результатом 10 . (Значения в формуле могут быть представлены ссылками на ячейки, а не числами, но Excel будет выполнять операции в том же порядке.) Попробуйте ввести формулу в Excel, чтобы увидеть, как она работает.

Введите сложную формулу

Теперь давайте создадим более сложную формулу.

  1. Откройте новую электронную таблицу и заполните ее данными следующим образом:

    • 7 в ячейке С1
    • 5 в ячейке С2
    • 9 в ячейке С3
    • 6 в ячейке C4
    • 3 в ячейке C5

  2. Выберите ячейку D1, чтобы сделать ее активной, и введите знак равенства, а затем левую скобку ( = ( ).

  3. Выберите ячейку  C2, чтобы ввести ссылку на ячейку в формуле, затем введите знак минуса ( — ).

  4. Выберите ячейку C4, чтобы ввести эту ссылку на ячейку в формулу, затем введите правую скобку ( ) ).

  5. Введите знак умножения ( * ), затем выберите ячейку C1, чтобы ввести эту ссылку на ячейку в формулу.

  6. Введите знак плюс ( + ), затем выберите C3, чтобы ввести эту ссылку на ячейку в формулу.

  7. Введите знак деления ( / ), затем выберите C5, чтобы ввести эту ссылку на ячейку в формулу.

  8. Нажмите Enter, чтобы завершить формулу. Ответ -4 появляется в ячейке D1 .

Как Excel вычислил результат

В приведенном выше примере Excel получил результат -4, используя правила BEDMAS следующим образом:

  • Скобки. Excel сначала выполнил операцию в скобках, C2-C4 или 5-6 с результатом -1 .
  • Экспоненты. В этой формуле нет показателей степени, поэтому Excel пропустил этот шаг.
  • Деление и умножение. В формуле есть две из этих операций, и Excel выполняет их слева направо. Сначала он умножил -1 на 7 (содержимое ячейки C1 ), чтобы получить результат -7 . Затем он выполнил операцию деления, C3 / C5 или 9/3 , для результата 3 .
  • Сложение и вычитание. Последней операцией в Excel было добавление -7 + 3 для окончательного результата -4 .

Функция PI

— служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

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

Описание

Возвращает число 3,14159265358979, математическую константу пи, с точностью до 15 цифр.

Синтаксис

ПИ()

Синтаксис функции PI не имеет аргументов:

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

92)

Данные

Радиус

3

Формула

Описание

Результат

=ПИ()

Возвращает пи.

3.141592654

Площадь круга с радиусом, указанным в A3.

28.27433388

vba — Как переписать формулы Excel в удобочитаемом виде?

Задавать вопрос

спросил

Изменено
3 года, 2 месяца назад

Просмотрено
17 тысяч раз

У меня есть файл Excel с формулами следующим образом:

=ЕСЛИ(ИЛИ(ЕОШИБКА(G16),ЕОШИБКА(G17)),X16,ЕСЛИ(ИЛИ(G16="xxx",G16="yyy", G16="zzz"),Y16,IF(G16="333","Н\Д",IF(G17="333",Z16,IF(D17="",IF((HEX2DEC(W$10)-HEX2DEC (W16))/ВПР(F16,$M$36:$N$41,2,ЛОЖЬ)<0,0,(HEX2DEC(W$10)-HEX2DEC(W16))/ВПР(F16,$M$36:$N$41 ,2,ЛОЖЬ)), ЕСЛИ((HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W17)-HEX2DEC( W16))/ВПР(F16,$M$36:$N$41,2,ЛОЖЬ)))))))

Я хотел бы упростить их, чтобы они были написаны более читабельно.

  • Могу ли я редактировать/записывать формулы Excel с отступом?
  • Какие упрощения я могу сделать?
  • Должен ли я использовать сценарий VBA вместо формул Excel?
  • vba
  • excel
  • excel-формула

8

Вы можете использовать Alt+Enter в строке формул, чтобы сделать вашу формулу многострочной. К сожалению, нет вкладок, только пробелы, поэтому создавать и редактировать становится утомительно. См. также

Excel Formula Formatter

1

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

В качестве примера, используя вспомогательные столбцы, вы можете сократить формулу следующим образом: N$41,2,FALSE)

[B1] =HEX2DEC(W$10)

[C1] =HEX2DEC(W16)

[D1] =HEX2DEC(W17)

тогда большая формула сокращается до

=ЕСЛИ(ИЛИ(ЕОШИБКА(G16),ЕОШИБКА(G17)),X16,ЕСЛИ(ИЛИ(G16="xxx ",G16="yyy",G16="zzz"),Y16,IF(G16="333","Н\Д",IF(G17="333",Z16,IF(D17="",IF( (B1-C1)/A1<0,0,(B1-C1)/A1), ЕСЛИ((D1-C1)/A1<0,0,(D1-C1)/A1)))))

Это особенно эффективно при использовании изменчивых функций, таких как ДАТА или СЕЙЧАС, которые вы не хотите пересчитывать для каждой ячейки, если это один и тот же результат.

Является ли это более читабельным, возможно, нет, но вы можете пометить заголовки столбцов соответствующими комментариями

3

FormulaDesk — это бесплатная надстройка Excel, которая делает сложные формулы более читабельными и понятными без необходимости их переписывать. Это значительно упрощает создание, редактирование, отладку и понимание формул. Он имеет два режима: «Редактировать вид» и «Исследовать вид», которые можно переключать.

  • Представление «Редактировать» — это расширенный редактор формул с Intellisense и т. д. Он форматирует по мере ввода, смещая вложенные элементы по вертикали для ясности и понятности.

  • Представление «Исследование» представляет формулу в простом вложенном/свернутом виде, сначала с простейшим представлением формулы верхнего уровня, но с возможностью перехода к вложенным выражениям. Это позволяет вам быстро понять, как/почему он возвращает текущий результат. Щелкните зеленые столбцы (свернутые результаты), чтобы развернуть их. Либо нажмите кнопки «Развернуть все», «Свернуть все».

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

  • Есть немало других возможностей.

[Раскрытие информации: я автор FormulaDesk]

0

Сочетание вспомогательных столбцов и именованных диапазонов сделало бы эту формулу довольно простой.

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

Обратите внимание, что «prices» — это имя для диапазона A2:A7, а «inflated_prices» — это имя для B2:B7.

Заметьте также, что имена интеллектуальны: sum(prices) суммирует весь диапазон, тогда как =+prices*2 в B2 преобразуется в =+A2*2 , =+prices*2 в B3 разрешается в =+A3*2 и так далее.

3

Вы можете существенно упростить формулу, сохранив при этом единую формулу. Вы повторяете почти одно и то же выражение 4 раза с помощью HEX2DEC/VLOOKUP 9.0134, который можно свести к одному экземпляру, если вы узнаете, что это

=ЕСЛИ(формула<0,0,формула)

..... эквивалентно

=МАКС(0,формула)

[для числовых результатов формулы]

и если вы вложите свое выражение IF(D17="".....в основную формулу, т.е. эту версию

=IF(ISERROR(G16&G17),X16,IF(OR (G16={"xxx","yyy","zzz"}),
Y16,IF(G16="333","Н\Д",IF(G17="333",Z16,MAX(0,(HEX2DEC(IF(D17="",W$10,W17))-HEX2DEC(W16 ))/ВПР(F16,$M$36:$N$41,2,0)))))

Поскольку вы спросили о коде VBA, я решил попробовать. Это, безусловно, более понятно и, следовательно, удобнее в сопровождении, однако функция имеет 11 аргументов, поэтому она немного громоздка.

 Функция Magic(d17 As Range _
                , f16 Как Диапазон _
                , g16 Как Диапазон _
                , g17 как диапазон _
                , w10 как диапазон _
                , w16 Как диапазон _
                , w17 Как диапазон _
                , x16 как диапазон _
                , y16 как диапазон _
                , z16 Как Диапазон _
                , m36 как диапазон) как вариант
    Затемнить как вариант
    Dim b как вариант
    Если IsError(g16. Value) Или IsError(g17.Value) Тогда
        Магия = x16.Значение
        Выход из функции
    Конец, если
    Если g16.Value = "xxx" Или g16.Value = "yyy" Или g16.Value = "zzz" Тогда
        Магия = y16.Значение
        Выход из функции
    Конец, если
    Если g16.Value = "333" Тогда
        Магия = "Н\Д"
        Выход из функции
    Конец, если
    Если g17.Value = "333" Тогда
        Магия = z16.Value
        Выход из функции
    Конец, если
    Если d17.Value = "" Тогда
        a = Application.WorksheetFunction.Hex2Dec(w10.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        a = a/Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        Если а < 0 Тогда
            Магия = 0
            Выход из функции
        Еще
            Магия = а
            Выход из функции
        Конец, если
    Еще
        b = Application.WorksheetFunction.Hex2Dec(w17.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        b = b/Application. WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        Если б < 0 Тогда
            Магия = 0
            Выход из функции
        Еще
            Магия = б
            Выход из функции
        Конец, если
    Конец, если
Конечная функция

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

0

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

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

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

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

FormulaDesk работает только в Windows.

В дополнение к приведенному выше FormulaDesk:

Вот ссылка на текущую бета-версию FastExcel 3. Но они, похоже, существуют уже более десяти лет. 29 баксов. Я думаю только окна. Сайт непонятный.

Making sense of complex Formulas: an Indenting Viewer-Editer

Аналогичные функции Precision Calc. 12 долларов. Имеет бесплатную версию Nagware, если она вам нужна только время от времени.

Читайте также: