Перечислите те функции программы excel: Функции Excel (по категориям) — Служба поддержки Майкрософт

7 программ, кроме Excel, которые помогут организовать вашу работу

Автор:

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

Достичь того, что Excel не удается

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

Во-вторых, Excel не всегда хорошо взаимодействует со всеми приложениями и программами. Например, если пользователь использует эту программу на MacOS, он обязательно заметит, что Excel не приспособлен для работы с этой операционной системой. В-третьих, современный мир деловых партнерских отношений порождает большую потребность в управлении проектами и входе, предназначенном для пользователей из различных источников, а Excel такую ​​потребность удовлетворить не может. Когда то Microsoft Office был единственным таким «всплеском» в водовороте современных технологий. Теперь ситуация кардинально изменилась и пользователи могут бесплатно загрузить отдельные приложения и программы, которые выполняют все те же функции, что когда-то мог выполнить только Excel. Вот 7 таких программ:

# 1: Электронная таблица Smartsheet + Инструменты эффективного пользования

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

# 2 Надежная и скоростная приборная панель от Workzone

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

# 3: Реализация проекта Microsoft с помощью подписки Office 365

Когда речь идет об офисном программном обеспечении, трудно игнорировать компанию Microsoft. Мы внесли в список лучших «неекселивських» программ и Microsoft Project, которая идет в наборе с подпиской на Office 365. Программа сочетает оригинальный Excel и премиум-инструменты для управления проектами.

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

# 4: Таблицы Google как альтернатива Excel

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

# 5: Лучший инструмент для организации работы малого бизнеса

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

# 6: Электронная таблица для управления проектами, которая отслеживает выполнение задач

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

# 7: Отслеживание проектов в электронной таблице

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

Источник: NAI Global

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

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

Функции могут использоваться как аргументы в других функциях. Если функция используется в качестве аргумента или является вложенной функцией, то она должна возвращать аргументу значение того же типа. Если функция возвращает значение другого типа, отобразится ошибка #ЗНАЧ!.

Например, следующая формула использует вложенную функцию СРЗНАЧ для сравнения ее значения со значением 50. Результат сравнения должен быть логической величиной (ИСТИНА или ЛОЖЬ), так как это требуемый тип для первого аргумента функции ЕСЛИ.

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

В MS Excel используется более 100 функций, объединенных по категориям:

•Функции работы с базами данных можно использовать, если необходимо убедиться в том, что значения списка удовлетворяют условию. С их помощью, например, можно определить количество записей в таблице о продажах или извлечь те записи, в которых значение поля «Сумма» больше 1000, но меньше 2500.

•Функции работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Например, если требуется использовать в формуле текущую дату, воспользуйтесь функцией СЕГОДНЯ, возвращающей текущую дату по системным часам.

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

18

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

•Информационные функции предназначены для определения типа данных, хранимых в ячейке. Они проверяют выполнение какого-то условия и возвращают в зависимости от результата значение ИСТИНА или ЛОЖЬ. Так, если ячейка содержит четное значение, функция ЕЧЁТН возвращает значение ИСТИНА. Если в диапазоне функций имеется пустая ячейка, можно воспользоваться функцией

СЧИТАТЬПУСТОТЫ.

•Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий. Так, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, — если оно ложно.

•Функции ссылки и автоподстановки осуществляют поиск в списках или таблицах. Например, для поиска значения в таблице используйте функцию ВПР, а для поиска положения значения в списке — функцию ПОИСКПОЗ.

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

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

•Функции обработки текста позволяют производить действия над строками текста, например изменить регистр или определить длину строки. Можно также объединить несколько строк в одну. Например, с помощью функций СЕГОДНЯ и ТЕКСТ можно создать сообщение, содержащее текущую дату и привести его к виду «дд-

ммм-гг»:= «Балансовый отчет от» &ТЕКСТ(СЕГОДНЯ(), «дд-мм-гг»)

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

5.7Ошибки в формулах

При появлении сообщения Ошибка в формуле:

19

•Проверьте, одинаково ли количество открывающих и закрывающих скобок.

•Проверьте правильность использования оператора диапазона при ссылке на

группу ячеек.

•Проверьте, все ли необходимые аргументы введены для функций.

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

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

ней.

•Не изменяйте формат чисел, введенных в формулы. Например, даже если в формулу необходимо ввести 1000 р., то введите число 1000.

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

Ошибка #ДЕЛ/0!. Ошибка появляется, когда в формуле делается попытка деления на ноль. Например, в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение (если операнд является пустой ячейкой, то ее содержимое интерпретируется как ноль), или в формуле содержится явное деление на ноль.

Ошибка #Н/Д. Значение ошибки #Н/Д является сокращением термина “Неопределенные Данные”. Это значение помогает предотвратить использование ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д, если они должны содержать данные, но в настоящий момент эти данные отсутствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать значение #Н/Д вместо того, чтобы пытаться производить вычисления. Ошибка может возникнуть, если не заданы один или несколько аргументов стандартной или пользовательской функции, а также задан недопустимый аргумент.

Ошибка #ИМЯ?. Ошибка #ИМЯ? появляется, когда Excel не может распознать имя, используемое в формуле. Возможная причина:

•Используемое имя было удалено или не было определено.

•Имеется ошибка в написании имени.

•Имеется ошибка в написании имени функции.

•В формулу введен текст, не заключенный в двойные кавычки.

•В ссылке на диапазон ячеек пропущен знак двоеточия (:).

Ошибка #ПУСТО!. Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек.

20

Ошибка #ССЫЛКА!. Ошибка #ССЫЛКА! появляется, когда используется недопустимая ссылка на ячейку. Возможно, ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.

Ошибка #ЗНАЧ!. Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения введен текст, и MS Excel не может преобразовать его к нужному типу данных.

5.8 Использование имен

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

Если данные не имеют заголовков или размещены на другом листе, можно создать имя, описывающее ячейку или диапазон ячеек. Использование имен может упростить понимание формулы. Например, =СУММ(Продано_в_первом_квартале) проще для понимания, чем формула =СУММ(Продажа!C20:C30). В этом примере имя Продано_в_первом_квартале представляет диапазон ячеек C20:C30 на листе «Продажа».

Имена можно использовать в любом листе книги. Например, если имя «Контракты» ссылается на группу ячеек A20:A30 в первом листе рабочей книги, то это имя можно применить в любом другом листе той же рабочей книги для ссылки на эту группу. По умолчанию имена являются абсолютными ссылками.

Для того чтобы присвоить имя ячейке или группе ячеек:

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

2.Выберите поле имени, которое расположено слева в строке формул и введите

имя ячеек.

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

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

21

Список функций Excel с примерами (бесплатная загрузка!)

Новые функции
XLOOKUP	Заменяет VLOOKUP, HLOOKUP и INDEX / MATCH	XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])
Логический	да
И	Проверяет выполнение всех условий. ИСТИНА/ЛОЖЬ	И(лог1,лог2)
IF	Если условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь еще.	ЕСЛИ(логическая_проверка,значение_если_истина,значение_если_ложь)
ЕСЛИОШИБКА	Если результатом является ошибка, сделайте что-нибудь другое.	ЕСЛИОШИБКА(ЗНАЧЕНИЕ,значение_если_ошибка)
НЕ	Изменяет ИСТИНА на ЛОЖЬ и ЛОЖЬ на ИСТИНА.	НЕ(логическое)
ИЛИ	Проверяет, выполняются ли какие-либо условия. ИСТИНА/ЛОЖЬ	ИЛИ(лог1,лог2)
XOR	Проверяет, выполняется ли одно и только одно условие. ИСТИНА/ЛОЖЬ	Исключающее ИЛИ(лог1,лог2)
Поиск и ссылка	да
ЛОЖЬ	Логическое значение: ЛОЖЬ.	ЛОЖЬ
ИСТИНА	Логическое значение: ИСТИНА.	ИСТИНА
АДРЕС	Возвращает адрес ячейки в виде текста.	АДРЕС(номер_строки,номер_столбца,номер_абс,C1,текст_листа)
ОБЛАСТИ	Возвращает количество областей в ссылке.	ОБЛАСТИ (ссылка)
ВЫБОР	Выбирает значение из списка на основе его номера позиции.	ВЫБОР(номер_индекса,значение1,значение2)
КОЛОННА	Возвращает номер столбца ссылки на ячейку.	КОЛОННА (ссылка)
СТОЛБЦЫ	Возвращает количество столбцов в массиве.	СТОЛБЦЫ (массив)
HLOOKUP	Поиск значения в первой строке и возврат значения.	HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГИПЕРССЫЛКА	Создает интерактивную ссылку.	ГИПЕРССЫЛКА(link_location,friendly_name)
ИНДЕКС	Возвращает значение на основе номеров столбца и строки.	ИНДЕКС(массив,номер_строки,номер_столбца)
НЕПРЯМАЯ	Создает ссылку на ячейку из текста.	НЕПРЯМАЯ(ref_text,C1)
ПРОСМОТР	Поиск значений по горизонтали или по вертикали.	ПРОСМОТР(искомое_значение,искомый_вектор,результирующий_вектор)
ПОИСКПОЗ	Ищет значение в списке и возвращает его позицию.	ПОИСКПОЗ (искомое_значение, искомый_массив, тип_соответствия)
СМЕЩЕНИЕ	Создает смещение привязки от начальной точки.	СМЕЩЕНИЕ(ссылка,строки,столбцы,высота,ширина)
ROW	Возвращает номер строки ссылки на ячейку.	РЯД (ссылка)
ROWS	Возвращает количество строк в массиве.	РЯДЫ (массив)
ТРАНСП	Меняет ориентацию диапазона ячеек.	ТРАНСП(массив)
ВПР	Поиск значения в первом столбце и возврат значения.	ВПР(искомое_значение,массив_таблиц,номер_индекса_столбца,диапазон_просмотра)
Дата и время	да
ДАТА	Возвращает дату из года, месяца и дня.	ДАТА(год,месяц,день)
DATEDIF	Количество дней, месяцев или лет между двумя датами.	РАЗНДАТ
ДАТАЗНАЧ	Преобразует дату, сохраненную в виде текста, в действительную дату	ДАТАЗНАЧ(дата_текст)
ДЕНЬ	Возвращает день в виде числа (1-31).	ДЕНЬ(серийный_номер)
ДНЕЙ	Возвращает количество дней между двумя датами.	ДНЕЙ(дата_конца,дата_начала)
DAYS360	Возвращает количество дней между двумя датами в году из 360 дней.	ДНЕЙ360(дата_начала,дата_окончания,метод)
EDATE	Возвращает дату, отстоящую от начальной даты на n месяцев.	ЭДАТА(дата_начала,месяцы)
КОНМЕСЯЦ	Возвращает последний день месяца, n месяцев до даты.	КОНМЕСЯЦА(дата_начала,месяцы)
ЧАС	Возвращает час в виде числа (0-23).	ЧАС(серийный_номер)
МИНУТЫ	Возвращает минуты в виде числа (0-59).	МИНУТА(серийный_номер)
МЕСЯЦ	Возвращает месяц в виде числа (1-12).	МЕСЯЦ(серийный_номер)
ЧИСТРАБДНИ	Количество рабочих дней между двумя датами.	ЧИСТРАБДНИ(дата_начала,дата_окончания,праздники)
NETWORKDAYS.INTL	Рабочие дни между двумя датами, настраиваемые выходные.	ЧИСТРАБДНИ.МЕЖД(дата_начала,дата_окончания,выходные,праздники)
СЕЙЧАС	Возвращает текущую дату и время.	СЕЙЧАС()
ВТОРАЯ	Возвращает секунды в виде числа (0-59)	SECOND(serial_number)
ВРЕМЯ	Возвращает время из часов, минут и секунд.	ВРЕМЯ(час,минута,секунда)
TIMEVALUE	Преобразует время, сохраненное в виде текста, в действительное время.	ВРЕМЗНАЧ(время_текст)
СЕГОДНЯ	Возвращает текущую дату.	СЕГОДНЯ()
ДЕНЬ НЕД	Возвращает день недели в виде числа (1-7).	ДЕНЬ НЕДЕЛИ(serial_number,return_type)
WEEKNUM	Возвращает номер недели в году (1-52).	НОМЕР НЕДЕЛИ (серийный_номер, тип_возврата)
РАБДЕНЬ	Дата n рабочих дней от даты.	РАБДЕНЬ(дата_начала,дни,праздники)
WORKDAY.INTL	Дата n рабочих дней от даты, пользовательские выходные.	РАБДЕНЬ.МЕЖД(дата_начала,дни,выходные,праздники)
ГОД	Возвращает год.	ГОД(серийный_номер)
YEARFRAC	Возвращает долю года между двумя датами.	YEARFRAC(start_date,end_date,basic)
Машиностроение	да
ПРЕОБРАЗОВАТЬ	Преобразование числа из одной единицы в другую.	ПРЕОБРАЗОВАТЬ(число,из_единицы,в_единицу)
Финансовый	да
FV	Расчет будущей стоимости.	БС(коэффициент,кпер,тыс.т,пс,тип)
PV	Вычисляет текущую стоимость.	PV(ставка,кпер,тысяча на тонну,б.с.,тип)
КПЕР	Вычисляет общее количество периодов платежей.	КПЕР(ставка,pmt,pv,fv,тип)
PMT	Расчет суммы платежа.	PMT(коэффициент,кпер,pv,fv,тип)
СТАВКА	Расчет процентной ставки.	СТАВКА(nper,pmt,pv,fv,тип,предположение)
NPV	Расчет чистой приведенной стоимости.	NPV(ставка,значение1,значение2)
IRR	Внутренняя норма доходности для набора периодических CF.	IRR(значения,предположения)
XIRR	Внутренняя норма доходности для набора непериодических КФ.	ЧИСТНДОХД(значения,даты,предположение)
ЦЕНА	Рассчитывает цену облигации.	ЦЕНА(расчет,срок,ставка,лет,погашение,ЧАСТОТА,база)
ДОХОД	Рассчитывает доходность облигации.	ДОХОД(расчет,срок,ставка,процент,погашение,ЧАСТОТА,база)
INTRATE	Процентная ставка полностью инвестированной ценной бумаги.	ВНУТРЕННЯЯ (расчет, срок погашения, инвестиции, погашение, база)
Информация	да
CELL	Возвращает информацию о ячейке.	ЯЧЕЙКА (тип_информации, ссылка)
ERROR.TYPE	Возвращает значение, представляющее ошибку ячейки.	ТИП ОШИБКИ(значение_ошибки)
IПУСТО	Проверить, пуста ли ячейка. ИСТИНА/ЛОЖЬ	ЕПУСТО (ЗНАЧЕНИЕ)
ISERR	Проверяет, является ли значение ячейки ошибкой, игнорирует #N/A. ИСТИНА/ЛОЖЬ	ЕОШИБКА(ЗНАЧЕНИЕ)
ЕОШИБКА	Проверить, является ли значение ячейки ошибкой. ИСТИНА/ЛОЖЬ	ОШИБКА(ЗНАЧЕНИЕ)
ISEVEN	Проверка четности значения ячейки. ИСТИНА/ЛОЖЬ	ISEVEN(число)
ISFORMULA	Проверить, является ли ячейка формулой. ИСТИНА/ЛОЖЬ	ИФОРМУЛА (ссылка)
НЕЛОГИЧНОСТЬ	Проверка логичности ячейки (ИСТИНА или ЛОЖЬ). ИСТИНА/ЛОЖЬ	НЕЛОГИЧНОЕ(ЗНАЧЕНИЕ)
ISNA	Проверить, является ли значение ячейки #N/A. ИСТИНА/ЛОЖЬ	ISNA(ЗНАЧЕНИЕ)
ISNONTEXT	Проверить, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА/ЛОЖЬ	НЕТЕКСТ(ЗНАЧЕНИЕ)
ISNUMBER	Проверить, является ли ячейка числом. ИСТИНА/ЛОЖЬ	IНОМЕР(ЗНАЧЕНИЕ)
ISODD	Проверить, является ли значение ячейки нечетным. ИСТИНА/ЛОЖЬ	ISODD(число)
ISREF	Проверить, является ли значение ячейки ссылкой. ИСТИНА/ЛОЖЬ	ISREF(ЗНАЧЕНИЕ)
ISTEXT	Проверить, является ли ячейка текстовой. ИСТИНА/ЛОЖЬ	ИСТЕКСТ(ЗНАЧ)
N	Преобразует значение в число.	N(ЗНАЧЕНИЕ)
NA	Возвращает ошибку: #N/A.	нет данных()
TYPE	Возвращает тип значения в ячейке.	ТИП(ЗНАЧЕНИЕ)
Математика	да
ABS	Вычисляет абсолютное значение числа.	АБС(номер)
АГРЕГАТ	Определение и выполнение вычислений для базы данных или списка.	АГРЕГАТ(номер_функции,параметры,массив,k)
CEILING	Округляет число до ближайшего указанного кратного.	ПОТОЛОК(номер,значение)
COS	Возвращает косинус угла.	COS(номер)
ГРАДУСЫ	Преобразует радианы в градусы.	ГРАДУСЫ(угол)
DSUM	Суммирует записи базы данных, соответствующие определенным критериям.	DSUM(база данных,поле,критерий)
ЧЕТНЫЙ	Округляет до ближайшего четного целого числа.	ЧЕТНЫЙ(число)
EXP	Вычисляет экспоненциальное значение для заданного числа.	EXP(число)
ФАКТ	Возвращает факториал.	ФАКТ(номер)
ЭТАЖ	Округляет число в меньшую сторону до ближайшего указанного кратного.	ЭТАЖ(номер,значение)
НОД	Возвращает наибольший общий делитель.	НОД(число1,число2)
INT	Округляет число в меньшую сторону до ближайшего целого числа.	INT(число)
LCM	Возвращает наименьшее общее кратное.	LCM(номер1,номер2)
LN	Возвращает натуральный логарифм числа.	ЛН(номер)
LOG	Возвращает логарифм числа по указанному основанию.	LOG(номер,база)
LOG10	Возвращает логарифм числа по основанию 10.	LOG10(число)
MOD	Возвращает остаток после деления.	MOD(число,делитель)
MROUND	Округляет число до указанного кратного.	КРУГЛЫЙ(число,несколько)
НЕЧЕТНОЕ	Округляет до ближайшего нечетного целого числа.	НЕЧЕТНЫЙ (число)
PI	Значение PI.	ПИ()
POWER	Вычисляет число, возведенное в степень.	МОЩНОСТЬ(число,мощность)
ПРОИЗВЕД	Умножает массив чисел.	ПРОДУКТ(номер1,номер2)
ЧАСТНОЕ	Возвращает целочисленный результат деления.	ЧАСТНОЕ(числитель,знаменатель)
РАДИАНЫ	Преобразует угол в радианы.	РАДИАН(угол)
СЛУЧАЙ	Вычисляет случайное число от 0 до 1.	СЛУЧАЙ()
СЛУЧМЕЖДУ	Вычисляет случайное число между двумя числами.	СЛУЧАЙ МЕЖДУ(нижний,верхний)
ОКРУГЛ	Округляет число до указанного количества цифр.	ОКРУГЛ(число,число_цифр)
ОКРУГЛ ВНИЗ	Округляет число в меньшую сторону (к нулю).	ОКРУГЛВНИЗ(число,число_разрядов)
ОКРУГЛВВЕРХ	Округляет число в большую сторону (от нуля).	ОКРУГЛВВЕРХ(число,число_разрядов)
ЗНАК	Возвращает знак числа.	ЗНАК(номер)
SIN	Возвращает синус угла.	SIN(номер)
SQRT	Вычисляет квадратный корень из числа.	SQRT(номер)
ПРОМЕЖУТОЧНЫЙ ИТОГ	Возвращает сводную статистику для серии данных.	ПРОМЕЖУТОЧНЫЙ ИТОГ(номер_функции,REh2)
СУММ	Складывает числа.	СУММ(число1,число2)
СУММЕСЛИ	Суммирует числа, соответствующие критерию.	СУММЕСЛИ(диапазон,критерий,сумма_диапазон)
СУММЕСЛИМН	Суммирует числа, соответствующие нескольким критериям.	СУММЕСЛИМН(сумма_диапазон,критерий_диапазон,критерий)
СУММПРОИЗВ	Умножает массивы чисел и суммирует полученный массив.	СУММПРОИЗВ(массив1,массив2,массив3)
TAN	Возвращает тангенс угла.	TAN(номер)
TRUNC	Усекает число до определенного количества цифр.	TRUNC(число,число_цифр)
Статистика	да
СРЕДНИЙ	Средние числа.	СРЕДНЕЕ(число1,число2)
AVERAGEA	Средние числа. Включает текст и FALSE = 0, TRUE = 1.	СРЗНАЧА(значение1,значение2)
СРЗНАЧЕСЛИ	Усредняет числа, соответствующие критерию.	СРЗНАЧЕСЛИ(диапазон,критерий,средний_диапазон)
AVERAGEIFS	Усредняет числа, соответствующие нескольким критериям.	СРЗНАЧЕСЛИМН(средний_диапазон,критерий_диапазон,критерий)
КОРРЕЛ	Вычисляет корреляцию двух рядов.	КОРРЕЛ(массив1,массив2)
COUNT	Подсчет ячеек, содержащих число.	СЧЕТ(значение1,значение2)
COUNTA	Подсчет непустых ячеек.	СЧЁТ(значение1,значение2)
COUNTB004 COUNTB005	Подсчет пустых ячеек.	СЧИТАТЬ ПУСТО (диапазон)
СЧЁТЕСЛИ	Подсчитывает ячейки, соответствующие критерию.	СЧЁТЕСЛИ(диапазон,критерий)
СЧЁТЕСЛИМН	Подсчитывает ячейки, соответствующие нескольким критериям.	СЧЁТЕСЛИМН(диапазон_критериев,критерий)
ПРОГНОЗ	Прогноз будущих значений y на основе линейной линии тренда.	ПРОГНОЗ(x,известные_y,известные_x)
ЧАСТОТА	Подсчитывает значения, попадающие в указанные диапазоны.	ЧАСТОТА(массив_данных,массив_бинов)
РОСТ	Вычисляет значения Y на основе экспоненциального роста.	РОСТ(известный_ys,известный_x,новый_x,const)
INTERCEPT	Вычисляет точку пересечения Y для линии наилучшего соответствия.	ПЕРЕХВАТ (известный_ys,известный_xs)
НАИБОЛЬШИЙ	Возвращает k-е наибольшее значение.	БОЛЬШОЙ(массив,k)
ЛИНЕЙН	Возвращает статистику по линии тренда.	ЛИНЕЙН(известный_ys,известный_xs,константа,статистика)
MAX	Возвращает наибольшее число.	МАКС(число1,число2)
МЕДИАНА	Возвращает медианное число.	МЕДИАНА(число1,число2)
МИН	Возвращает наименьшее число.	МИН(число1,число2)
РЕЖИМ	Возвращает наиболее распространенный номер.	РЕЖИМ(номер1,номер2)
ПРОЦЕНТИЛЬ	Возвращает k-й процентиль.	ПРОЦЕНТИЛЬ(массив,k)
PERCENTILE.INC	Возвращает k-й процентиль. Где k включительно.	ПРОЦЕНТИЛЬ.ВКЛ(массив,k)
PERCENTILE.EXC	Возвращает k-й процентиль. Где k является исключительным.	ПРОЦЕНТИЛЬ.ИСКЛ(массив,k)
КВАРТИЛЬ	Возвращает указанное значение квартиля.	КВАРТИЛЬ(массив,квартиль)
QUARTILE.INC	Возвращает указанное значение квартиля. включительно.	КВАРТИЛЬ.ВКЛ(массив,кварта)
КВАРТИЛЬ.ИСКЛ	Возвращает указанное значение квартиля. Эксклюзив.	КВАРТИЛЬ.ИСКЛ(массив,кварта)
RANK	Ранг числа в серии.	РАНГ(номер,ссылка,порядок)
RANK.AVG	Ранг числа в серии. Средние.	RANK.AVG(номер,ссылка,порядок)
RANK.EQ	Ранг числа в серии. Высший ранг.	RANK.EQ(номер,ссылка,порядок)
НАКЛОН	Вычисляет наклон на основе линейной регрессии.	НАКЛОН(известный_ys,известный_xs)
МАЛЕНЬКИЙ	Возвращает k-е наименьшее значение.	МАЛЕНЬКИЙ(массив,k)
СТАНДОТКЛОН	Вычисляет стандартное отклонение.	СТАНДОТКЛОН(номер1,номер2)
STDEV.P	Вычисляет стандартное отклонение всей совокупности.	СТАНДОТКЛОН.П(число1,число2)
STDEV.S	Вычисляет SD образца.	СТАНДОТКЛ.С(номер1,номер2)
СТАНДОТКЛОН	Вычисляет стандартное отклонение всей совокупности	СТАНДОТКЛОН(число1,число2)
TREND	Вычисляет значения Y на основе линии тренда.	TREND(известный_ys,известный_xs,новый_xs,постоянный)
Текст	да
CHAR	Возвращает символ, заданный кодом.	СИМВОЛ(число)
CLEAN	Удаляет все непечатаемые символы.	ОЧИСТКА(текст)
КОД	Возвращает числовой код символа.	КОД(текст)
СЦЕПИТЬ	Объединяет текст вместе.	СЦЕПИТЬ(текст1,текст2)
ДОЛЛАР	Преобразует число в текст в денежном формате.	ДОЛЛАР(число,десятичные)
EXACT	Проверить, равны ли ячейки. Чувствительный к регистру. ИСТИНА/ЛОЖЬ	ТОЧНО(текст1,текст2)
НАЙТИ	Находит положение текста в ячейке. С учетом регистра.	НАЙТИ(найти_текст,внутри_текста,начальный_номер)
LEFT	Обрезает текст на несколько символов слева.	ЛЕВЫЙ(текст,число_знаков)
LEN	Подсчитывает количество символов в тексте.	ДЛСТР(текст)
LOWER	Преобразует текст в нижний регистр.	НИЖНИЙ (текст)
MID	Извлекает текст из середины ячейки.	СРЕДНИЙ (текст, начальный_номер, число_знаков)
PROPER	Преобразует текст в правильный регистр.	ПРАВИЛЬНЫЙ(текст)
REPLACE	Заменяет текст в зависимости от его местоположения.	ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст)
REPT	Повторяет текст несколько раз.	ПОВТОР(текст,количество_раз)
ПРАВО	Усекает текст на несколько символов справа.	ПРАВО(текст,число_знаков)
ПОИСК	Определяет положение текста в ячейке. Регистр не учитывается.	ПОИСК(найти_текст,внутри_текста,начальный_номер)
ПОДСТАВИТЬ	Находит и заменяет текст. Чувствительный к регистру.	ЗАМЕНИТЬ(текст,старый_текст,новый_текст,номер_экземпляра)
ТЕКСТ	Преобразует значение в текст в определенном числовом формате.	ТЕКСТ(ЗНАЧЕНИЕ,format_text)
ТРИМ	Удаляет все лишние пробелы из текста.	ОТДЕЛКА (текст)
ПРОПИСНЫЕ	Преобразует текст в верхний регистр.	ВЕРХНИЙ (текст)
ЗНАЧЕНИЕ	Преобразует число, сохраненное как текст, в число.	ЗНАЧЕНИЕ(текст)

Список функций Excel с примерами формул

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

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

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

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

• Текстовые/строковые функции
• Логические функции
• Математические функции

г.

• Статистические функции
• Функции поиска и ссылок
• Финансовые функции
• Функции даты
• Функции времени
• Функции UDF для подсчета и суммирования ячеек по цвету

Текстовые функции Excel (строковые функции)

Существует множество функций Microsoft Excel для работы с текстовыми строками. Вот наиболее важные из них:

Функция ТЕКСТ

ТЕКСТ(значение, формат_текст) используется для преобразования числа или даты в тестовую строку в указанном формате, где:

• Значение — числовое значение, которое необходимо преобразовать в текст.
• Format_text — желаемый формат.

Следующие формулы демонстрируют функцию ТЕКСТ в Excel в действии:

=ТЕКСТ(A1,"мм/дд/гггг") — преобразование даты в ячейке A1 в текстовую строку в традиционном для США формате даты, например «01.01.2015» (месяц/день/год).

=ТЕКСТ(A1,"€#,##0,00") — преобразует число в формате A1 в текстовую строку валюты, например «3,00 евро».

Примеры формул ТЕКСТ:

• Функция ТЕКСТ для преобразования даты в текстовый формат
• ТЕКСТОВЫЕ формулы для преобразования числа в текст

Функция СЦЕПИТЬ

СЦЕПИТЬ(текст1, [текст2], …) предназначена для объединения нескольких фрагментов текста или объединения значений из нескольких ячеек в одну ячейку. Аналогичного результата можно добиться с помощью оператора Excel и , как показано на следующем снимке экрана.

Вы можете найти множество других примеров формул в учебнике СЦЕПИТЬ в Excel: объединяйте текстовые строки, ячейки и столбцы.

Функция TRIM

TRIM(текст) удаляет начальные и конечные пробелы, а также лишние пробелы между словами. Где текст — это либо текстовая строка, либо ссылка на ячейку, содержащую текст, из которого вы хотите удалить пробелы. На следующем снимке экрана показан пример использования:

Дополнительные примеры формул см. в разделе 3 способа удаления пробелов между словами и числами в Excel.

Функция ПОДСТАВИТЬ

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра]) заменяет один набор символов другим в указанной ячейке или текстовой строке. Синтаксис функции ПОДСТАВИТЬ следующий:

• Текст — исходная текстовая строка или ссылка на ячейку, в которую нужно подставить определенные символы.
• Old_text — символы, которые вы хотите заменить.
• Новый_текст — символы, которыми вы хотите заменить старый текст.

г.

• Nth_appearance — необязательный параметр, указывающий, какое вхождение old_text вы хотите заменить новым_текстом. Если опущено, то каждое вхождение старого текста будет заменено новым текстом.

Например, следующая формула ПОДСТАВИТЬ заменяет все запятые в ячейке A1 точками с запятой:

=ПОДСТАВИТЬ(A2, ",", ";")

Примеры формул ПОДСТАВКИ:

г.

Функция ЗНАЧ

ЗНАЧ(текст) — преобразует текстовую строку в число.

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

Примеры формул ЗНАЧЕНИЕ:

• Преобразование цифр в текстовом формате в числа
• ЗНАЧЕНИЕ функция для преобразования текста в дату

г.

Функция EXACT

EXACT(text1, text2) сравнивает две текстовые строки и возвращает TRUE, если оба значения совпадают, включая регистр, FALSE в противном случае.

Например, если A2 — это «яблоки», а B2 — это «яблоки», формула =ТОЧНО(A2, B2) вернет ЛОЖЬ, поскольку они не совпадают точно.

Функция EXACT редко используется сама по себе, но она полезна в более сложных задачах, таких как выполнение Vlookup с учетом регистра в Excel.

Функции для изменения регистра текста (ПРОПИСНЫЕ, МНИЖНИЕ, ПРОПИСНЫЕ)

Microsoft Excel предоставляет 3 текстовые функции для преобразования между ПРОПИСНЫМИ, нижними и правильными регистрами.

ПРОПИСНЫЕ(текст) — переводит все символы указанной текстовой строки в верхний регистр.

LOWER(текст) — изменяет все прописные буквы в текстовой строке на строчные.

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

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

Дополнительные примеры формул для преобразования регистра текста можно найти в разделе Изменение регистра текста в Excel на ПРОПИСНОЙ, нижний или правильный.

Извлечение текстовых символов (LEFT, RIGHT, MID)

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

LEFT(текст, [число_символов]) — возвращает указанное количество символов с начала текстовой строки.

ПРАВО(текст,[число_символов]) — возвращает указанное количество символов с конца текстовой строки.

MID(text, start_num, num_chars) — возвращает определенное количество символов из текстовой строки, начиная с любой указанной вами позиции.

В этих функциях вы предоставляете следующие аргументы:

• Текст — текстовая строка или ссылка на ячейку, содержащую символы, которые вы хотите извлечь.
• Start_num — указывает, с чего начать (т. е. позицию первого символа, который вы хотите извлечь).
• Num_chars — количество символов, которые вы хотите извлечь.

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

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

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

Функции И, ИЛИ, XOR

И(логический1, [логический2], …) — возвращает ИСТИНА, если все аргументы оцениваются как ИСТИНА, в противном случае — ЛОЖЬ.

ИЛИ(логический1, [логический2], …) — возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА.

Исключающее ИЛИ(лог1, [лог2],…) — возвращает логических исключающих или всех аргументов. Эта функция появилась в Excel 2013 и недоступна в более ранних версиях.

Примеры формул логических функций:

• И примеры формул
• Примеры формулы ИЛИ
• Примеры формулы XOR

Функция НЕ

НЕ(логическое) — меняет значение своего аргумента на противоположное, т.е. если логическое оценивается как ЛОЖЬ, функция НЕ возвращает ИСТИНА и наоборот.

Например, обе следующие формулы вернут ЛОЖЬ:

=НЕ(ИСТИНА)

=НЕ(2*2=4)

Дополнительные примеры функции НЕ см. в разделе Использование функции НЕ в Эксель.

Функция ЕСЛИ

Функцию ЕСЛИ в Excel иногда называют «условной функцией», поскольку она возвращает значение на основе заданного вами условия. Синтаксис IF следующий:

IF(логическая_проверка, [значение_если_истина], [значение_если_ложь])

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

Например, формула =ЕСЛИ(A1<>"", "хорошо", "плохо") возвращает «хорошо», если в ячейке A1 есть какое-либо значение, и «плохо» в противном случае.

А вот пример вложенной формулы ЕСЛИ, которая «расшифровывает» экзаменационную оценку в ячейке A2:

=ЕСЛИ(A2>80, "Великолепно", ЕСЛИ(A2>50, "Хорошо", ЕСЛИ(A2>30, "Удовлетворительно", "Плохо")))

Примеры формул ЕСЛИ:

• IF формулы для чисел, текста, дат, пустых ячеек
• Оператор IF с несколькими условиями И/ИЛИ
• Вложенные функции ЕСЛИ в Excel
• Функция ЕСЛИ в формулах массива
• Использование ЕСЛИ с другими функциями Excel

Функции ЕСЛИОШИБКА и ЕСЛИНА

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

ЕСЛИОШИБКА(значение, значение_если_ошибка) — проверяет, дает ли формула или выражение ошибку. Если это так, формула возвращает значение, указанное в аргументе value_if_error , в противном случае возвращается результат формулы. Эта функция обрабатывает все возможные ошибки Excel, включая ЗНАЧЕНИЕ, Н/Д, ИМЯ, ССЫЛКА, ЧИСЛО и другие. Он доступен в Excel 2007 и выше.

IFNA(value, value_if_na) — введен в Excel 2013, работает аналогично ЕСЛИОШИБКА, но обрабатывает только ошибки #N/A.

В следующих примерах показана простейшая формула ЕСЛИОШИБКА:

Примеры формул ЕСЛИОШИБКА / ЕСЛИ:

• Использование функций ЕСЛИОШИБКА и ЕСЛИНА в Excel
• ВПР Excel с ЕСЛИОШИБКА / ЕОШИБКА

Математические функции Excel

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

Нахождение суммы ячеек

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

Функция СУММ

СУММ(число1,[число2],…) возвращает сумму своих аргументов. Аргументы могут быть числами, ссылками на ячейки или числовыми значениями на основе формул.

Например, простейшая математическая формула =СУММ(A1:A3, 1) суммирует значения в ячейках A1, A2 и A3, а затем добавляет к результату 1.

Примеры формулы СУММ:

• Функция СУММ для суммирования столбца В Excel
• Excel СУММ в формулах массива

Функции СУММЕСЛИ и СУММЕСЛИМН (условная сумма)

Обе функции суммируют ячейки в заданном диапазоне, удовлетворяющие определенному условию. Разница в том, что СУММЕСЛИМН может оценивать только один критерий, а СУММЕСЛИМН, представленный в Excel 2007, позволяет использовать несколько критериев. Обратите внимание, что порядок аргументов в каждой функции разный:

СУММЕСЛИ(диапазон, критерии, [диапазон_суммы])

СУММЕСЛИМН(диапазон_суммы, диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2], …)

• диапазон / диапазон_критериев соответствующие критерии.
• критерии — условие, которое необходимо выполнить.
• sum_range — ячейки для суммирования при выполнении условия.

На следующем снимке экрана показано, как функции СУММЕСЛИ и СУММЕСЛИМН можно использовать с реальными данными:

Примеры формул СУММЕСЛИ и СУММЕСЛИМН:

• СУММЕСЛИ в Excel — формулы для условного суммирования ячеек
• Суммировать ячейки с несколькими критериями

Функция СУММПРОИЗВ

СУММПРОИЗВ(массив1,массив2, …) — одна из немногих функций Microsoft Excel, обрабатывающих массивы. Он умножает предоставленные компоненты массива и возвращает сумму произведений.

Суть функции СУММПРОИЗВ может быть трудной для понимания, и мы надеемся, что следующие примеры прольют свет на ее основные области применения.

Примеры формулы СУММПРОИЗВ:

• формула СУММПРОИЗВ для Vlookup с учетом регистра
• Подсчет дубликатов между двумя столбцами
• Суммировать ячейки с несколькими критериями

Генерация случайных чисел (СЛУЧАЙ и СЛУЧМЕЖДУ)

Microsoft Excel предоставляет 2 функции для генерации случайных чисел. Обе функции являются изменчивыми, что означает, что каждый раз, когда рабочий лист вычисляет s, возвращается новое число.

RAND() — возвращает случайное действительное (десятичное) число от 0 до 1.

RANDBETWEEN(нижний, верхний) — возвращает случайное целое число между указанными вами нижними и верхними числами.

В следующем учебном пособии довольно хорошо объясняется суть каждой функции с примерами формул: Как использовать функции СЛЧИС и СЛУЧМЕЖДУ в Excel.

Функции округления

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

• ОКРУГЛ — округлить число до указанного количества цифр.
• ОКРУГЛВВЕРХ — округление числа в большую сторону до указанного количества цифр.
• ОКРУГЛВНИЗ — округлить число в меньшую сторону до указанного количества цифр.
• ОКРУГЛ — округляет число в большую или меньшую сторону до указанного кратного.
• ЭТАЖ — округлить число до указанного кратного.
• ПОТОЛОК — округлить число до указанного кратного.
• INT — округлить число до ближайшего целого числа в меньшую сторону.

г.

• TRUNC — обрезать число до указанного количества знаков после запятой.
• ЧЕТНОЕ — округлить число до ближайшего четного целого числа.
• ODD — округлить число до ближайшего нечетного целого числа.

Получение остатка после деления (функция ОСТАТ)

ОСТАТ(число, делитель) возвращает остаток после деления аргумента числа на делитель .

Эта функция очень удобна во многих случаях, например:

• Сумма значений в каждой второй или N ^-й строке
• Чередование цветов строк в Excel

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

Нахождение наибольшего, наименьшего и среднего значений

MIN(число1, [число2], …) — возвращает минимальное значение из списка аргументов.

МАКС(число1, [число2], …) — возвращает максимальное значение из списка аргументов

СРЗНАЧ(число1, [число2], …) — возвращает среднее значение аргументов.

МАЛЕНЬКИЙ(массив, k) — возвращает k-е наименьшее значение в массиве.

НАИБОЛЬШИЙ(массив, k) — возвращает k-е наибольшее значение в массиве.

На следующем снимке экрана показаны основные статистические функции в действии.

Примеры формул:

• Использование ЕСЛИ с функциями СРЗНАЧ, МИН и МАКС
• Вычисление среднего значения первых N элементов списка — Пример 1. Вычисление среднего значения первых N элементов списка
• ВПР со СРЗНАЧ, МАКС., МИН. (формулы массива)
• Сумма N наибольших/наименьших чисел в диапазоне (формула массива)

Подсчет ячеек

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

COUNT(значение1, [значение2], …) — возвращает количество числовых значений (числа и даты) в списке аргументов.

СЧЁТ(значение1, [значение2], …) — возвращает количество непустых ячеек в списке аргументов. Он подсчитывает ячейки, содержащие любую информацию, включая значения ошибок и пустые текстовые строки («»), возвращаемые другими формулами.

СЧИТАТЬПУСТОТЫ(диапазон) — подсчитывает количество пустых ячеек в указанном диапазоне. Ячейки с пустыми текстовыми строками («») также считаются пустыми ячейками.

СЧЁТЕСЛИ(диапазон, критерии) — подсчитывает количество ячеек в диапазоне, удовлетворяющих заданным критериям.

СЧЕТЧИСЛО(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…) — подсчитывает количество ячеек, соответствующих всем указанным критериям.

На следующем снимке экрана показаны функции счетных ячеек в действии:

Примечание. Поскольку даты хранятся в Excel как числа, они также учитываются статистическими функциями Excel. Например, формула =СЧЁТЕСЛИ(A2:A9, ">5") подсчитывает дату в ячейке A8 на снимке экрана выше, поскольку во внутренней системе Excel она хранится как 42005.

Примеры формул для подсчета ячеек:

• Подсчет ячеек с текстом (функции COUNTA и COUNT)
• Подсчет пустых ячеек в Excel (COUNTBLANK)
• Подсчет пустых или не пустых ячеек (COUNTIF)
• Использование СЧЁТЕСЛИ в Excel
• Подсчет ячеек с несколькими критериями (COUNTIFS)

Функции поиска и ссылок Excel

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

Функция ВПР

Функция ВПР ищет указанное значение в первом столбце и извлекает соответствующие данные из той же строки в другом столбце. Требуются следующие аргументы:

ВПР (искомое_значение, массив_таблиц, индексный_номер_столбца, [диапазон_просмотра])

• lookup_value — значение для поиска.
• table_array — два или более столбца данных.
• col_index_num — номер столбца, из которого извлекаются данные.
• range_lookup — определяет, следует ли искать с точным совпадением (FALSE) или с приблизительным соответствием (TRUE или опущено).

Например, формула =ВПР("яблоки", A2:C10, 3) ищет «яблоки» в ячейках с A2 по A10 и возвращает соответствующее значение из столбца C:

Примеры формулы ВПР:

• Учебник Excel по ВПР для начинающих
• Как использовать функции ВПР и СУММ или СУММЕСЛИ в Excel
• Двусторонний поиск, вложенный ВПР с несколькими критериями
• 4 способа выполнения Vlookup с учетом регистра в Excel
• ВПР Excel не работает — решения для ошибок N/A, NAME и VALUE

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

ИНДЕКС(массив, номер_строки, [номер_столбца]) — возвращает ссылку на ячейку в массиве на основе указанных вами номеров строки и столбца.

Вот простая формула ИНДЕКС: =ИНДЕКС(A1:C10, 3 ,4) , которая выполняет поиск в ячейках от A1 до C10 и возвращает значение на пересечении 3-й ^{-й строки} и 4-го ^{-го столбца} , это ячейка D3.

В следующем учебном пособии подробно объясняется функция ИНДЕКС: 6 наиболее эффективных способов использования функции ИНДЕКС в Excel.

Функция ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления]) — ищет искомое_значение в искомом_массиве, а затем возвращает относительное положение этого элемента в диапазоне.

Комбинацию функций ПОИСКПОЗ и ИНДЕКС можно использовать как более мощную и универсальную альтернативу функции ВПР в Excel, как показано в следующем учебном пособии: ИНДЕКС и ПОИСКПОЗ — лучшая альтернатива ВПР в Excel.

Функция ДВССЫЛ

ДВССЫЛ(ref_text, [a1]) — возвращает ссылку на ячейку или диапазон, заданную текстовой строкой.

Вот пример простейшей формулы ДВССЫЛ, чтобы получить общее представление:

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

Примеры формул ДВССЫЛ:

• Как использовать функцию ДВССЫЛ в Excel
• Как сделать зависимый раскрывающийся список в Excel

Функция OFFSET

OFFSET(ссылка, строки, столбцы, [высота], [ширина]) — возвращает ссылку на диапазон ячеек, который смещен от начальной ячейки или диапазона ячеек на указанное количество строки и столбцы.

Например, =СМЕЩ(A1, 1, 2) возвращает значение в ячейке C2, поскольку она находится на 1 строку ниже и на 2 столбца левее от A1.

Дополнительные примеры формул СМЕЩ см. в разделе Использование функции СМЕЩ в Excel.

функция ТРАНСП

ТРАНСП(массив) — преобразует горизонтальный диапазон ячеек в вертикальный диапазон и наоборот, т.е. преобразует строки в столбцы и столбцы в строки.

В следующем учебном пособии приводятся примеры формул и объясняются сильные и слабые стороны функции: Функция Excel ТРАНСП для преобразования строки в столбец.

Функция ГИПЕРССЫЛКА

HYPERLINK(link_location, [friendly_name]) — Создает гиперссылку на документ, хранящийся в локальной сети или Интернете.

Пошаговые инструкции по использованию функции ГИПЕРССЫЛКА см. в разделе Как добавить гиперссылку на другой лист Excel.

Финансовые функции Excel

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

Функция FV

FV(rate, nper ,pmt ,[pv], [type]) — вычисляет будущую стоимость инвестиций на основе постоянной процентной ставки.

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

Функции даты Excel

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

Создание дат

• ДАТА — возвращает порядковый номер указанной даты.
• ДАТАЗНАЧ — преобразует текстовую строку, представляющую формат даты в дату.

Текущая дата и время

• СЕГОДНЯ — возвращает текущую дату.
• СЕЙЧАС — возвращает текущую дату и время.

Извлечение дат и компонентов даты

• ДЕНЬ — возвращает день месяца.
• МЕСЯЦ — возвращает месяц указанной даты.

г.

• ГОД — возвращает год указанной даты.
• КОНМЕСЯЦ — возвращает последний день месяца.
• ДЕНЬ НЕД — возвращает день недели.
• WEEKNUM — возвращает номер недели даты.

Вычисление разницы дат

• РАЗНДАТ — возвращает разницу между двумя датами.
• ДАТАМЕС — возвращает дату, которая является указанным числом месяцев до или после даты начала.
• YEARFRAC — вычисляет долю года между двумя датами.

г.

Расчет рабочих дней

• РАБДЕНЬ — вычисляет дату, которая является заданным количеством рабочих дней до или после даты начала.
• РАБДЕНЬ.МЕЖД — вычисляет дату, которая представляет собой указанное количество дней недели до или после даты начала, с пользовательскими параметрами выходных.
• ЧИСТРАБДНИ — возвращает количество рабочих дней между двумя датами.
• NETWORKDAYS.INTL — возвращает количество рабочих дней между двумя датами с настраиваемыми выходными.

Функции времени Excel

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

TIME(час, минута, секунда) — возвращает порядковый номер, представляющий время.

TIMEVALUE(time_text) — преобразует время, введенное в виде текстовой строки, в порядковый номер, представляющий время.

СЕЙЧАС() — возвращает серийный номер, соответствующий текущей дате и времени.

ЧАС(серийный_номер) — преобразует указанный порядковый номер в час.

МИНУТЫ(серийный_номер) — преобразует указанный серийный номер в минуты.

SECOND(serial_number) — преобразует указанный серийный номер в секунды.

Примеры формулы времени:

• Преобразование времени в десятичное число, часы, минуты или секунды
• Расчет времени в Excel — разница во времени, сложение/вычитание времени
• Вставка времени в Excel с помощью функций СЕЙЧАС и ВРЕМЯ

г.

Подсчет и суммирование ячеек по цвету (определяемые пользователем функции)

Microsoft Excel не имеет встроенной функции для подсчета и суммирования ячеек по цвету, поэтому мы сделали шаг вперед и создали несколько пользовательских функций. Эти функции написаны на языке VBA, и вы можете добавлять их на листы через среду программирования Excel — редактор Visual Basic for Applications. Вот наш список пользовательских функций Excel:

• GetCellColor — возвращает цветовой код цвета фона указанной ячейки.

г.

• GetCellFontColor( — возвращает цветовой код цвета шрифта указанной ячейки.
• CountCellsByColor) — считает ячейки с заданным цветом фона.
• CountCellsByFontColor — подсчитывает ячейки с указанным цветом шрифта.
• SumCellsByColor( — вычисляет сумму ячеек с определенным цветом фона.
• SumCellsByFontColor( — возвращает сумму ячеек с определенным цветом шрифта.

г.

• WbkCountCellsByColor — подсчитывает ячейки с указанным цветом фона во всей книге.
• WbkSumCellsByColor — суммирует ячейки с указанным цветом фона во всей книге.

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

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