Наиболее часто встречающиеся функции
СУММ (Список) – математическая функция, определяющая сумму всех числовых значений в Списке. Список может состоять из адресов ячеек и блоков, а также числовых значений. Например, СУММ(A3:E3;230).
СРЗНАЧ (Список) – статистическая функция. Определяет среднее арифметическое значение всех перечисленных в Списке величин.
ЕСЛИ (Лог_выражение;Истина;Ложь)- логическая функция, проверяющая на истинность заданное логическое условие. Если условие выполняется, то результатом функции является значение аргумента «Истина». Если условие не выполняется, то результатом становится значение аргумента «Ложь».
В Excel встроены более 300 функций. Использовать их позволяет мастер функций. Все функции в мастере разбиты на категории, поэтому сначала выбирают категорию, а затем функцию. Мастер выводит подсказку: шаблон для функции, который помогает понять правила ее записи, и описание ее назначения.
5.4.3. Автоматическое изменение относительных ссылок при копировании и перемещении формул
Буфер промежуточного хранения
Важной особенностью многих ЭТ является буфер промежуточного хранения. Буфер используется при выполнении команд копирования и перемещения для временного хранения копируемых или перемещаемых данных, после которого они направляются по новому адресу. При удалении данных они также помещаются в буфер. Содержимое буфера сохраняется до тех пор, пока в него не будет записана новая порция данных.
Буфер промежуточного хранения – область оперативной памяти, предоставляемая в распоряжении пользователя, при помощи которой он может перенести данные из одной части таблицы в другую, из одного окна (таблицы) в другое или из одного приложения Windows в другое.
Относительная и абсолютная адресация
При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Поэтому в ЭТ при написании формул используется понятие относительной и абсолютной ссылок.
Абсолютная ссылка - это не изменяющийся при копировании формулы адрес ячейки, содержащей исходное данное. Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.
Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащей исходное данное, не меняется. Для этого символ $ становится перед именем столбца и номером строки.
Частичная абсолютная ссылка указывается, если при копировании или перемещении не меняется номер строки или именование столбца. При этом символ в первом случае становится перед номером строки, а во втором - перед именованием столбца.
Относительная ссылка - это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное (операнд). Изменение адреса происходит по правилу относительной ориентации клетки с исходной формулой и клеток с операндами. Форма записи относительной ссылки совпадает с обычной записью.
Правило относительной ориентации клетки
Формула, где в качестве операнда используются ссылки ячеек, воспринимается системой как шаблон, а ссылки ячеек в таком шаблоне - как средство указания на место положение ячеек с операндами относительно ячейки с формулой.
Рассмотрим правило относительной ориентации клетки на примере:
Клетка со ссылкой С2 содержит формулу - шаблон сложения двух чисел, находящихся в ячейках А1 и В4. Эти ссылки являются относительными и отражают ситуацию расположения исходных данных в ячейках А1 и В4 и результаты вычисления по формуле в ячейке С2. По правилу относительной ориентации клеток ссылки исходных данных воспринимаются системой не сами по себе, а так, как они расположены относительно клетки С2 ссылка А1 указывает на клетку, которая смещена относительно клетки С2 на одну клетку вверх и на две клетки влево; ссылка В4 указывает на клетку, которая смещена относительно клетки С2 на две клетки вниз и одну клетку влево.
Копирование формул
Особенностью ЭТ является возможность автоматического изменения ссылок при копировании и перемещении формул. Копирование содержимого одной ячейки (блока ячеек) в другую (блока ячеек) производится для упрощения ввода однотипных данных и формул. При этом осуществляется автоматическая настройка относительных ссылок операндов. Для запрета автоматической настройки адресов используют абсолютные ссылки ячеек.
Исходная формула, подлежащая копированию или перемещению, воспринимается как некий шаблон, где указывается местоположение входных данных относительно местоположения клетки с формулой.
Копируемую формулу назовем формулой - оригиналом. Скопированную формулу - формулой - копией. При копировании формул действует правило относительной ориентации клеток. Поэтому после окончания копирования относительное расположение клеток, содержащих формулу копию и исходные данные (заданные относительными ссылками), остается таким же, как в формуле - оригинале. Поясним на примере.
До копирования После копирования
| A | B | C | |
| =C1+B2 |
| A | B | C | |
| =C1+B2 | =D1+C2 | =E1+D2 |
| A | B | C | |
| =$C$1+$B$2 | =$C$1+$B$2 | =$C$1+$B$2 |
| A | B | C | |
| =$C$1+$B$2 |
| A | B | C | |
| =$C1+B$2 |
| A | B | C | |
| =$C1+B$2 | =$C1+C$2 | =$C1+D$2 |
a. ри копировании формулы с использованием относительных ссылок происходит их автоматическая подстройка;
b. Применение абсолютных ссылок запрещает автоматическую настройку адресов, и копируемая формула сохраняет свой первоначальный вид;
c. С частично абсолютными ссылками.
Перемещение формул
В ЭТ часто перемещают данные ячейки (диапазона ячеек) в другую заданную ячейку (блок ячеек). После перемещения данных исходная ячейка окажется пустой. Это главное отличие перемещения от процесса копирования, в котором копируемая ячейка сохраняет свои данные. Перемещение формул также связано с автоматической подстройки входящих в нее адресов операндов. При перемещении формул, так же как при их копировании, действует правило относительной ориентации клеток. Поэтому после перемещения относительное расположение клеток, содержащих перемещенную формулу и исходные данные (заданные относительными адресами), сохраняется таким же, как в формуле - оригинале.
До перемещения
| A | B | C | |
| =A1+A2 |
| A | B | C | |
| =A1+A2 |
| A | B | C | |
| =A3*4 | |||
| =A1+A2 |
После перемещения
| A | B | C | |
| =A1+A2 |
| A | B | C | |
| =C1+C2 |
| A | B | C | |
| =C3*4 | |||
| =A1+A2 |
5.4.5. Технология работы с электронными таблицами
Хотя работа в каждой ЭТ имеет свои особенности, можно говорить о некоторой обобщенной (усредненной) технологии работы с ней. Изобразим технологию работы в виде схемы (рис. 5.4.1).
На этапе 1 формируется структура таблицы. Структура включает: определение заголовка таблицы, названий строк и столбцов, а также ввод в ячейки таблицы исходных данных, формул и функций.
На этапе 2 производится работа с данными, которая состоит в исследовании сформированной таблицы. Также исследование может быть связано с использованием определенных математических моделей (моделированием), методов одновременной работы с несколькими таблицами и методов работы сданными.
Математические модели помогают пользователю на основе имеющейся таблицы получить новую информацию решением таких задач компьютерного моделирования: «Что, ес
Рис. 5.4.1. Обобщенная технология работы с ЭТ
ли?», анализ чувствительности и др. Решение задач типа: «Что будет, если?» - дает возможность узнать, как изменятся выходные параметры при изменении одной или нескольких входных величин (условий). Расширением таких задач являются задачи анализа чувствительности, позволяющие определить, как изменится решение модели при изменении одной или нескольких входных величин с заданным шагом в определенном диапазоне значений. Обратной по отношению к задаче «Что будет, если?» - является задача «Как сделать, чтобы?». Она возникает в случае, если целью является достижении определенного значения модели, и ищутся значения входных параметров, обеспечивающих достижение этой цели. Различные виды анализа данных, содержащихся в исходной таблице можно проводить с использованием встроенных функций и процедур. Так, входящие в состав ЭТ статистические функции могут использоваться в статистическом анализе или для прогноза содержащихся в таблице данных. Использование финансовых функций позволяет произвести анализ эффективности планируемых капиталовложений, рассчитать стоимость ценных бумаг или размер амортизационных отчислений. Для решения оптимизационных задач используются специальные модели математического программирования.
Часто фирма (предприятие) имеет центральный офис и несколько филиалов. В таких условиях появляется задача объединения различных документов и отчетов, приходящих из этих филиалов. Решение подобной задачи требует использования специальных многотабличных связей и программных методов для манипулирования с файлами и генерации отчетных форм. Одновременная работа с несколькими таблицами – это одна из возможностей работы с данными в электронных таблицах.
Иногда при работе с большими ЭТ требуется найти ту или иную строку (столбец) или отсортировать строки (столбцы) в нужном порядке. Для этого в ЭТ предусмотрен ограниченный программный инструментарий СУБД, позволяющий манипулировать строками и столбцами как компонентами БД.
Этап 3 технологии позволяет в графическом виде представить результаты, полученные на первом и втором этапах, и наиболее ярко их интерпретировать.
Этап 4 обеспечивает вывод результирующих данных на печать. При этом результаты могут быть распечатаны в табличном виде или в виде графических диаграмм.
Проектирование ЭТ
Проектирование ЭТ рассмотрим на примере форсирования прогноза финансовой деятельности некоторой компании за 5 лет.
Доход в любом году определяется как произведение объема продаж в натуральном выражении на продажную цену. Размер прибыли при этом определяется как разность между доходом и суммой расходов данного года.
Пользователем задаются исходные данные первого года. Данные всех последующих лет вычисляются ЭТ на основе допущений о характере их изменений в будущем.
| A | B | C | D | E | |
| Объем продаж, шт. | |||||
| Цена | $2.00 | $2.10 | $2.21 | $2.32 | |
| Доход | $20000 | $24780 | $30702 | $30702 | |
| Расходы | $15000 | $15750 | $16537 | $81364 | |
| Прибыль | $5000 | $9030 | $14165 | $20676 | |
| Прогнозные допущения | |||||
| Рост объема продаж | 18,00% | ||||
| Рост цен | 5,00% |
Эти данные расположены в нижнем левом углу таблицы.
Расходы и продажные цены определяются с учетом заданного роста цен, а объемы продаж – с учетом роста объемов продаж. При изменении данных допущений ЭТ должна немедленно пересчитывать значения всех прогнозируемых финансовых характеристик. Для этого ЭТ проектируется таким образом, чтобы прогнозируемые параметры определялись по формулам, зависимым от исходных прогнозных допущений.
Проектирование ЭТ основано на использовании уже рассмотренных нами команд копирования и перемещения.
Разработка любой ЭТ начинается с постановки цели. ЭТ, проектируемая для целей финансового прогнозирования, должна всякий раз пересчитывать финансовые показатели при изменении принятых прогнозных допущений.
Создание ЭТ начинается с ввода названий столбцов и строк.
Столбцами в нашей таблице являются годы, составляющие прогнозный период, а строками – прогнозируемые финансовые показатели. Кроме того, в таблицу входят прогнозные допущения.
| A | B | C | D | E | |
| Объем продаж, шт. | =(1+$B$10)*B3 | … | … | ||
| Цена | $2.00 | =(1+$B$11)*B4 | |||
| Доход | =B3*B4 | =C3*C4 | |||
| Расходы | =(1+$B$11)*B6 | ||||
| Прибыль | =B5-B6 | =C5-C6 | |||
| Прогнозные допущения | |||||
| Рост объема продаж | 18,00% | ||||
| Рост цен | 5,00% |
Далее вводятся данные первого года (в нашем примере 2006), которые частично задаются как числовые данные (объем продаж, продажная цена), а частично – как формулы. В ячейку В5 записываем формулу для вычисления дохода = B3*B4, а в ячейку В7 – формулу для вычисления прибыли =B5-B6. И вводим числовые данные, выражающие прогнозные допущения.
Далее вводим формулы в столбец второго года (2007). Эти формулы учитывают результаты первого года и, кроме того, отражают прогнозные допущения. Так, объем продаж в 2007 году определяется как объем продаж в 2006 году, умноженный на процент роста, указанный в прогнозных допущениях.
Объем продаж (2007) = (1+%роста)* объем продаж (2006)
ячейка В3
относительный адрес
ячейка В10
абсолютный адрес
ячейка С3
формула:
=(1+$B$10)*B3
В формуле используем относительные и абсолютные адреса, что позволит скопировать ее в оставшиеся колонки. Абсолютный адрес для ячейки, содержащей процент роста объема продаж, предполагает ее обязательное использование для всех расчетов в рамках данной ЭТ. Относительный адрес ячейки, содержащей объем продаж предыдущего года, дает возможность его подстройки при копировании формулы, поскольку сохраняется логика расчета объема продаж для последующих лет.
Пересчет остальных параметров из столбца В в столбец С выполняется аналогичным образом.
Остальные столбцы (D, E, F) заполняются простым копированием формул, содержащихся в столбце С. Команда копирования, при этом автоматически подстроит содержащиеся в них относительные адреса ячеек.
Построенная ЭТ дает возможность создавать всевозможные финансовые прогнозы, изменяя прогнозные допущения. Например, изменив одно или несколько прогнозных допущений, определить, что произойдет с прибылью в 2010году.
Объединение ЭТ
При работе с ЭТ часто возникает необходимость их объединения.
Среди инструментов объединения ЭТ отметим:
- организацию межтабличных связей;
- консолидацию ЭТ или их частей;
- объединение файлов.