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

 

Формулы

Формулы – это выражение, начинающееся со знака равенства и состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены знаками арифметических операций. К знакам арифметических операций, которые используются в Excel относятся:сложение; вычитание; умножение; деление; возведение в степень.

Некоторые операции в формуле имеют более высокий приоритет и выполняются в такой последовательности:

 возведение в степень и выражения в скобках;

 умножение и деление;

 сложение и вычитание.

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

Внесение изменений в формулу
Для внесения изменений в формулу щелкните мышью на строке формул или клавишу F2. Затем внесите изменения и нажмите кнопку Ввода в строке формул или клавишу Enter. Если вы хотите внести изменения в формулу непосредственно в ячейке, где она записана, то дважды щелкните мышью на ячейке с этой формулой. Для отмены изменений нажмите кнопку Отмена в строке формул или клавишу Esc.

Использование ссылок

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

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

При копировании формул можно управлять изменением адресов ячеек или ссылок. Если перед всеми атрибутами адреса ячейки поставить символ “$” (например, $A$1), то это будет абсолютная ссылка, которая при копировании формулы не изменится. Изменятся только те атрибуты адреса ячейки, перед которыми не стоит символ “$”, т.е. относительные ссылки. Для быстрой установки символов “$” в ссылке ее необходимо выделить в формуле и нажать клавишу F4.

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

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

Распространение формул
Помимо копирования и перемещения формулу можно распространить на часть строки или столбца. При этом происходит изменение относительных ссылок. Для распространения формулы необходимо выполнить следующие действия:
1. Установите курсор в ячейку с формулой.
2. Подведите указатель мыши к маркеру заполнения. Изображение указателя изменяется на черный крестик.
3. Нажмите левую кнопку мыши и, удерживая ее нажатой, перемещайте курсор до нужного места. Для завершения распространения формулы отпустите кнопку.

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

 

Функции Excel

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

 

Использование функций

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

Чтобы использовать функцию, её нужно ввести как часть формулы в ячейку листа. Формула должна начинаться со знака равенства (=), за которым следует набор величин, операторов и функций. Если функция стоит в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле [1,c18].

Скобки используются для обозначения начала и конца списка аргументов. Скобки должны быть парными, пробелы перед скобками или после них не допускаются. Внутри скобок должны располагаться аргументы. Список аргументов может состоять из чисел, текста, логических величин, массивов, значений ошибок или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах Microsoft Excel можно использовать до семи уровней вложения функций [1,c18].

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

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические. В распоряжении пользователя предоставляется более 300 (!) функций [2,c18].

 

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

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

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

Функции просмотра и ссылок позволяет «просматривать» информацию, хранящуюся в списке или таблице, а также обрабатывать ссылки.

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

Логические функции являются неотъемлемыми компонентами многих формул.

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

Функция ЕСЛИ (арг_лог, арг1, арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог приобретает значение ЛОЖЬ.

Если арг_лог имеет значение ИСТИНА и значение арг1 опущено, то возвращается значение ИСТИНА. Если арг_лог имеет значение ЛОЖЬ и значение арг2 опущено, то возвращается значение ЛОЖЬ.

При конструировании более сложных проверок в качестве значений аргументов арг1 и арг2 могут быть вложены до семи функций ЕСЛИ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Пример.

1. Введите в ячейку Е3 формулу = ЕСЛИ(Е1= 5; «Правильный ответ»; «Ошибка»).

2. Введите в ячейку Е1 число 4. В ячейке Е3 появилось слово «Ошибка».

3. Введите в ячейку Е1 число 5. В ячейке Е3 появилась надпись «Правильный ответ». Такая конструкция позволяет создавать достаточно сложные тестовые программы, например, такие, в которых следует выбрать номер правильного варианта из многих.

4. Результатом работы логических функций ^ИСТИНА()и ЛОЖЬ() являются логические ИСТИНА или ЛОЖЬ [1,c18].

Функция НЕ меняет значение своего аргумента на противоположное логиче­ское значение и обычно используется в сочетании с другими функциями. Эта функция возвращает логическое значение ИСТИНА, если аргумент имеет значение ЛОЖЬ, и логическое значение ЛОЖЬ, если аргумент имеет значе­ние ИСТИНА. Например, следующая формула возвращает текст Прошел, если значение в ячейке А1 не равно 2: =ЕСЛИ(НЕ(А1=2);"Прошел";"Не прошел").

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

Хотя функция ^ИЛИ имеет те же аргументы, что и И, результаты получаются совершенно различными. Например, следующая формула возвращает текст «Сдал», если средний балл больше 75 или если студент имеет меньше 5 пропус­ков занятий без уважительных причин: =ЕСЛИ(ИЛИ(С4<5;Р4>75%);"Сдал";"Не сдал"). Таким образом, функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвра­щает логическое значение ИСТИНА, только если все логические выражения истинны.

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

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

Функция ^ОКРУГЛ(число; число разрядов) округляет первый аргумент функции до числа знаков, заданного вторым аргументом. Число разрядов — количество десятичных разрядов, до которого нужно округлить число. Если число разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой. Если число разрядов равно 0, то число округляется до ближайшего целого. Если число разрядов меньше 0, то число округляется слева от десятичной запятой [3,c18].

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

Функция ^ОКРУГЛВВЕРХ, ОКРУГЛВНИЗ подобна функции ОКРУГЛ, за тем исключением, что ОКРУГЛВВЕРХ округление всегда производится с избытком. А при функции ОКРУГЛВНИЗ число всегда округляется с недостатком.

Функция ПРОИЗВЕД перемножает все числа, задаваемые ее аргументами. Excel игнорирует любые пустые ячейки, текстовые и логические значения. Функция ПРОИЗВЕД может иметь до 30 аргументов.

Функция СУММ суммирует значение всех ячеек указанных диапазонов.

Пример. Сумм (А10:С15; Е3:В8)

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

Поскольку ^СУММ является очень популярной функцией, на стандартной панели инструментов имеется специальная кнопка для ввода этой функции.

Функция КОРЕНЬ возвращает положительный квадратный корень числа.

Аргумент число должен быть положительным числом. Например, следующая функция возвращает значение 4: =КОРЕНЬ(16)

Если число отрицательное, КОРЕНЬ возвращает ошибочное значение: =#ЧИСЛО!.

Функция ^СТЕПЕНЬ(число; степень) возвращает результат возведения числа в степень. Число — основание. Оно может быть любым вещественным числом. Степень — показатель степени, в которую возводится основание.

Например, следующая функция возвращает значение 25: =СТЕПЕНЬ(5;2)

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

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

Функция ТЕКСТ (значение; формат) преобразует число в текстовую строку с заданным форматом. Аргумент значение может быть любым числом, формулой или ссылкой на ячейку. Аргумент формат определяет, в каком виде отображается возвраща­емая строка.. Для задания необходимого формата можно использовать любой из символов форматирования ($, #, 0 и т. д.) за исключением звездочки (*).

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

Если «число повторений» равно 0 (нулю), то функция ^ПОВТОР возвращает "" (пустой текст).Если «число повторений» не целое, то оно усекается. Результат функции ПОВТОР не должен превышать 32 767 знаков, в противном случае ПОВТОР возвращает сообщение об ошибке #ЗНАЧ!.

Функция ^СОВПАД сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для того, чтобы проверить, входит ли некоторый текст в документ [3,c18].

Например, если ячейки С5 и А3 рабочего листа содержат один и тот же текст. Тогда, следующая формула возвращает значе­ние ИСТИНА: =СОВПАД(С5;А3)

Функции ^ЕТЕКСТ и ЕНЕТЕКСТ проверяют, явля­ется ли значение текстовым.

Предположим, вы хотите определить, является ли значение в ячейке А8 текстом. Если в ячейке А8 находится текст или формула, которая возвращает текст, можно использовать формулу: =ЕТЕКСТ(А8). В этом случае Excel возвращает логическое значение ИСТИНА. Аналогично, если вы проверите ту же ячейку, используя формулу =ЕНЕТЕКСТ(А8) Excel возвращает логическое значение ЛОЖЬ.

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

Excel поддерживает пять встроенных логарифмических функций: LOG10, LOG, LN, ЕХР и СТЕПЕНЬ. В этом разделе я расскажу только про функции LOG, LN и ЕХР.

Функция LOG (число; основание) возвращает логарифм положительного числа по заданному основанию.

Например, следующая формула возвращает значение 2,321928095, то есть логарифм 5 по основанию 2: =LOG(5; 2). Если вы не укажете аргумент основание, Excel примет его равным 10.

Функция LN возвращает натуральный (по основанию е) логарифм положительного числа, указанного в качестве аргумента [4,c18]..

Например, следующая формула возвращает значение 0,693147181: =LN(2).

Функция ЕХР вычисляет значение константы е (приблизительно 2,71828183), возведенной в заданную степень.

Функция ЕХР является обратной по отношению к LN. Например, пусть ячейка А1 содержит формулу =LN(8), тогда следующая формула возвращает значение 8: =ЕХР(А1).

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

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

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