Стандартные функции Excel

Для удобства работы функции в MS Excel поделены на несколько категорий:

Текстовые функции служат для решения задач, связанных с обработкой строковых значений.

ЛЕВСИМВ (текст; количество_знаков) – возвращает указанное число знаков с начала текстовой строки

текст – текстовая строка, содержащая извлекаемые знаки;

количество_знаков – количество знаков, извлекаемых функцией ЛЕВСИМВ.

ПРАВСИМВ (текст; число_знаков) – возвращает указанное число знаков с конца текстовой строки. В остальном функция аналогична функции ЛЕВСИМВ.

СЦЕПИТЬ (текст1; текст2;...) – объединяет несколько текстовых строк в одну.

текст1, текст2,... – это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.

ПРИМЕР

Получить ФИО гражданина, если известы его полные имя, фамилия и отчество.

Рис. 9. Получение ФИО

ЗАМЕНИТЬ (старый_текст; нач_поз; число_знаков; новый_текст) – замещает указанную часть знаков текстовой строки другой строкой текста.

старый_текст – текст, в котором желательно заменить некоторые знаки;

нач_поз – позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст;

число_знаков – число знаков в тексте старый_текст, которые заменяются текстом новый_текст;

новый_текст – текст, который заменяет знаки в тексте старый_текст.

ПОДСТАВИТЬ (текст; стар_текст; нов_текст; номер_вхождения) – подставляет текст нов_текст вместо текста стар_текст в текстовой строке.

текст – это либо текст, либо ссылка на ячейку, содержащую текст, в котором подставляются знаки;

стар_текст – заменяемый текст;

нов_текст – текст, на который заменяется стар_текст;

номер_вхождения – определяет, какое вхождение текста стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то заменяется только это вхождение текста стар_текст. В противном случае, каждое вхождение текста стар_текст в текстовой строке заменяется на текст нов_текст.

СИМВОЛ (число) – возвращает символ с заданным кодом.

число – это число от 1 до 255, указывающее нужный знак. Знаки выбираются из кодовой таблицы.

ПРИМЕР

Требуется исправить наиболее распространённые при написании текста на белорусском языке ошибки: использование русской буквы (и) вместо белорусской буквы (i) и неверное использование буквы (у) там, где требуется буква (ў).

Рис. 10. Исправление текста

Обратите внимание на то, что функция ЗАМЕНИТЬ вложена в качестве первого аргумента в функцию ПОДСТАВИТЬ. В этом случае текст из ячейки С9 вначале обрабатывается функцией ЗАМЕНИТЬ, которая меняет один символ на 16-й позиции на символ (ў), получаемый при помощи функции СИМВОЛ(162). Затем полученный от функции ЗАМЕНИТЬ текст передаётся в функцию ЗАМЕНИТЬ, которая дорабатывает его, заменяя все буквы (и) на буквы (i).

НАЙТИ (искомый_текст; просматриваемый_текст; нач_позиция). Функция НАЙТИ находит вхождение одной текстовой строки искомый_текст в другую текстовую строку просматриваемый_текст и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.

икомый_текст – это искомый текст;

посматриваемый_текст – это текст, включающий искомый текст;

нч_позиция – это позиция знака, с которой следует начинать поиск. Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1. Если искомый_текст не входит в просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!. Если нач_позиция больше длины строки просматриваемый_текст, то функция НАЙТИ возвращает значение ошибки #ЗНАЧ!.

ПОИСК (искомый_текст; текст_для_поиска; нач_позиция) – функция выполняет те же операции, что и функция НАЙТИ. Имеются два основных различия. Функция ПОИСК не учитывает регистр и допускает использование подстановочных знаков (знак вопроса (?) соответствует любому знаку; звездочка (*) соответствует любой последовательности знаков) в аргументе искомый_текст.

ПРИМЕР

Из записи-описания товара нужно выбрать название изделия и производителя.

Рис. 11. Получение строки описания товара

Здесь сначала при помощи функции НАЙТИ находится позиция первого знака (,) в тексте ячейки D19, а затем, при помощи функции ЛЕВСИМВ выбираются все символы от начала до найденной позиции.

Попробуйте самостоятельно объяснить присутствие в выражении (-1).

Для решения этой задачи можно использовать и функцию ПОИСК.

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

ДАТА(год; месяц; число) – возвращает код-даты, соответствующий числовым значениям года, месяца, числа:

год – аргумент, который может иметь от одной до четырех цифр;

месяц – число, представляющее месяц года. Если значение аргумента больше 12, введенное число месяцев отсчитывается от первого месяца указанного года. Например, ДАТА(2008; 14; 2) возвращает число, соответствующее 2 февраля 2009 года;

день – число, представляющее день месяца. Если значение аргумента больше числа дней в указанном месяце, введенное число дней отсчитывается от первого дня месяца. Например, ДАТА(2008; 1; 35) возвращает число, соответствующее 4 февраля 2008 года.

ВРЕМЯ(часы; минуты; секунды) – возвращает целое число, представляющее определенное время. Время в числовом формате, возвращаемое функцией:

часы – целое число от 0 (нуля) до 32767, задающее часы. Если значение параметра часы больше 23, его можно разделить на 24; остаток от деления будет соответствовать значению часов. Например, ВРЕМЯ(27; 0; 0) = ВРЕМЯ(3; 0; 0) = 0, 125 = 3: 00 AM;

минуты – число от 0 (нуля) до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. Например, ВРЕМЯ(0; 750; 0) = ВРЕМЯ(12; 30; 0) = 0, 520833 = 12: 30 PM;

секунды – число от 0 (нуля) до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды. Например, ВРЕМЯ(0; 0; 2000) = ВРЕМЯ(0; 33; 22) = 0, 023148 = 12: 33: 20 AM.

СЕГОДНЯ() – возвращает код текущей даты.

ГОД(дата) – возвращает год, соответствующий аргументу дата. Год определяется как целое в интервале 1900-9999.

МЕСЯЦ (дата) – возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число диапазоне от 1 (январь) до 12 (декабрь).

ДЕНЬ (дата) – возвращает день в дате, заданной в числовом формате. День возвращается как целое число диапазоне от 1 до 31.

У функций ГОД, МЕСЯЦ и ДЕНЬ аргумент дата представляет собой дату (“12.10.2005”) или код-даты (38637).

ЧАС(время) – возвращает час, соответствующий заданному времени в числовом формате. Час определяется как целое в интервале от 0 до 23.

МИНУТЫ(время) – возвращает минуты, соответствующие аргументу время. Минуты определяются как целое число в интервале от 0 до 59.

СЕКУНДЫ(время) – возвращает секунды, соответствующие аргументу время. Секунды определяются как целое в интервале от 0 до 59.

У функций ЧАС, МИНУТЫ и СЕКУНДЫ аргумент время представляет собой время (“12: 05”) или код-даты (0, 50347).

ПРИМЕР

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

Рис. 12. Вычисление даты

Обратите внимание: в ячейке D34 для получения текущей даты записана функция СЕГОДНЯ().

Логические функции позволяют составлять сложные логические выражения, а также организовывать разветвляющиеся вычисления:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь) – возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ:

лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ;

значение_если_истина – это выражение, результат вычисления которого возвращается, если лог_выражение равно ИСТИНА;

значение_если_ложь – это выражение, результат вычисления которого возвращается, если лог_выражение равно ЛОЖЬ.

И(логическое_значение1; логическое_значение2;...) – возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

ИЛИ(логическое_значение1; логическое_значение2;...) – возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

К логическим функциям можно также отнести функции проверки типа значений, хотя они и вынесены в отдельную группу. Принцип работы у этих функций одинаков: проверяется тип значения и возвращается значение ИСТИНА или ЛОЖЬ в зависимости от соответствия значения аргумента определённому типу (ЕОШИБКА (значение) – проверка на значение ошибки (#ЧИСЛО!, #ИМЯ!), ЕЧИСЛО (значение) – проверка на числовое значение ЕТЕКСТ (значение) – проверка на текстовое значение, ЕПУСТО(значение) – проверка отсутствия значения.

ПРИМЕР

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

Рис. 13. Анализ параметра

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

ПРОСМОТР (искомое_значение; просматриваемый_вектор; вектор_результатов) – просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор) в поисках определенного значения и возвращает значение из другого столбца или строки:

искомое_значение – это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение;

просматриваемый_вектор – интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями. Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания, в противном случае функция ПРОСМОТР может вернуть неверный результат;

вектор_результатов – интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.

• Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в просматриваемом векторе, которое меньше, чем искомое_значение.
• Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

В Excel широко представлены математические функции:

СУММ(число1; число2;...) – суммирует все числа в интервале ячеек.

число1, число2,... – от 1 до 30 аргументов, для которых требуется определить итог или сумму. Если аргумент является массивом или ссылкой, то суммируются только содержащиеся в них числа. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

ПРИМЕР

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

Рис. 14. Вычисление доли зарплаты

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования) – суммирует ячейки, заданные критерием:

диапазон – диапазон вычисляемых ячеек;

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

диапазон_суммирования – диапазон для суммирования.

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

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

диапазон – диапазон, в котором нужно подсчитать ячейки;

критерий – аналогично параметру критерий функции СУММЕСЛИ.

ПРИМЕР

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

Рис. 15. Вычисление общей суммы выплат и средней
заработной платы

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

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

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