Что такое функция рабочего листа

Обновлено: 02.07.2024

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

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

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

 СУММ(), ОКРУГЛ(), ОКРВВЕРХ(), ОКРВВЕРХ(), ОТБР();

 ВЫБОР(), ПОИСКПОЗ(), ГПР(), ВПР(), ИНДЕКС(), СЧЁТ(), СЧЁТЕСЛИ(),

СУММЕСЛИ() МИН(), МАКС();

 СЕГОДНЯ(), ДЕНЬНЕД(), ГОД(), МЕСЯЦ(), ДЕНЬ(), ДАТАМЕС(),

 функции работы с массивами (раздел 4.15).

Замечания.

1. Некоторые из перечисленных функций не входят в ядро Excel и становятся доступны только при загрузке дополнительного Пакета анализа из меню Сервис+Надстройки. Все такие функции помечены в тексте двумя галочками.

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

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

4.1. Функция суммирования

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

Функция имеет следующий формат:

СУММ( ; ; . ) – возвращает сумму чисел, входящих в список аргументов. Список может содержать до 30 элементов. Если в суммируемом блоке встречаются нечисловые элементы, они участвуют в суммировании как нули.

Выражение, содержащее функцию суммирования, можно ввести с клавиатуры, а можно с помощью пиктограммы Автосуммирование  (см. раздел 2.4) или комбинации клавиш Alt+=.

Функция суммирования аналогична простому перечислению слагаемых со знаком плюс. Следующие выражения в Excel совершенно эквивалентны: =СУММ(A1:A3) и =A1+A2+A3. В каких же случаях следует пользоваться функцией суммирования, а в каких сложением? Функция СУММ() удобна, если слагаемых много и они находятся в смежных ячейках. Очевидно, что гораздо проще набрать выражение СУММ(D1:D400), нежели адреса четырехсот слагаемых D1+D2+D3+ … + D400. Кроме того, при большом числе элементов это может оказаться технически невозможным, поскольку длина клеточного выражения ограничена. Имеется еще одно отличие. Если среди слагаемых имеется нечисловой элемент, он будет пропущен функцией СУММ(…), но приведет к ошибке при сложении через знак плюс.

Другое удобство заключается в том, что функция автоматически реагирует на изменение (включение/удаление строк в диапазоне суммирования) размеров таблицы. На рис. 4.1-1 приведены результаты суммирования данных из области А1:А2 в числовом и формульном виде с использованием обеих формул. Они одинаковы (результат равен 6).

Рис. 4.1-1 Рис. 4.1-2

На рис. 4.1-2 в диапазон суммирования между первой и второй строками вставлена строка, содержащая цифру 5. Видим, что результат, полученный с помощью функции СУММ(), правильный, поскольку функция автоматически учла новое слагаемое (выражение =СУММ(A1:A2) изменилось на =СУММ(A1:A3)). Формула же =А1+А2 осталась прежней и, как следствие, результат неверный. Такое адаптивное поведение вообще характерно для любых функций Excel – они, в основном, правильно учитывают изменения в структуре таблиц.

Однако здесь имеются два исключения – вставка строки непосредственно над областью обработки (*) и непосредственно над строкой (**), содержащей суммы (рис. 4.1-3).

Функция СУММ() не откликается на эти действия, продолжая считать своими аргументами только те клетки, которые были аргументами и до вставок (на рисунке заштрихованы), игнорируя новые строки. Таким образом, пользователю в каждом подобном случае предстоит редактировать итоговую строку, что, конечно, очень обременительно. В нашем примере в А6 придется изменить выражение =СУММ(A3:A4) на =СУММ(A2:A5).

Что можно предпринять для учета этих обстоятельств? Нужно сделать так, чтобы крайние верхняя и нижняя строки перестали быть таковыми.

1. Неудобств, связанных с вставкой данных над областью суммирования, можно легко избежать, если включить в нее заголовок столбца, т.е. изменить формулу в A4 на =СУММ(A1:A3). Такой заголовок обязательно должен быть нечислового типа, иначе он будет участвовать в суммировании. Если же он содержит числа, можно поступить иначе – непосредственно под заголовком, но выше собственно области данных включить пустую строку, участвующую в суммирования. Если затем такую строку скрыть, то внесение новой строки вверху видимого диапазона суммирования повлечет правильную автоматическую коррекцию итоговой функции.

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

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

 Начиная с версии Excel-2000 предусмотрен механизм, во многом снимающий эту проблему. Соответствующая установка имеется в меню Сервис+Параметры+вкладка Правка. Если здесь установить флажок Расширять форматы и формулы в списках, то дополнение таблицы в любом ее месте влечет автоматическое копирование в новую строку/столбец и форматов и формул из предыдущих строк/столбцов. Чтобы это свойство проявилось нужно только, чтобы повторяющиеся форматы и формулы были, по крайней мере, в трех из пяти предшествующих рядах таблицы. При этом не только автоматически добавляются формулы в заполняемой строке, но и корректируются итоговые выражения, находящиеся под/справа таблицы. Однако следует отметить, что указанный механизм может не работать в случаях сложных итоговых функций и в самом начале таблицы.

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Больше. Основные параметры

Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

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

Эта функция используется для суммирования значений в ячейках.

Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.

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

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

Данная функция применяется для поиска элемента в диапазоне ячеек с последующим выводом относительной позиции этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 7 и 38, то формула =MATCH(7,A1:A3,0) возвращает значение 2, поскольку элемент 7 является вторым в диапазоне.

Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента "номер_индекса".

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

Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.

Эта функция возвращает число дней между двумя датами.

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

Эта функция возвращает значение или ссылку на него из таблицы или диапазона.

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

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

Возвращает интегральную функцию бета-распределения.

Возвращает обратную интегральную функцию указанного бета-распределения.

Возвращает отдельное значение вероятности биномиального распределения.

Возвращает одностороннюю вероятность распределения хи-квадрат.

Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

Возвращает тест на независимость.

Соединяет несколько текстовых строк в одну строку.

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

Возвращает ковариацию, среднее произведений парных отклонений.

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

Возвращает экспоненциальное распределение.

Возвращает F-распределение вероятности.

Возвращает обратное значение для F-распределения вероятности.

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

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

Возвращает результат F-теста.

Возвращает обратное значение интегрального гамма-распределения.

Возвращает гипергеометрическое распределение.

Возвращает обратное значение интегрального логарифмического нормального распределения.

Возвращает интегральное логарифмическое нормальное распределение.

Возвращает значение моды набора данных.

Возвращает отрицательное биномиальное распределение.

Возвращает нормальное интегральное распределение.

Возвращает обратное значение нормального интегрального распределения.

Возвращает стандартное нормальное интегральное распределение.

Возвращает обратное значение стандартного нормального интегрального распределения.

Возвращает k-ю процентиль для значений диапазона.

Возвращает процентную норму значения в наборе данных.

Возвращает распределение Пуассона.

Возвращает квартиль набора данных.

Возвращает ранг числа в списке чисел.

Оценивает стандартное отклонение по выборке.

Вычисляет стандартное отклонение по генеральной совокупности.

Возвращает t-распределение Стьюдента.

Возвращает обратное t-распределение Стьюдента.

Возвращает вероятность, соответствующую проверке по критерию Стьюдента.

Оценивает дисперсию по выборке.

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

Возвращает распределение Вейбулла.

Возвращает одностороннее P-значение z-теста.

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

Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.

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

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

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

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

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

Некоторые функции, такие как СУММ (SUM), SIN (SIN) и ФАКТР (FACT), являются эквивалентами длинных математических формул, которые можно создать самим. Другие функции, такие как ЕСЛИ (IF) и ВПР (VLOOKUP), в виде формул реализовать невозможно.

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

Быстро получить информацию о функциях можно также с помощью кнопки Вставка функции.

Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Имя функции, например, СУММ (SUM) или СРЗНАЧ (AVERAGE) описывает операцию, которую эта функция выполняет. Аргументы функции Excel задают значения или ячейки, используемые функцией. Например, в следующей формуле СУММ – это имя функции, а С3:С5 – ее единственный аргумент. Эта формула суммирует числа в ячейках С3, С4 и С5:

Некоторые функции, такие как ПИ (PI) и ИСТИНА (TRUE), не имеют аргументов. Даже если функция не имеет аргументов, она все равно должна содержать круглые скобки:

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

В функции можно использовать до 30 аргументов, если при этом общая длина формулы не превосходит 1024 символов. Однако любой аргумент может быть диапазоном, содержащим произвольное число ячеек листа. Например, следующая функция имеет три аргумента, но суммирует числа в 29 ячейках (первый аргумент, А1:А5, ссылается на диапазон пяти ячеек от А1 до А5 и т.д.):

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

Комбинацию функций можно использовать для создания выражения, которое Excel сводит к единственному значению и интерпретирует его как аргумент. Например, в следующей формуле: SIN(A1*ПИ()) и 2*COS(A2*ПИ()) – это выражения, которые вычисляются и используются в качестве аргументов функции СУММ:

Типы аргументов

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

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

Аргументы функции могут быть числовыми. Например, функция СУММ в следующей формуле суммирует числа 327, 209 и 176:

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

В качестве аргумента функции могут использоваться текстовые значения. Например:

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

Если значение в ячейке А1 равно ИСТИНА, то выражение А1=ИСТИНА возвращает значение ИСТИНА, и функция ЕСЛИ возвращает строку Новая, а формула в целом возвращает текстовое значение Новая цена.

В качестве аргумента функции можно указать имя диапазона. Например, если выбрать команду Присвоить подменю Имя меню Вставка и назначить диапазону С3:С6 имя Получено, то для вычисления суммы чисел в ячейках С3, С4, С5 и С6 можно использовать формулу:

Аргументом функции может быть массив. Некоторые функции, такие как ТЕНДЕНЦИЯ (TREND) и ТРАНСП (TRANSPOSE) требуют задания массива аргументов. Другие функции не требуют задания массива, но могут использовать такие аргументы. Массивы могут содержать числовые, текстовые или логические значения.

В одной функции можно использовать аргументы различных типов. Например, в следующей формуле аргументами являются имя диапазона (Группа 1), ссылка на ячейку (A3) и числовое выражение (5*3), а сама формула возвращает единственное числовое значение:

Ввод функций в рабочем листе

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

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

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

Excel введет знак равенства, имя функции и пару круглых скобок. Затем Excel откроет второе окно диалога Мастера функций (без строки заголовка).

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

Рис. 2.2. Окно диалога Мастер функций – шаг 1 из 2

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

После нажатия кнопки ОК или клавиши Enter созданная функция появится в строке формул.

Некоторые функции, такие как ИНДЕКС (INDEX) имеют несколько форм (вариантов задания аргументов). Если выбрать такую функцию в списке Функция, Excel откроет дополнительное окно диалога Мастера функций, как на рис. 2.2, в котором можно выбрать нужную форму функции.

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

Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.

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

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

В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом вычисления формул, содержащих арифметические операторы, всегда является число. К арифметическим операторам относятся: +, -, *, /, %,^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, =, .

Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся:

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

, - оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ(В5:В15,С15:С25);

“ “ – оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ(В4:С6 В4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

Приоритет выполнения операций:

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

После ввода формулы в ячейку рабочего листа на экране в окне рабочего листа в ячейку выводится результат вычисления. Для вывода в ячейки формул следует установить флажок Формулы на вкладке Вид команды Параметры меню СЕРВИС.

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

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.Например: $A$1

Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D6.

Ссылки на ячейки других листов книги имеют следующий формат:

!ссылка на ячейку, например: Лист2!А1:А10 .

Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например: ‘лицевой счет’!А1:А10 .

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например: Лист1:Лист5!$A$1:$D$3 .

Ссылки на ячейки других книг имеют следующий формат:

[имя книги] !ссылка на ячейку, например: [книга2]Лист3!Е5:Е15.

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

В большинстве случаев работа с текстовыми значениями происходит так же, как с числами. Для объединения текстовых значений используется оператор &, причем таких операторов в формуле может быть несколько. С помощью оператора & можно объединять и числовые значения. В результате будет сформирован числовой текст. Этот оператор можно также использовать для объединения текстовых и числовых значений.

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

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

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

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

Для выделения всей строки или столбца следует щелкнуть по имени строки или столбца.

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

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

Ввод данных в активную ячейку осуществляется нажатием клавиши Enter после набора данных на клавиатуре. При этом выделение по умолчанию перемещается вниз по активному столбцу. Направление перемещения задается параметром Переход к другой ячейке после ввода в направлении на вкладке Правка команды Параметры меню СЕРВИС.

Для ввода данных в диапазон необходимо выделить нужный интервал ячеек и ввести данные последовательно в ячейки диапазона. Чтобы ввести одну и то же значение сразу в несколько ячеек, нужно сначала выделить эти ячейки, а затем ввести значение и при нажатой клавише Ctrl нажать Enter.

Допущенные при вводе ошибки могут быть исправлены до завершения ввода или после. До нажатия клавиши Enter можно вносить любые исправления, пользуясь строкой формул, можно также отменить весь ввод, нажав клавишу Esc.

Отмена ввода после нажатия клавиши Enter осуществляется командой Отменить меню ПРАВКА. Эта команда позволяет отменить до 16 последних выполненных действий для рабочего листа. Команда Вернуть меню ПРАВКА позволяет повторно выполнить отмененные действия.

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

Полное удаление редактируемых данных возможно также несколькими способами: удаление старых данных осуществляется при вводе новых данных, начинающимся с первой позиции; при нажатии клавиши DEL; при выполнении команды Очистить меню ПРАВКА Эта команда позволяет выбрать объект удаления:

- все – удаляет содержимое ячеек, форматирование и примечания;

- форматы – удаляет из ячейки только форматирование;

- содержимое – удаляет из ячейки только содержимое;

- примечание – удаляет только примечание, относящееся к ячейке.

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

Копирование и перемещение ячеек на рабочем листе можно выполнять с помощью команд меню ПРАВКА или мышью способом Drug & Drop. Последний способ используется при копировании или перемещении на небольшие расстояния. Для его использования должен быть установлен параметр Перетаскивание ячеек на вкладке Правка команды Параметры меню СЕРВИС.

Копирование и перемещение ячеек можно также производить с помощью оперативного меню.

При выполнении копирования ячеек вышеуказанными способами пользователь не может задавать параметры вставки. Excel предоставляет такую возможность, но только для копируемых (а не перемещаемых) данных. Для этого необходимо после выполнения команды Копировать меню ПРАВКА над выделенными ячейками выполнить команду Специальная вставка меню ПРАВКА Диалоговое окно этой команды позволяет:

1) Указать, что именно будет вставляться: формула, значение, форматы, примечание и т.д. Задается переключателем в поле Вставка.

2) Указать, какую операцию следует выполнить над содержимым буфера обмена и исходным содержимым диапазона назначения. Можно выполнять любые арифметические операции. Задается переключателем в поле Операция.

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

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

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

Для вставки ячеек следует выделить интервал, в который необходимо вставить ячейки, выполнить команду Ячейки менюВСТАВКА, указать направление, в котором необходимо переместить окружающие ячейки. При вставке ячеек осуществляется автоматическое изменение ссылок в формулах, расположенных в смещаемых ячейках.

Для вставки строк и столбцов следует щелкнуть по заголовку строки или столбца или выделить такое количество строк или столбцов, какое необходимо вставить, а затем выполнить команду Строки или Столбца меню ВСТАВКА. Вставка всегда происходит выше первой из выделенных строк и левее первого из выделенных столбцов. Удаление выделенных ячеек, строк, столбцов производится аналогично (команда Удалить меню ПРАВКА). При удалении ячеек следует указать направление смещения окружающих ячеек.

Над рабочими листами книги можно выполнять следующие операции:

- выделять рабочие листы;

- вставлять новые листы;

- перемещать и копировать листы в пределах одной книги или в другую книгу;

- защитить данные на рабочем листе.

Вставка и удаление листов производится также аналогично вставке строк и столбцов (соответствующие команды меню ВСТАВКА и ПРАВКА).

Рабочему листу можно присвоить любое символьное имя длиной не более 31 символа, включая пробелы. Переименовать лист можно следующими способами:

1) Щелкнуть дважды по ярлыку листа и ввести новое имя.

2) Выполнить команду Лист - Переименовать меню ФОРМАТ.

3) Выполнить команду Переименовать оперативного меню, выводимого при нажатии правой клавиши мыши, установленной на ярлыке листа.

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

Перемещение листа в другую книгу можно осуществить двумя способами:

2) С помощью мыши аналогично перемещению листа внутри книги.

Команды копирования листов аналогичны командам перемещения. Копирование мышью осуществляется протаскиванием указателя мыши при нажатой клавише Ctrl.

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

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

Чтобы установить ручное обновление вычислений, следует включить режим Вручную на вкладке Вычисления команды Параметры меню СЕРВИС.

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

Чтобы пересчитать только активный лист, следует нажать комбинацию клавиш Ctrl и F9. Даже если установлено ручное обновление вычислений, Excel обычно пересчитывает всю книгу при ее сохранении на жестком диске.

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

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

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

В общем виде любая функция может быть записана в виде:

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

2) Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3) Между именем функции и знаком «(« пробел не ставится.

4) Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

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

1) Арифметические и тригонометрические.

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

3) Информационные, предназначенные для определения типа данных, хранимых в ячейках.

4) Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

5) Статистические, предназначенные для выполнения статистического анализа данных.

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

7) Функции баз данных, предназначенные для анализа данных из списков или баз данных.

8) Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).

9) Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.

10) Нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic.

Командная кнопка "автосумма" – на панели инструментов предназначена для автосуммирования, т.е. для получения итоговых данных для любых указанных диапазонов данных с помощью функции СУММ. Технология работы с командой Автосуммирования следующая:

- выделить ячейку, в которой должен располагаться итог;

- будет предложен диапазон для суммирования (он окружен подвижной рамкой). Если диапазон неверен, следует выделить нужный диапазон (ячейка, смежные ячейки, несмежные ячейки – в любой комбинации);

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

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

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

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

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

Отслеживание зависимостей выполняется командой Зависимости меню СЕРВИС или командными кнопками панели инструментов Зависимости.

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

Понятие встроенных функций

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

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

Сама функция состоит из двух компонентов:

имя функции (например, СУММ, ЕСЛИ, ИЛИ), которое указывает на то, что делает данная операция;
аргумент функции - он заключен в скобках и указывает, в каком диапазоне действует формула, и какие действия будут выполнены.

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

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

Детально стоит остановиться на понятии аргумента функции, так как на них опирается работа со встроенными функциями Excel.

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

Обычно в качестве аргумента используются:

числа;
текст;
массивы;
ошибки;
диапазоны;
логические выражения.

Как вводить функции в Excel

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

После запуска данной команды откроется мастер создания функций – небольшое окно по центру рабочего листа.

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

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

Виды основных функций

Теперь стоит более детально рассмотреть, какие же есть в Microsoft Excel встроенные функции.

Всего 8 категорий:

математические (50 формул);
текстовые (23 формулы);
логические (6 формул);
дата и время (14 формул);
статистические (80 формул) – выполняют анализ целых массивов и диапазонов значений;
финансовые (53 формулы) – незаменимая вещь при расчетах и вычислениях;
работа с базами данных (12 формул) – обрабатывает и выполняет операции с базами данных;
ссылки и массивы (17 формул) – прорабатывает массивы и индексы.

Как видно, все категории охватывают достаточно широкий спектр возможностей.

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

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

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

Самая популярная функция из этого радела – СУММ (сумма).

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

Еще один лидер категории – ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Позволяет считать итоговую сумму с нарастающим итогом.

Функции ПРОИЗВЕД (произведение), СТЕПЕНЬ (возведение чисел в степень), SIN, COS, TAN (тригонометрические функции) также очень популярны, и, что самое важное, не требуют дополнительных знаний для их введения.

Функции дата и время

Формула ДАТА указывает в выбранной ячейке листа текущее время. Аналогично работает и функция ТДАТА – она указывает текущую дату, преобразовывая ячейку во временной формат.

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

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

Еще две неразлучные команды – ИСТИНА и ЛОЖЬ, позволяют отображать только те значения, которые были выбраны в качестве исходных. Все отличные от них данные отображаться не будут.

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

Текстовые функции

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

Так, формула БАТТЕКСТ преобразовывает любое число в каллиграфический текст. ДЛСТР подсчитывает количество символов в выбранном тексте.

Функция ЗАМЕНИТЬ находит и заменяет часть текста другим. Для этого нужно ввести заменяемый текст, искомый диапазон, количество заменяемых символов, а также новый текст.

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

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

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

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

Формулы МАКС() и МИН() отображают соответственно максимальное и минимальное значение в диапазоне.

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

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

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

Функции работы с базами данных

Еще одна важная категория, в которую входят встроенные функции в Excel для работы с базами данных. Собственно, они очень удобны для быстрого анализа и проверок больших списков и баз с данными. Все формулы имеют общее название – БДФункция, но в качестве аргументов используются три параметра:

база данных;
критерий отбора;
рабочее поле.

Все они заполняются в соответствии с потребностью пользователя.

База данных, по сути, это диапазон ячеек, которые объединены в общую базу. Исходя из выбранных интервалов, строки преобразовываются в записи, а столбцы – в поля.

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

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

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

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

Функции ссылки и массивы

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

Основная задача этих функций – вычленение из массива значений нужного элемента. Для этого задаются критерии отбора.

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

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