Табличный процессор Excel позволяет:
– решать математические задачи: выполнять табличные вычисления, вычислять значения и исследовать функции, строить графики функций, решать уравнения, работать с матрицами и комплексными числами и т.д.;
– осуществлять математическое моделирование и численное экспериментирование;
– проводить статистический анализ, осуществлять прогнозирование и оптимизацию;
– реализовывать функции базы данных – ввод, поиск, сортировку, фильтрацию и анализ данных;
– вводить пароли или устанавливать защиту некоторых ячеек таблицы, скрывать фрагменты таблицы (или всю таблицу);
– наглядно представлять данные в виде диаграмм или графиков;
осуществлять импорт/экспорт, обмен данными с другими программами;
– осуществлять многотабличные связи;
– подготавливать выступления, доклады и презентации благодаря встроенному режиму презентаций.
С помощью инструмента Карта можно создавать географические карты на основании данных рабочих листов, организованных специальным образом. Один столбец должен содержать такие географические данные, как названия городов, штатов, областей или стран. При этом в карту можно добавлять надписи, дополнительные данные и применять к ним форматирование.
Прежде всего, надо организовать информацию рабочего листа по столбцам. Рабочий лист должен содержать дополнительные данные для каждого средства карты, например объемы продаж по каждому штату (области). Можно включить эти данные в ячейки, выделяемые при создании карты.
Карту можно использовать для анализа географических данных путем добавления подписей, текста, флажков. Например, форма карты прекрасно подойдет для отображения и анализа данных о продажах в разных регионах.
После создания карты можно изменять ее размер, модифицировать легенду, изменять другие элементы. На карту можно добавлять такие подписи, как названия городов и стран (которые уже записаны на рабочем листе), либо создавать надписи с «посторонним текстом». С помощью кнопки Вывод подписей объектов панели инструментов Карта помечаются важные точки на карте, а с помощью кнопки Нанесение надписей вставляются примечания.
Для представления данных на карте другим способом можно добавить круговую диаграмму или гистограмму. Возможно, именно столбики гистограммы или секторы круговой диаграммы помогут лучше представить данные, связанные с географическими областями.
Основные типы данных: числа, как в обычном, так и в экспоненциальном формате, текст – последовательность символов, состоящая из букв, цифр и пробелов, формулы. Формулы должны начинаться со знака равенства и могут включать в себя числа, имена ячеек, функции (математические, статистические, финансовые, текстовые, дата и время и т. д.) и знаки математических операций.
Электронные таблицы позволяют работать с основными типами данных:
– числами, например (1.2367 0.25Е-7)
– текстом, например (–23.569Е+8рс)
– формулами, например =SUM(A1:F25;D47)
Числа могут быть представлены как в обычном виде, так и в экспоненциальном и выравниваются по разрядам при размещении по столбцам для удобства пользователя.
В электронных таблицах существует три вида ссылок:
– абсолютные;
– относительные;
– смешанные.
Различия между ними проявляются при копировании формулы из активной ячейки в другую ячейку.
Относительная ссылка в формуле используется для указания адреса ячейки, в которой находится формула. При перемещении или копировании формулы из активной ячейки относительные ссылки автоматически обновляются в зависимости от нового положения формулы.
Абсолютная ссылка в формуле используется для указания фиксированного адреса ячейки. При перемещении или копировании формулы абсолютные ссылки не меняются. В абсолютных ссылках перед неизменяемым значением адреса ячейки ставится знак доллара. Если символ доллара стоит перед буквой ($А1), то координата столбца – абсолютная, а строки – относительная. Если символ доллара стоит перед цифрой (А$1), то координата столбца – относительная, а строки – абсолютная. Такие ссылки называются смешанными.
После того как формула введена в ячейку, вы можете ее перенести, скопировать или распространить на блок ячеек.
При перемещении формулы в новое место таблицы ссылки в формуле не изменяются, а ячейка, где раньше была формула, становится свободной. При копировании формула перемещается в другое место таблицы, ссылки изменяются, но ячейка, где раньше находилась формула, остается без изменения.
При копировании формул возникает необходимость управлять изменением адресов ячеек или ссылок. Для этого перед символами адреса ячейки или ссылки устанавливаются символы $. Изменяются только те атрибуты адреса ячейки, перед которыми не стоит символ $. Если перед всеми атрибутами адреса ячейки поставить символ $, то при копировании формулы ссылка не изменится.
Например, если в записи формулы ссылку на ячейку D7 записать в виде $D7, то при перемещении формулы будет изменяться только номер строки 7. Запись D$7 означает, что при перемещении будет изменяться только символ столбца D. Если же записать адрес в виде $D$7, то ссылка при перемещении формулы на этот адрес не изменится и в расчетах будут участвовать данные из ячейки D7. Если в формуле указан интервал ячеек G3:L9, то управлять можно каждым из четырех символов – G, 3, L и 9, – помещая перед ними символ $.
Трехмерные ссылки используются при необходимости анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, перед которой ставятся имена листов. Microsoft Excel использует все листы, хранящиеся между начальным и конечным именами, указанными в ссылке. Например, формула = СУММ(Лист2:Лист13!В5) суммирует все значения, содержащиеся в ячейке В5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
Трехмерные ссылки могут быть использованы для создания ссылок на ячейки на других листах, определения имен и создания формул с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЕТ, СЧЕТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел), а также в формулах, использующих неявное пересечение.
Изменения в трехмерных ссылках при перемещении, копировании, вставке или удалении листов. Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. Предположим, что используется формула =СУММ(Лист2:Лист6!А2:А5), суммирующая содержимое ячеек с А2 по А5 с Лист2 по Лист6 включительно:
– вставка или копирование. Если между листами 2 и 6 книги вставить новые листы, Microsoft Excel добавит в сумму содержимое ячеек с А2 по А5 на новых листах;
– удаление. Если между листами 2 и 6 книги удалить листы, Microsoft Excel исключит из суммы содержимое ячеек удаленных листов;
– перемещение. Если переместить листы, находящиеся между листами 2 и 6 книги, и разместить их таким образом, что они будут расположены перед листом 2 или после листа 6, Microsoft Excel исключит из суммы содержимое ячеек перемещенных листов;
– перемещение граничного листа. Если переместить лист 2 или 6 в новое место книги, Microsoft Excel включит в сумму содержимое ячеек листов, находящихся между листами 2 и 6 включительно;
– удаление граничного листа. Если удалить лист 2 или 6, Microsoft Excel включит в сумму содержимое ячеек листов, находившихся между ними.
Стиль ссылок R1C1. Также можно использовать стиль ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 полезен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 Microsoft Excel указывает положение ячейки буквой R, за которой идет номер строки, и буквой С, за которой идет номер столбца.
При записи макроса Microsoft Excel записывает некоторые команды с использованием стиля ссылок R1C1. Например, если записывается такая команда, как нажатие кнопки Автосумма для вставки формулы, суммирующей диапазон ячеек, Microsoft Excel использует при записи формулы стиль ссылок R1C1, а не А1.
Чтобы включить или выключить стиль ссылок R1C1:
1. Выберите пункт Параметры в меню Сервис и перейдите на вкладку Общие.
2. В разделе Параметры установите или снимите флажок Стиль ссылок R1C1.
Электронные таблицы позволяют осуществить сортировку данных, они могут сортироваться по возрастанию или убыванию, выстраиваясь в определенном порядке. В электронных таблицах возможен поиск данных в соответствии с указанными условиями – фильтрами.
Ячейки рабочего листа электронной таблицы могут содержать:
– исходные, или первичные, данные – константы;
– производные данные, которые рассчитываются с помощью формул или функций.
Аргументами функции могут быть:
– числа;
– ссылки на ячейки и диапазоны ячеек;
– имена;
– текст;
– другие функции;
– логические значения.
Для ссылки на ячейки в столбцах и строках можно использовать заголовки этих столбцов и строк листа. Также для представления ячеек, диапазонов ячеек, формул или констант можно создавать имена. Заголовки можно использовать в формулах, содержащих ссылки на данные на том же листе; для представления группы ячеек, находящейся на другом листе, этой группе следует присвоить имя:
– определенное имя в формуле облегчает понимание назначения формулы. Например, формулу =СУММА(Продано_в_первом_квартале) легче опознать, чем =СУММ(С20:С30);
– имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек «А20:А30» первого листа рабочей книги, то это имя можно применить на любом другом листе той же рабочей книги для ссылки на эту группу;
– имя можно присвоить формуле или постоянному значению (константе). Например, имя «Процентная Ставка», которому присвоено значение 6,2 %, можно использовать в любом месте для вычисления процентов;
– также можно ссылаться на определенное имя в другой книге или определить имя, ссылающееся на ячейку в другой книге. Например, формула =СУММА(Продажи.х1з!Контракты) ссылается на диапазон «Контракты» в книге «Продажи»;
Примечание. По умолчанию имена являются абсолютными ссылками.
– допустимые знаки. Первый знак в имени должен быть буквой или знаком подчеркивания. Остальные знаки имени могут быть буквами, числами, точками и знаками подчеркивания;
– ссылки на ячейки как имена. Имена не могут иметь такой же вид, как и ссылки на ячейки, например Z$100 или R1C1;
– использование нескольких слов. В имени может быть больше одного слова, но пробелы недопустимы. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например «Налог_на_продажи» или «Первый.Квартал»;
– число знаков, которое можно использовать. Имя может содержать до 255 знаков;
Примечание. Если имя диапазона содержит больше 255 знаков, оно не может быть выбрано из поля Имя.
– учет регистра в именах. Имя может состоять из строчных и прописных букв, но Microsoft Excel их не различает. Например, если создано имя «Продажа», а затем в той же книге создано другое имя «ПРОДАЖА», то второе имя заменит первое;
– многоуровневые заголовки. Если на листе используются заголовки столбцов и строк, эти заголовки можно применять при создании формул, ссылающихся на данные листа. Если лист содержит многоуровневые заголовки столбцов – в которых за заголовком в одной ячейке ниже следуют один или более заголовков, – можно использовать эти заголовки в формулах, ссылающихся на данные листа. Например, если в ячейке Е5 находится заголовок «Запад», а в ячейке Е6 – заголовок «Проект», то формула = СУММ(Запад; Проект) возвращает общее значение для столбца «Запад Проект»;
– порядок заголовков. При использовании ссылок на данные с помощью многоуровневых заголовков вы ссылаетесь на данные в порядке, в котором отображаются заголовки сверху вниз. Если заголовок «Запад» находится в ячейке F5, а заголовок «Фактически» – в ячейке F6, то можно для обращения к фактическим объемам продаж для запада использовать в формуле ссылку «Запад; Фактически». Например, чтобы вычислить среднее значение фактических объемов продаж для запада, следует использовать формулу =СРЗНАЧ(Запад; Фактически);
– использование дат в качестве заголовков. Если с помощью диалогового окна Заголовки диапазонов создаются заголовки, содержащие год или дату, то при вводе формулы Microsoft Excel определяет дату как заголовок путем заключения ее в одинарные кавычки. Например, пусть лист содержит два заголовка – «2007» и «2008», – определенных в диалоговом окне Заголовки диапазонов. При вводе формулы =СУММ(2008) год автоматически заключается в кавычки: =СУММ('1997’).
Операторы сравнения. Операторы сравнения (табл. 15.1) используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
Таблица 15.1.
Операторы сравнения
| Оператор сравнения | Значение (пример) |
| = (знак равенства) | Равно (А1 = В1) |
| > (знак больше) | Больше (А1 > В1) |
| < (знак меньше) | Меньше (А1 < В1) |
| > (знак больше или равно) | Больше или равно (А1 ³ В1) |
| < (знак меньше или равно) | Меньше или равно (А1 £ В1) |
| <> (знак не равно) | Не равно (А1 <> В1) |
Операторы ссылки. Для описания ссылок на диапазоны ячеек используются следующие операторы (табл. 15.2).
Таблица 15.2.
Операторы ссылки
| Оператор ссылки | Значение (пример) |
| : (двоеточие) | Ставится между ссылками на первую и j последнюю ячейки диапазона. Такое сочетание является ссылкой на диапазон (В2:В15) |
| ;(точка с запятой) | Оператор объединения. Объединяет несколько ссылок в одну ссылку (СУММ(B5:B15;D5:D15)) |
| Пробел | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов (B7:D7 C6:C8) |
Формулы вычисляют значения в определенном порядке. Формула в Microsoft Excel всегда начинается со знака равенства =. Знак равенства свидетельствует о том, что последующие знаки составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Ввод данных в ячейки. Для выполнения этого задания необходимо знать следующее:
1. Данные вводятся в активную ячейку. Активная ячейка выделена черной рамкой.
2. Адрес активной ячейки отображается в строке формул.
3. Ячейка активизируется или щелчком мыши, или с помощью курсорных клавиш.
4. В активную ячейку можно ввести текст, число, дату, время, формулу.
5. Ввод данных заканчивается:
– нажатием клавиши Enter (при этом активизируется соседняя ячейка);
– щелчком мышью по кнопке с изображением галочки в строке формул (при этом активной остается та же ячейка);
– щелчком мышью в любой другой ячейке.
Excel содержит более 400 встроенных функций, условно разделенных на несколько категорий:
– математические и тригонометрические;
– статистические;
– финансовые;
– логические;
– инженерные;
– информационные;
– функции даты и времени.
С помощью статистических функций возможно проводить статистическое моделирование. Кроме того, возможно использовать элементы факторного и регрессионного анализа.
Режимы работы табличного процессора:
– режим готовности – в строке состояния появляется индикатор Готово;
– режим ввода данных – в строке состояния появляется индикатор Ввод;
– режим редактирования – отмечается появлением индикатора Правка;
– командный режим – в строке состояния появляются подсказки;
– запись макроса – производится запись макроса.
Формулой в Excel называется последовательность символов, начинающаяся со знака равенства =. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы. Результатом работы формулы является новое значение, которое выводится как результат вычисления формулы по уже имеющимся данным.
Ввод формул. Для выполнения этого задания необходимо знать следующее:
1. Ввод формулы начинается со знака равенства =.
2. В формулу могут входить числа, адреса ячеек и стандартные функции, соединенные знаками арифметических операций:
– % (процент);
– ^(операция возведения в степень (символ Л набирается в латинском регистре при нажатии сочетания клавиш Shift+6 на основной клавиатуре));
– * (умножение);
– / (деление);
– + (сложение);
– – (вычитание).
3. При записи выражения могут использоваться круглые скобки, например = 2 ^ 3 – (3 * 4 + 10).
4. Ссылка однозначно определяет ячейку или группу ячеек рабочего листа. Ссылки указывают, в каких ячейках находятся значения, которые нужно использовать в качестве аргументов формулы. С помощью ссылок можно использовать в формуле данные, находящиеся в различных местах рабочего листа, а также использовать значение одной и той же ячейки в нескольких формулах.
5. Имя – это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно в поле имени или с помощью команд меню Вставка Þ Имя ÞПрисвоить....
Ввод примечаний. Примечание – это поясняющий текст к ячейке.
В электронных таблицах предусмотрен также графический режим работы, который дает возможность графического представления (в виде графиков, диаграмм) числовой информации, содержащейся в таблице.
Электронные таблицы просты в обращении, быстро осваиваются непрофессиональными пользователями компьютера и во много раз упрощают и ускоряют работу бухгалтеров, экономистов, ученых.
Основные элементы электронных таблиц:
1. Столбец.
2. Заголовки столбцов.
3. Строка.
4. Заголовки строк.
5. Неактивная ячейка.
6. Активная ячейка.
Форматирование ячеек. Для выполнения этого задания необходимо знать следующее:
1. К форматированию ячеек относятся следующие операции:
– выбор формата представления данных (числовой, денежный, процентный, текстовый, дата и т.д.);
– выравнивание данных (горизонтальное, вертикальное);
– установка шрифтов;
– установка границ;
– выбор заливки.
2. Для любой ячейки или группы ячеек можно задать свой формат.
3. Перед форматированием ячейки необходимо выделить.
4. Существует три способа выполнения операций по форматированию ячеек:
– команды меню Формат Þ Ячейки;
– команда Формат ячеек... из контекстного меню выделенного блока, которое вызывается щелчком правой кнопкой мыши;
Диаграммы. Табличный процессор MS Excel также позволяет создавать диаграммы (рис. 15.1) и вставлять их в текст документа.
Элементы диаграммы:
1. Область диаграммы.
2. Область построения диаграммы.
3. Название диаграммы.
4. Название оси X.
5. Ось категорий (ось X).
6. Название оси Y.
7. Ось значений (ось У). Метки оси 7проставляются автоматически.
8. Основные линии сетки оси значений.
9. Ряды данных (столбцы).
10. Подписи данных.
11. Легенда (соответствие цветов и названий рядов данных). Мастер диаграмм – это подпрограмма Excel, помогающая построить диаграмму. Запуск: выделить диапазон ячеек с данными и подписями к данным и нажать кнопку Мастер диаграмм на панели инструментов или выбрать команды меню Вставка Диаграмма.
В открывшемся диалоговом окне Мастер диаграмм можно создать диаграмму, выполнив четыре шага.
Шаг 1. Тип диаграммы (гистограмма, круговая, график и др.).
Шаг 2. Источник данных диаграммы (вкладки: Диапазон данных, Ряд). На вкладке Ряд можно изменить диапазон ячеек, по которым строится диаграмма.
Шаг 3. Параметры диаграммы. Вкладки:
– Заголовки – название диаграммы, подписи осей;
– Оси – значения на осях;
– Линии сетки – вспомогательные линии, выходящие из осей;
– Легенда – добавление и размещение легенды в области диаграммы;
– Подписи данных – подписи и ключи к рядам данных;
– Таблица данных – таблица данных под осью X.
Шаг 4. Размещение диаграммы (на отдельном листе диаграмм или на любом рабочем листе).
Стандартные операции для редактирования диаграммы: изменить размер, переместить, скопировать, удалить. Если данные в таблице изменились, пересчет диаграммы произойдет автоматически. Изменить (добавить) параметры диаграммы можно с помощью команды контекстного меню диаграммы Параметры диаграммы.
Для форматирования диаграммы нужно дважды щелкнуть по элементу диаграммы (или на панели Диаграммы выбрать элемент диаграммы из раскрывающегося списка и нажать кнопку Формат) – открывается диалоговое окно форматирования выбранного элемента.
Собственное оформление диаграммы можно сохранить в списке Тип диаграммы (будет использоваться по умолчанию): выполнить команды меню Диаграмма Þ Тип диаграммы ÞСделать стандартной Þ Да, затем ввести название и описание нового типа диаграммы и нажать кнопку ОК. Цвета и шрифты этой диаграммы будут применяться ко всем видам диаграмм, которые находятся на вкладке Стандартные.
Добавление других видов оформления: выполнить команды меню Диаграмма => Тип диаграммы Þ вкладка Нестандартные Þ Дополнительные, ввести название и описание нового вида диаграммы и нажать кнопку ОК. В разделе Нестандартные дополнительные находятся все добавленные виды диаграмм.