В чем особенность необязательных аргументов функции excel

Как в Excel функции использовать: несколько простых правил

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

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

Вы можете «вкладывать» одну функцию в другую, копировать формулы с функциями (не забываем о разных типах ссылок). Главное – чётко понимать, как работает функция, иначе она может дать неверный результат или ошибку. Вы можете и не заметить этого. А чтобы разобраться в работе функций – читайте мои посты о различных функциях и справку Microsoft.

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

Аргументы функции Excel

Аргументы функций – это исхоные данные для расчета функции. Например, для функции СУММ (суммирование) – это перечень чисел, ячеек или диапазонов ячеек для суммирования. Аргументы указываются в скобках после имени функции и разделяются точкой с запятой (в англоязычной версии — запятой). По количеству аргументов, функции могут быть:

• Без аргументов – не нуждаются в аргументах для расчета. Например, =ПИ() – возвращает число 3,1428.
• С одним аргументом – нужно ввести всего один аргумент. Например =СТРОЧН(А1) – переведёт в строчный вид все символы в ячейке А1.
• С несколькими аргументами – нужно ввести определенное число аргументов, больше одного. Например, функция =ПСТР(А1;1;10) вернёт первые 10 символов из строки в ячейке А1.
• С необязательными аргументами – функция имеет аргументы, которые указывать необязательно. Например, =ВПР(«Иванов»;А1:В30;2;0) будет искать фамилию «Иванов» в диапазоне А1:В30 и вернёт информацию о нём. Последний аргумент здесь – «Интервальный просмотр» — обозначает способ поиска, его указывать необязательно.
• С переменным количеством аргументов – количество аргументов может изменяться. Например, =СРЗНАЧ(А1;В3:В15;С2:F2) – посчитает среднее значение цифр в указанных диапазонах. Перечисляя ячейки через точку с запятой, вы можете задать разное количество аргументов.

Как вставить функцию в Excel

Чтобы вставить функцию в формулу, можно воспользоваться одним из перечисленных способов:

1. Если вы знаете имя функции – начните его записывать с клавиатуры, инструмент «Автозаполнение» предложит варианты функций, выберите в списке нужную, нажмите TAB и вводите аргументы;
   Автозаполнение при ручном вводе функции
2. Еще один способ, если вы знакомы с перечнем функций – выбрать на ленте: Формулы – Библиотека функций . В этой группе собраны функции по типам: логические, текстовые, математические и др. Открыв нужную группу – выберите функцию и перейдите к заполнению аргументов.
   Выбор функции на ленте

Если имя функции вам неизвестно, воспользуйтесь окном Вставка функции . Чтобы его вызвать – попробуйте один из способов:

1. Нажмите комбинацию клавиш SHIFT+F3
2. Выполните на ленте Формулы – Библиотека функций – Вставить функцию
3. Нажать на значок fx слева от строки формул
   Окно «Вставка функции»

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

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

Окно» Аргументы функции»

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

Вот и всё о вставке функций на лист, а в следующей статье мы начнём рассматривать текстовые функции. До встречи на страницах блога OfficeЛЕГКО.com!

Аргумент функции

Аргумент функции — это конкретный вход для функции. Например, функция ВПР принимает четыре следующих аргумента:

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

Наконец, многие функции Excel принимают несколько необязательных аргументов, которые обозначаются многоточием (…)

Например, функция СЧЁТЕСЛИМН принимает несколько и необязательных пар диапазонов и критериев, которые могут быть представлены следующим образом:

Это означает, что при желании вы можете добавлять дополнительные аргументы парами: диапазон3 / критерии3, диапазон4 / критерии4 и т. Д.

Синтаксис функции

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

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

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

– функции без аргументов: =СЕГОДНЯ() или ПИ();

– функции с одним аргументом: =ПРОПНАЧ(текст) или SIN(число);

– функции с фиксированным числом аргументов:

– функции с неопределенным числом аргументов: =СРЗНАЧ(число1;число2;…;числоn);

– функции с необязательными аргументами:

=ВПР( искомое_значение;табличный_массив;номер_индекса_столбца ;интервальный_просмотр).

Категории функций

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

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

Дата и время. Выполняют операции с данными, содержащими время и даты.

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

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

Ссылки и массивы. Вычисляют ссылки и массивы, значение диапазона, создают гиперссылки для сетевых и Web-документов.

Текстовые . Выполняют операции с текстовыми данными. Преобразуют текстовые строки в верхние и нижние регистры, объединяют строки, удаляют пробелы и т. д.

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

Вставка функции в формулу

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

Для вставки функции, запустите Мастер функций одним из приведенных ниже способов.

– Выполните команду меню Вставка→Функция .

– Щелкните на кнопке Вставка функции , которая находится на стандартной панели инструментов.

– Нажмите комбинацию клавиш <Shift+F3> .

В первом окне Мастера функций, в списке Категория представлены все категории функций. При выборе:

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

— в категории 10 недавно использовавшихся перечислены те функции, которые недавно использовали.

— в категории Полный алфавитный перечень включены все имеющиеся функции из всех категорий. Используйте этот список, если вы знаете, имя функции, но вам не известно, к какой катего-

рии она относится.

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

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

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

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

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

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

вое окно Мастер функций .

Советы по вводу функций

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

– Чтобы получить дополнительную справку о функции, выбранной в окне Мастер функций, щелкните на кнопке Справка по этой функции, которая расположена в нижней части окна.

– Функции можно вставлять в уже существующие формулы. Для этого выделите ячейку с формулой и перейдите в режим редактирования. Установите курсор в место вставки формулы. Затем откройте диалоговое окно Мастер функций и выберите необходимую функцию.

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

– Если вы находитесь в окне Мастер функций или Аргументы функции и передумали вставлять функцию, просто щелкните на кнопке Отмена или нажмите клавишу <Esc>.

– Количество полей в окне Аргументы функции зависит от количества аргументов выбранной функции. Если функция не содержит аргументов, то не будет ни одного поля. Если же функция содержит переменное число аргументов(как, например, в функции СРЗНАЧ), то по мере ввода аргументов Excel будет отображать дополнительные поля.

– После ввода каждого аргумента справа от поля ввода будет показано его текущее значение.

– Некоторые функции, например, ИНДЕКС , имеют два или больше списков аргументов(иными словами, имеют разные синтаксические формы). Если вы выберете такую функцию, то Excel отобразит дополнительное диалоговое окно, в котором нужно указать, какую форму вы будете использовать.

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

20.Обработка списков в Microsoft Excel: сортировка, фильтрация, создание итоговых таблиц. Списки и базы данных Microsoft Excel

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

– список целиком помещается на одном рабочем листе, максимальный размер списка – 65 536 строк и 256 столбцов;

– список должен оделяться от других данных рабочего листа хотя бы одним незаполненным столбцом и/или строкой;

– имена столбцов списка располагаются в первой строке таблицы, имена могут быть многострочными;

Microsoft Excel/Функции в Microsoft Excel

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

Все функции в Excel характеризуются:

• Названием;
• Предназначением (что, собственно, она делает);
• Количеством аргументов (параметров);
• Типом аргументов (параметров);
• Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

• Название: СТЕПЕНЬ;
• Предназначение: возводит указанное число в указанную степень;
• Количество аргументов: РАВНО два (ни меньше, ни больше, иначе Excel выдаст ошибку!);
• Тип аргументов: оба аргумента должны быть числами, или тем, что в итоге преобразуется в число. Если вместо одного из них вписать текст, Excel выдаст ошибку. А если вместо одно из них написать логические значения «ЛОЖЬ» или «ИСТИНА», ошибки не будет, потому что Excel считает «ЛОЖЬ» равно 0, а истину — любое другое ненулевое значение, даже −1 равно «ИСТИНА». То есть логические значения в итоге преобразуются в числовые;
• Тип возвращаемого значения: число — результат возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячейке и нажать Enter, в ячейке будет число 1024. Здесь 2 и 10 — аргументы (параметры), а 1024 — возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

Синтаксис записи функции [ править ]

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

Общий синтаксис записи любой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

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

• Некоторые функции вообще не принимают аргументов. Например, функция ПИ() просто возвращает в ячейку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Однако, даже если функция не принимает аргументов, пустые круглые скобки писать обязательно, иначе Excel выдаст ошибку!

• Некоторые функции принимают РОВНО ОДИН аргумент. Например функции sin(число), cos(число) и т. п.

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

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

Ввод функций вручную [ править ]

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

К недостаткам набора формул вручную можно отнести:

• Вручную ставить «=»;
• набирать имя функции;
• открывать/закрывать круглые скобки;
• расставлять точки с запятой;
• следить за порядком вложенности
• заключать текст в двойные кавычки;
• не иметь возможность посмотреть промежуточные расчеты;
• и т. п.

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

Ввод функции с помощью кнопки «сигма» [ править ]

Одно из средств облегчить и ускорить работы с функциями — кнопка на панели инструментов «Стандартная». В ней разработчики Microsoft «спрятали» пять часто используемых функций:

• СУММ(минимум один, максимум 30 аргументов). Суммирует свои аргументы.

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

• СРЗНАЧ(минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов;
• СЧЁТ(минимум один, максимум 30 аргументов). Подсчитывает количество чисел в списке аргументов (используется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются);
• МАКС(минимум один, максимум 30 аргументов). Возвращает максимальный аргумент;
• МИН(минимум один, максимум 30 аргументов). Возвращает минимальный аргумент.

1. активизируем ячейку, где должен быть результат (просто щелкаем);
2. Нажимаем на стрелочку справа от кнопки «сигма»;
3. Выбираем нужную функцию. После выбора Excel сам вставит знак «=», имя функции, круглые скобки, и даже попытается угадать диапазон, который мы ходим выделить (хотя он редко угадывает);
4. Выделяем связный диапазон ячеек. Excel вставит в круглые скобки адрес выделенного диапазона;
5. Если нужно, например, просуммировать числа из несвязных диапазонов, зажимаем Ctrl, и выделяем нужное количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на другой диапазон;
6. Когда выделили все нужные диапазоны, для завершения нажимаем Enter;

В процессе выделения можно нажимать F4 для изменения типа ссылки и F3 для вставки именованных ссылок.