стандартные функции в Excel. Бесплатные примеры и статьи.

Функция ДВССЫЛ(), английский вариант INDIRECT(), возвращает ссылку на ячейку(и), заданную текстовой строкой. Например, формула =ДВССЫЛ("Лист1!B3") эквивалентна формуле =Лист1!B3. Мощь этой функции состоит в том, что саму ссылку (Лист1!B3) также можно изменять формулами, ведь для ДВССЫЛ() это просто текстовая строка! С помощью этой функции можно транспонировать таблицы, выводить значения только из четных/ нечетных строк, складывать цифры числа и многое другое.

Функция НАИМЕНЬШИЙ(), английский вариант SMALL(), возвращает k-ое наименьшее значение из массива данных. Например, если диапазон A1:А4 содержит значения 2;10;3;7, то формула =НАИМЕНЬШИЙ(A1:А4;2) вернет значение 3 (второе наименьшее).

Функция ЧСТРОК(), в английском варианте ROWS(), подсчитывает число строк в диапазоне ячеек или массиве констант. Например, формула ЧСТРОК(A1:F3) возвращает значение 3, т.к. в заданном диапазоне 3 строки: 1, 2, 3.

Рассмотрим функцию ВРЕМЯ(), у которой 3 аргумента: часы, минуты, секунды. Записав формулу =ВРЕМЯ(10;30;0), получим в ячейке значение 10:30:00 в формате Время. Покажем, что число 0,4375 соответствует 10:30 утра.

Функция ЕСЛИ(), английский вариант IF(), используется при проверке условий. Например, =ЕСЛИ(A1>100;"Бюджет превышен";"ОК!"). В зависимости от значения в ячейке А1 результат формулы будет либо "Бюджет превышен" либо "ОК!".

Функция АДРЕС(), английский вариант ADDRESS(), возвращает адрес ячейки на листе, для которой указаны номера строки и столбца. Например, формула АДРЕС(2;3) возвращает значение $C$2.

Функция ПРОСМОТР(), английский вариант LOOKUP(), похожа на функцию ВПР(): ПРОСМОТР() просматривает левый столбец таблицы и, если находит искомое значение, возвращает значение из соответствующей строки самого правого столбца таблицы. Существенное ограничение использования функции ПРОСМОТР() - левый столбец исходной таблицы, по которому производится поиск, должен быть отсортирован по возрастанию, иначе получим непредсказуемый (вероятнее всего неправильный) результат.

Функция ИНДЕКС(), английский вариант INDEX(), возвращает значение из диапазона ячеек по номеру строки и столбца. Например, формула =ИНДЕКС(A9:A12;2) вернет значение из ячейки А10, т.е. из ячейки расположенной во второй строке диапазона.

Функция ВЫБОР(), английский вариант CHOOSE(), возвращает значение из заданного списка аргументов-значений в соответствии с заданном индексом. Например, формула =ВЫБОР(2;"ОДИН";"ДВА";"ТРИ") вернет значение ДВА. Здесь 2 - это значение индекса, а "ОДИН";"ДВА";"ТРИ" это первый, второй и третий аргумент соответственно.

Функция СЧЁТЕСЛИМН(), английская версия COUNTIFS(), предназначена для подсчета строк, поля которых удовлетворяют двум критериям и больше.

Функция НЕЧЁТ(), английский вариант ISODD(), округляет до ближайшего нечетного целого, которое больше исходного значения (округляет вверх до ближайшего нечетного).

Функция ЧЁТН(), в английском варианте EVEN(), округляет до ближайшего четного целого, которое больше исходного значения (округляет вверх до ближайшего четного).

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), английский вариант SUBTOTAL(), используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые строки.

Функция СЧИТАТЬПУСТОТЫ(), английская версия COUNTBLANK(), подсчитывает количество пустых ячеек в заданном диапазоне. Также она подсчитывает ячейки с формулами, результатом вычисления которых является значение Пустой текст "" (например, =ЕСЛИ(1=1;"";1000))

Функция ПРОПНАЧ(), английский вариант PROPER(), делает первую букву в тексте ПРОПИСНОЙ (ЗАГЛАВНОЙ), например =ПРОПНАЧ("ааа") вернет "Ааа". =ПРОПНАЧ("ааа аа") вернет "Ааа Аа". В статье также показано как из "ааа аа" получить "Ааа аа" (заглавная только первая буква текстовой строки).

Функция СМЕЩ(), английский вариант OFFSET(), возвращает ссылку на диапазон ячеек. Размер диапазона и его положение задается в параметрах этой функции.

Функция И(), английский вариант AND(), проверяет на истинность условия и возвращает ИСТИНА если все условия истинны или ЛОЖЬ если хотя бы одно ложно.

Функция ИЛИ(), английский вариант OR(), проверяет на истинность условия и возвращает ИСТИНА если хотя бы одно условие истинно или ЛОЖЬ если все условия ложны.

Функция БИЗВЛЕЧЬ(), английский вариант DGET(), извлекает из столбца (списка) или таблицы отдельное значение, удовлетворяющее заданным условиям.

Задача функции ЕЧИСЛО(), английский вариант ISNUMBER(), - проверять являются ли значения числами или нет. Формула =ЕЧИСЛО(5) вернет ИСТИНА, а =ЕЧИСЛО("Привет!") вернет ЛОЖЬ.

Задача функции ЕТЕКСТ(), английский вариант ISTEXT(), - проверять является ли содержимое ячейки текстовым значением или нет. Формула =ЕТЕКСТ(5) вернет ЛОЖЬ, а =ЕТЕКСТ("Привет!") вернет ИСТИНА.

Задача функции ЕПУСТО(), английский вариант ISBLANK(), - проверять есть ли в ячейке число, текстовое значение, формула или нет. Если в ячейке А1 имеется значение 555, то формула =ЕПУСТО(А1) вернет ЛОЖЬ, а если ячейка А1 пуста, то ИСТИНА.

Функция ЯЧЕЙКА(), английская версия CELL(), возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях необходимость использования VBA. Функция особенно полезна, если необходимо вывести в ячейки полный путь файла.

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

Задача функции СТОЛБЕЦ(), английский вариант COLUMN(), - возвращать номер столбца. Формула =СТОЛБЕЦ(B1) вернет 2, т.к. столбец B - второй столбец на листе.

Функция ПОИСКПОЗ(), английский вариант MATCH(), возвращает позицию значения в диапазоне ячеек. Например, если в ячейке А10 содержится значение "яблоки", то формула =ПОИСКПОЗ ("яблоки";A9:A20;0) вернет 2, т.е. искомое значение "яблоки" содержится во второй ячейке диапазона A9:A20:А9 - первая ячейка (предполагается, что в ней не содержится значение "яблоки"), А10 - вторая, А11 - третья и т.д. (подсчет позиции производится от верхней ячейки).

Функция ЦЕЛОЕ(), в английском варианте INT(), округляет число до ближайшего меньшего целого.

Функция ТЕКСТ(), английская версия TEXT(), преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;"0,00 р.") вернет текстовую строку 100,00 р., а формула =ТЕКСТ(1;"#%") вернет текст (не число!) 100%. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.

Подсчитаем количество ячеек содержащих хоть какие-нибудь значения с помощью функции СЧЁТЗ(), английская версия COUNTA(). Предполагаем, что диапазон содержит числа, значения в текстовом формате, значения ошибки, пустые ячейки, значения Пустой текст "".

Функция СТРОЧН(), английская версия LOWER(), делает все буквы в тексте строчными (в нижнем регистре).

Функция СЛЧИС(), английский вариант RAND(), возвращает случайное вещественное число (равномерно распределенное), которое большее или равно 0 и меньше 1 (например, 0,797285074257933).

Функция СЖПРОБЕЛЫ(), английский вариант TRIM(), удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ() используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.

Функция РИМСКОЕ(), английский вариант ROMAN(), преобразует арабское число в римское, которое возвращается в текстовом формате.

Функция ПСТР(), английский вариант MID(), возвращает указанное число знаков из текстовой строки, начиная с указанной позиции. Формула =ПСТР(A1;8;5) из строки "Первый канал - лучший" извлекает слово "канал". Т.е. функция ПСТР() возврачает часть текста из середины строки.

Функция РАНГ(), английский вариант RANK(), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 будет иметь ранг 1, т.к. это наименьшее число, число 10 - ранг 2, а 20 - ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).

Функция ПРОПИСН(), английский вариант UPPER(), делает все буквы в тексте ПРОПИСНЫМИ (ЗАГЛАВНЫМИ).

Функция ОСТАТ(), английский вариант MOD(), возвращает остаток от деления аргумента «число» на значение аргумента «делитель». Результат имеет тот же знак, что и делитель.

Функция ПРАВСИМВ(), английский вариант RIGHT(), возвращает последние знаки текстовой строки исходя из заданного количества знаков. Формула ПРАВСИМВ("Мария Викторовна";10) вернет "Викторовна".

Функция ПОВТОР(), английский вариант REPT(), используется для заполнения ячейки заданным количеством вхождений текстовой строки. Формула =ПОВТОР("*-"; 6) вернет *-*-*-*-*-*-.

Функция ОТБР(), английский вариант TRUNC(), отбрасывает дробную часть числа.

Функция ОКРУГЛТ(), английский вариант MROUND(), возвращает число, округленное с нужной точностью.

Функция ОКРУГЛ(), английский вариант ROUND(), округляет число до указанного количества десятичных разрядов: формула =ОКРУГЛ(2,15;1) вернет 2,2

Функция ОКРУГЛВНИЗ(), английский вариант ROUNDDOWN(), округляет число до ближайшего меньшего по модулю значения: формула =ОКРУГЛВНИЗ(3,2; 0) вернет 3.

Функция НЕ(), английский вариант NOT(), используется в тех случаях, когда необходимо убедиться, что значение не равно некой конкретной величине.

Функция ОКРУГЛВВЕРХ(), английский вариант ROUNDUP(), округляет число до ближайшего большего по модулю значения.

Функция НАИБОЛЬШИЙ(), английский вариант LARGE(), возвращает k-ое по величине значение из массива данных. Например, формула =НАИБОЛЬШИЙ(A2:B6;1) вернет максимальное значение (первое наибольшее) из диапазона A2:B6.

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

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

Функция ЛЕВСИМВ(), английский вариант LEFT(), возвращает первые знаки текстовой строки исходя из заданного количества знаков. Формула ЛЕВСИМВ("Мария Викторовна";5) вернет "Мария".

Функция КОДСИМВ(), английский вариант CODE(), возвращает числовой код первого знака в текстовой строке.

Функция ЗНАЧЕН(), английский вариант VALUE(), преобразует строку текста в число (там где это возможно).

Функция ЗАМЕНИТЬ(), английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. "Указанную часть знаков" означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Функция ДЛСТР(), английский вариант LEN(), возвращает количество знаков в текстовой строке. Например, записав формулу =ДЛСТР("8 марта"), получим число 7 - количество знаков в текстовой строке с учетом пробела.

Функция ДЕНЬ(), английский вариант DAY(), возвращает день, соответствующий заданной дате. День определяется как целое число в диапазоне от 1 до 31.

Функция ГИПЕРССЫЛКА(), английский вариант HYPERLINK(), создает ярлык или гиперссылку, которая позволяет открыть страницу в сети интернет, файл на диске (документ MS EXCEL, MS WORD или программу, например, Notepad.exe) или перейти к указанному листу (диапазону ячеек) в текущей книге

Функция ВРЕМЗНАЧ(), английский вариант TIMEVALUE(), преобразует значение из текстового формата в формат Время (где это возможно).