Как в экселе установить формулу: Функции Excel (по категориям)

Основные принципы работы с датами и временем в Excel

11448
27.10.2012
Скачать пример

Видео

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

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

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т. д.) и задается через контекстное меню — правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):

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

16:45

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть 

27.10.2012 16:45

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY):

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27. 10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!  😉




• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

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

Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис — Надстройки (Tools — Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

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

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).  

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

• Как вычислять возраст (стаж) в полных годах-месяцах-днях




• Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.




• Автоматическое добавление текущей даты в ячейку при вводе данных.




• Как вычислить дату второго воскресенья февраля 2007 года и т.п.

Получить формулу ячейки

Активити Получить формулу ячейки позволяет извлекать формулы из ячеек при работе с таблицами в Microsoft Excel.

Чтобы открыть окно настроек, нажмите на активити на графической модели процесса.

Вкладка «Параметры»

На вкладке Параметры отображаются основные параметры активити.

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

Путь до файла Excel — расположение файла Excel в файловой системе. При указании пути можно использовать контекстные переменные процесса, в которых хранится путь до файла или часть этого пути. Нажмите и укажите переменную. Её можно выбрать из списка или добавить новую, нажав Создать параметр. Для выбора доступны переменные типа Строка. Значение добавляется в формате {$наименование переменной}. Подробнее о создании контекстных переменных читайте в статье «Контекст процесса».

Способ получения листа — выберите способ определения листа в файле Excel, из которого нужно извлечь формулу:

• По номеру — в поле Номер листа нужно указать порядковый номер листа в файле Excel;
• По имени — в поле Название листа нужно указать название листа в файле Excel.

Считать несколько — возможность извлечь формулы сразу из нескольких ячеек в таблице.

Адрес ячейки/ячеек — введите адрес ячейки, из которой нужно извлечь формулу, или диапазон таблицы, если нужно извлечь формулы из нескольких ячеек. Например, B2 или A1:C3.

Когда вы указываете номер, название листа или адрес ячейки, можно использовать значения контекстных переменных процесса. Нажмите и укажите переменную. Её можно выбрать из списка или добавить новую, нажав Создать параметр. Для выбора доступны переменные типа Строка. Значение добавляется в формате {$наименование переменной}.

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

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

Вкладка «Обработчики»

О вкладке Обработчики можно прочитать в статье «Общие принципы настройки активити».

get-cell-value.html




replace-cell-value.html

Была ли статья полезной?

ДаНет

Выберите вариантРекомендации не помоглиТекст трудно понятьНет ответа на мой вопросСодержание статьи не соответствует заголовкуДругая причина

Нашли опечатку? Выделите текст, нажмите ctrl + enter и оповестите нас

Создание формул

Excel для Интернета

Офис для веб-обучения

Excel для Интернета

Excel для Интернета

Создание формул

• Знакомство с Excel для Интернета
  

  видео

• Создание формул
  

  видео

• Вставить сводную таблицу
  

  видео

• Условное форматирование
  

  видео

• Мгновенное заполнение
  

  видео

• Получите ценную информацию с Анализом данных
  

  видео

• Сотрудничать
  

  видео

Следующий:

PowerPoint для Интернета

Попробуйте!

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

1. Выберите ячейку.

2. Введите знак равенства =.

3. Введите или выберите ячейку или введите функцию, например СУММ .

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

5. Нажмите Enter для получения результата. Результат отображается в ячейке, но формулу всегда можно увидеть в строке формул .

Динамические массивы

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

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

2. Например, введите =SORTBY(A2:B15,B2:B15,1) .

3. Нажмите Enter для получения результатов.

   Примечание. Формулы массива нельзя вводить, если что-то блокирует выходной диапазон. В этом случае Excel вернет ошибку #SPILL! Ошибка , указывающая на наличие блокировки. Если вы удалите блокировку, формула выльется, как и ожидалось.

Хотите больше?

Обзор формул в Excel

Функции Excel (по категориям)

Динамические массивы и поведение разлитого массива

Справка по Excel и обучение

Быстрый старт Office для Интернета

Как создать пользовательскую формулу в Excel (пошаговое руководство) —

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

Скачать практическую рабочую тетрадь

Создайте пользовательскую формулу в Excel

Шаг 1. Включите параметр разработчика, чтобы открыть окно VBA в Excel

Шаг 2. Напишите коды VBA для создания пользовательской формулы

Шаг 3. Примените пользовательскую формулу в электронной таблице Excel

Быстрые заметки

Вывод

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

Загрузить рабочую тетрадь

Загрузите это учебное пособие, чтобы выполнить задание, пока вы читаете эту статью.

Создание пользовательской формулы в Excel

Рассмотрим пример, в котором вам нужно создать пользовательскую формулу, чтобы узнать общую стоимость ваших товаров , указанных в наборе данных. Excel позволяет нам создавать собственные пользовательские функции, используя коды VBA . Эти пользовательские функции в Excel известны как пользовательских функций (UDF) 9.0113 . Они позволяют вам создавать свои собственные пользовательские функции для выполнения практически любого типа операций. В этом разделе мы рассмотрим пошаговое руководство по его созданию. Давай сделаем это!

Шаг 1. Включите параметр разработчика, чтобы открыть окно VBA в Excel

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

• Нажмите на Настраиваемую панель быстрого доступа Из доступных вариантов нажмите Дополнительные команды.

• Окно параметров Excel открывается. Нажмите Настроить ленту .
• Теперь выберите параметр Developer , чтобы создать эту ленту. Нажмите OK , чтобы продолжить.

• Теперь на листе Excel появилась новая лента с именем Developer .

• Выберите ленту разработчика . Щелкните Macros , чтобы открыть VBA 9.0113
• Или вы можете просто нажать « Alt+F11 », чтобы сделать это.

Шаг 2. Напишите коды VBA для создания пользовательской формулы

• В окне VBA нажмите Вставить .
• Из доступных вариантов щелкните Модуль , чтобы создать модуль. Мы запишем наши коды VBA в модуль.

• Запишите коды VBA , чтобы создать собственную формулу. Чтобы найти Общая цена для данных предметов, коды VBA ,

 Общественная функция  TOTALPRICE (номер 1, номер 2)
ОБЩАЯ ЦЕНА = (число1 * число2)
Завершить функцию 

• Нам нужно объявить коды VBA как функцию. Вот почему этот код начинается с объявления функции и заканчивается End Function

.

• Формуле необходимо имя. Мы назвали его TOTALPRICE

.

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

ОБЩАЯ ЦЕНА = (число1 * число2)

• Закройте окно VBA и вернитесь на главный рабочий лист.

Шаг 3. Применение пользовательской формулы в электронной таблице Excel

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

• Вставьте значения в формулу. Окончательная формула:

=ОБЩАЯ ЦЕНА(C4,D4)

• Где C4 и D4 — это Склад и Цена за единицу

• Нажмите Введите , чтобы получить результат.

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

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

• Откройте окно VBA и перейдите к модулю , следуя процедурам, которые мы обсуждали ранее.
• Запишите VBA Код VBA для пользовательской формулы:

 Общедоступная функция RETAILPRICE(номер1, номер2,номер3)
РОЗНИЧНАЯ ЦЕНА = (число1 + число2)/число3
Завершить функцию 

• Теперь закройте окно VBA и перейдите на главный рабочий лист. В Cell F4 найдите нашу новую индивидуальную функцию RETAILPRICE .
• Дважды щелкните по нему, когда найдете.

• Вставьте значения в формулу, и окончательная форма:

= РОЗНИЧНАЯ ЦЕНА (C4, D4, E4)

• Где C4, D4, E4 — это Price1, Price2, и Делитель

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

Подробнее: Как создать формулу в Excel для нескольких ячеек (9методы)

Краткие заметки

👉 Вы не можете Записывать настраиваемую формулу, как макрос Excel.

👉 Создание пользовательской формулы имеет больше ограничений, чем обычные макросы VBA.