Как создавать пользовательские функции Excel с помощью VBA 2021

Главная Коллекция «Revolution» Программирование, компьютеры и кибернетика Использование стандартных функций Microsoft Excel

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

Рубрика Программирование, компьютеры и кибернетика
Вид реферат
Язык русский
Дата добавления 05.10.2014
Размер файла 231,2В K

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д. PPT, PPTX и PDF-файлы представлены только в архивах. Рекомендуем скачать работу.

Функция ЧАСТОТА в Excel

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

Ниже представлена ​​формула ЧАСТОТЫ в Excel.

image

Аргументы, используемые для формулы ЧАСТОТА в Excel.

  • Data_array     Обязательный. Массив или ссылка на набор значений, для которых нужно подсчитать частоты.
  • Bins_array     Обязательный. Массив или ссылка на интервалы, в которые должны быть сгруппированы значения в data_array .

Объяснение функции ЧАСТОТА в Excel

Частота возвращает массив значений и, следовательно, ее необходимо ввести как формулу массива, то есть нажать CTRL + Shift + Enter (или Command + Shift + Enter для Mac). Ячейки, в которые требуется вывод, эти ячейки должны быть выбраны в первую очередь, а затем вводится формула ЧАСТОТА в excel, после чего она вводится как формула массива.

Выберите ячейки à Введите формулу à Нажмите CTRL + Shift + Enter.

Возврат

ЧАСТОТА Функция в Excel возвращает частотное распределение data_array в интервалах bins_array . Результат всегда на единицу больше, чем количество элементов в bins_array. Дополнительный элемент в возвращаемом массиве соответствует количеству значений, превышающих самый высокий элемент bins_array . Предположим, что bins_array содержит три элемента {2, 4, 6}, функция вернет четыре элемента {6}.

Если массив данных _ не содержит значений, функция ЧАСТОТА Excel возвращает массив нулей. Если bins_array не содержит значений, функция ЧАСТОТА Excel возвращает общее количество элементов, указанных в data_array .

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

ЧАСТОТА в Excel — Иллюстрация

Предположим, у вас есть числа, для которых вы хотите рассчитать частоту. Цифры {1, 3, 2, 4, 6, 2, 3, 4, 5} приведены в B3: B11.

image

Номера должны быть разбиты на интервалы: {2, 4, 6}, указанные в D3: D5.

Чтобы вычислить частоту, сначала выберите четыре ячейки E3: E6, а затем следующий синтаксис:

= ЧАСТОТА (B3: B11; D3: B5)

и нажмите CTRL + Shift + Enter.

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

Он вернет частоту.

Данный выход {3, 4, 2, 0} соответствует интервалу {6}.

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

Как использовать функцию ЧАСТОТА в Excel?

Функция ЧАСТОТА в Excel очень проста и удобна в использовании. Давайте разберемся с работой FREQUENCY in excel на некоторых примерах.

Вы можете скачать этот шаблон Excel с функцией ЧАСТОТА здесь — Шаблон Excel с функцией ЧАСТОТА

Пример # 1

Предположим, вы провели опрос и собрали данные о высоте, как показано ниже.

Теперь вы хотите рассчитать частоту роста в следующих интервалах:

155–160

160–165

165–170

> 170

Интервалы {155, 160, 165, 170} приведены в E4: E7.

Чтобы вычислить частоту, сначала выберите пять последовательных ячеек (4 + 1).

Затем введите следующий синтаксис:

= ЧАСТОТА (B4: B14; E4: E7)

и нажмите CTRL + Shift + Enter.

Он вернет частоту.

Пример # 2

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

Теперь все те, кто не прошел (по одному или нескольким предметам), будут считаться «Неудачниками». Теперь вам нужно знать количество студентов, которые проиграли.

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

= СУММ (- (ЧАСТОТА (B4: B9; B4: B9)> 0))

Он вернет 4.

Давайте подробно рассмотрим синтаксис:

ЧАСТОТА (B4: B9, B4: B9) вычислит частоту данных B4: B9 с использованием интервала B4: B9. Он вернет {1; 1; 2; 0; 2; 0; 0}

FREQUENCY (B4: B9, B4: B9)> 0 проверяет, больше ли полученная частота нуля. Он возвращает логическое ИСТИНА, если оно больше нуля, иначе ЛОЖЬ. Он вернет {ИСТИНА; ПРАВДА; ПРАВДА; ЛОЖНЫЙ; ПРАВДА; ЛОЖНЫЙ; ЛОЖНЫЙ}

СУММ (- (ЧАСТОТА (..)> 0)) затем суммирует ИСТИНА и возвращает количество уникальных значений.

Пример # 3

Предположим, у вас есть данные о клиентах, посещенных в супермаркете за день, а также время их посещения в ячейках B4: C20, как показано ниже.

Теперь вы хотите узнать, в какие промежутки времени клиенты чаще всего посещали магазин. Это поможет вам эффективно спланировать рабочее время сотрудников. Магазин открывается в 11:00 и закрывается в 20:00.

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

  • 11:00 УТРА
  • 12:00 УТРА
  • 1:00 ВЕЧЕРА
  • 14:00
  • 3:00 ВЕЧЕРА
  • 4:00 ВЕЧЕРА
  • 5:00 ВЕЧЕРА
  • 18:00
  • 7:00 ВЕЧЕРА
  • 8:00 ВЕЧЕРА

Теперь выберите ячейки в частотной таблице, чтобы получить. G4: G13 в данном случае. Поскольку магазин закрывается в 20:00, мы не выбираем ячейку для> 20:00, так как во всех случаях она будет равна нулю.

Теперь введите следующий синтаксис:

= ЧАСТОТА (B4: C39, G4: G13)

и нажмите CTRL + Shift + Enter.

Он вернет частоту посещения магазина покупателем. В этом случае большинство посещений приходилось на период с 17:00 до 18:00.

  • Формула ЧАСТОТА в Excel дает частотное распределение заданных данных ( data_array ) в заданные интервалы ( bins_array ).
  • Формула ЧАСТОТА в Excel вводится как формула массива. Выбирается диапазон соседних ячеек, в которых должно появиться распределение. Чтобы ввести формулу ЧАСТОТА в Excel, вам нужно нажать CTRL + Shift + Enter (или Command + Shift + Enter для Mac)
  • Для x элементов в массиве bins_array убедитесь, что выбрано x + 1 количество ячеек при вводе формулы ЧАСТОТА в Excel. Дополнительная ячейка возвращает количество значений в data_array , которое больше третьего значения интервала.
  • Он игнорирует пустые ячейки и текст.

Все формулы в Excel начинаются со знака равенства «=». Они оперируют с числами, текстом, названиями ячеек, функциями. Главные отличия от обычных математических формул – формула в Excel задается одной строкой, вместо переменных используются названия ячеек. Для вычислений применяются следующие операции:

  • сложение – «+»;
  • вычитание – «-»;
  • умножение – «*»;
  • деление – «/»;
  • возведение в степень – «^».

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

=2+6/3

=B1-B3

=A1+1000

=(1+3)/(2+2)

Пример простейших вычислений:

Применение функций

Для автоматизации расчетов применяются разнообразные функции. Их можно вставлять нажатием кнопки «Вставить функцию». Альтернативный вариант – нажатие комбинации Shift+F3 (для ноутбуков Shift+Fn+F3). Появляется диалоговое окно, в котором надо выбрать категорию. Далее определяется конкретная функция, задаются ее аргументы, нажимается «ОК».

Вот пример пошагового вычисления квадратного корня числа. Вызвали диалоговое окно, выбрали раздел «Математические», далее «КОРЕНЬ»:

Задали аргумент (в данном случае это B1):

Нажали «ОК»:

Математические вычисления

Одна из самых востребованных математических функций – СУММ, она предназначена для суммирования значений ячеек. СУММ может работать с целым диапазоном:

=СУММ(B1:B3)

Здесь сложили B1, B2 и B3.

Также применяется суммирование отдельных ячеек:

=СУММ(B1;B3)

А здесь сложили B1 и B3.

Аналог СУММ – ПРОИЗВЕД, позволяет перемножать значения. Пример перемножения диапазона:

=ПРОИЗВЕД(B1:B3)

В категории «Математические» также представлены средства для тригонометрических вычислений, для работы с целыми числами, для округления дробей.

Логические функции

В разделе «Логические» есть средства для работы с логическими значениями. Самый простой вариант – присвоить ячейке значение ИСТИНА или ЛОЖЬ:

=ИСТИНА()

=ЛОЖЬ()

  Как и где используют функцию «Подбор параметра» в Excel

Можно инвертировать содержимое:

=НЕ(B1)

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

ЕСЛИ(логическое_выражение;значение1;значение2)

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

  • равно – «=»;
  • больше – «>»;
  • меньше – «<»;</li>
  • больше или равно – «>=»;
  • меньше или равно – «<=»;</li>
  • не равно – «<>».

Примеры операций сравнения: 2<3, B1<>B4, F5>=10.

Пример использования ЕСЛИ:

=ЕСЛИ(2>=1;10;20)

Обработка текста

В Excel имеются средства и для несложных операций с текстовыми величинами. ДЛСТР возвращает длину текстового аргумента, например, =ДЛСТР(«Волга впадает в Каспийское море») даст результат 31.

НАЙТИ осуществляет поиск одного текста в другом и возвращает номер позиции первого вхождения. Если ввести =НАЙТИ(«ас»;»Василий»;1), то получим 2.

ПОДСТАВИТЬ заменяет в тексте один фрагмент другим. =ПОДСТАВИТЬ(«Все нормально!»;»е»;»ё») даст результат «Всё нормально!». Если в качестве третьего аргумента указать пустую строку, то фрагмент будет просто удален из всего текста.

Чтобы объединить несколько строк в одну, можно использовать СЦЕПИТЬ. =СЦЕПИТЬ(«Добрый «;»день») создаст небольшую фразу из двух слов.

Дата и время

В Excel много удобных средств для обработки времени и дат. В приведенном ниже примере в A1 поместили текущую дату с помощью формулы =СЕГОДНЯ(), потом разбили ее на составные части. Для этого применили конструкции =ДЕНЬ(A1), =МЕСЯЦ(A1), =ГОД(A1). Результат:

Чтобы получить текущее время, наберите =ТДАТА(), затем измените формат ячейки правой кнопкой мыши («Формат ячеек…» -> «Число» -> «Время»), выберите удобное представление. Из текущего времени также можно выделить составные части (используя СЕКУНДЫ, МИНУТЫ, ЧАСЫ).

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

Использование программы Microsoft Excel давным-давно стало обязательным во многих отраслях нашей жизни. Будь вы студентом, математиком, бухгалтером, вам в любом случае понадобится данная программа. При устройстве на работу умение пользоваться офисными программами является обязательным практически для любой должности. Сегодня мы разберём функции в Excel. Считайте данную публикацию небольшим вводным уроком на данную тему.

Определение

Так что же такое функции в Excel? Если говорить кратко, то это выражения, способные заменить целый ряд математических и логических операций. Они предназначены для упрощения вычислений и для создания массивных формул. Все функции разделены и упорядочены по категориям, в зависимости от их назначения. Несомненным плюсом является то, что даже если вы не знаете, как именно пишется функция и в каком порядке необходимо поставить аргументы, программа сама вам подскажет, что именно и как должно быть написано. Если же вы примерно помните, как звучат функции в Excel, то можете воспользоваться поиском. Для этого нажмите на «Вставка», затем «Функция» и в строке поиска введите первые буквы.

Основы

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

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

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

  1. СУММ (число1; число2; …) — данное выражение возвращает готовую сумму всех аргументов, перечисленных за скобкой. Конечно, в обычной ситуации это не очень удобно, но вместо чисел вы можете указать отдельные ячейки таблицы или целые блоки. Для этого зажмите Ctrl и щелкайте указателем мышки по нужным ячейкам. Или же, зажав Shift, растяните рамку на нужный вам диапазон.
  2. МАКС (значение1; значение2; …) — вернет вам максимальное значение среди всех чисел под скобкой. Синтаксис такой же, как и в предыдущем случае.

Все функции в Excel разделены по подгруппам, чтобы их было проще искать пользователю. Поскольку их слишком много, мы не будем приводить весь список, но учтите, что зачастую их можно запросто найти через поиск, ведь пишутся они всего двумя способами: либо так же как в математике (например, Log, Cos), либо по описанию команды (пример: ОКРУГЛ (число; количество_разрядов)).

Условия

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

Итак, у нас есть ячейка А1=ЕСЛИ(В1<С1;»Истина»;»Ложь»). Что мы видим? Первым идет логическое выражение. Это условие, от которого зависит, что же будет напечатано в ячейке. Далее «истина» — то значение, которое примет ячейка в случае, когда логическое выражение верно. Как нетрудно догадаться, «ложь» — это обратное значение, принимаемое ячейкой при неверности логического выражения.</p>

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

Итог

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

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

Выясняем назначение функции если в MS Ecxel и разбираемся в работе оператора с несколькими или одним условием.

image

image

Ставим курсор в ячейку напротив первого участника таблицы — у нас это D4. Вводим «=» и выбираем функцию ЕСЛИ в списке над таблицей.

image

Откроется новое окно, в котором надо задать параметры функции.

image

Логическое выражение — любое условие, при выполнении которого можно получить ответ «да» или «нет», что на языке Экселя обозначается как «ложь» и «истина». В нашем случае мы сравниваем возраст человека с числом 18, чтобы определить, взрослый это или ребенок. Значит, в поле «Лог_выражение» следует записать формулу «С4 (ячейка с возрастом) >=18». В графе «Значение_если_истина» пишем «Взрослый», а в «Значение_если_ложь» — «Ребенок». Все буквенные обозначения должны быть в кавычках.

image

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

image

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

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

image

Новая функция проверяет, есть ли в ячейке слово «Взрослый». Если да — она возвращает значение 500, если нет — 200.

image

Изучаем Эксель: функция ЕСЛИ с несколькими условиями

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

Предположим, надо поделить участников таблицы не на две категории, а на три — «Ребенок», «Взрослый» и «Пожилой.» Для этого уже недостаточно простой проверки «больше или равно 18» — а значит, надо ввести еще одно условие: в нашем случае это будет «больше 60».

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

В MS Excel 2010 таким образом можно вкладывать друг в друга до семи условий.

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

Поделиться ссылкой

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий