Вычисление будущего значения с помощью функции БЗ

Функция БЗ определяет будущее значение капиталовложений.

К примеру, Вы можете подсчитать значение Вашего капитала через 10 лет, если Вы будете вносить на свой счет по 1000 рублей в год при 10% годовых (рис. 16).

Рис. 16. Пример применения функции БЗ

6.4. Ошибки в функциях

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

В табл. 7 показаны наиболее распространенные ошибочные значения Excel.

Таблица 7

Коды ошибочных значений Мастера функций.

6.5. Ссылки абсолютные и относительные

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

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

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

А1, $А$1, А$1 и $А1.

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

Рис. 17. Пример применения абсолютных, относительных и смешанных ссылок

Пример создания таблицы умножения (рис. 18):

- в ячейке В1 набираем = А1+1;

- протягиваем через первую строчку до столбца К;

- в ячейке А2 набираем = А1+1;

- протягиваем через первый столбец до строчки 11;

- в ячейке В2 набираем = $А2+В$1;

- протягиваем диапазон ячеек до ячейки К11.

Рис. 18. Пример создания таблицы умножения

6.6. Использование имен диапазонов в функциях

Диапазонам ячеек можно присваивать имена и использовать их вместо ссылок на ячейки.

Например, можно присвоить имя Ряд диапазону ячеек с В3 по В8 и тогда суммирование значений этих ячеек с помощью функции СУММ может быть оформлено двумя способами:

= СУММ(В3:В8) = СУММ(Ряд)

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

Для присвоения имени диапазону в Excel предусмотрены два способа: команда Создать из подменю Имя меню Вставка (автоматический способ) или щелчок на поле имен с последующим вводом названия (ручной способ).

Рис. 19. Пример автоматического создания имени диапазона ячеек

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

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

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

Имя диапазона можно выбрать из списка с помощью диалогового окна Вставка имени(команда Вставить подменю Имя меню Вставка) либо из ниспадающего списка в поле имени.

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

Рис. 20. Диалоговое окно «Присвоение имени»

6.7. Установка связей между листами

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

Пример ссылки:

= Фрукты!B1 + Овощи!C4 + Ягода!B33

7. Анализ деловых данных

Excel содержит несколько полезных средств планирования, на основе которых можно составлять прогнозы на будущее: команда Подбор параметра для нахождения значения, приводящего к требуемому результату, надстройкой Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям, а также Диспетчером сценариев для создания и оценки наборов сценариев «что-если» с несколькими вариантами исходных данных.

7.1. Оптимизация с помощью команды Подбор параметра

Основной командой для решения оптимизационных задач в Excel является команда Подбор параметра из меню Сервис. Эта команда определяет неизвестную величину, приводящую к требуемому результату, – например, количество проданных компакт-дисков по 73 рубля, необходимое для достижения объема продаж в 500 000 рублей.

Для завершения итерационного цикла меняется всего одна переменная. Задача не подразумевает изменение дополнительных переменных.

Для работы с командой Подбор параметра необходимо, чтобы в листе находились:

- формула для расчета;

- пустая ячейка для искомого значения;

- все прочие величины, встречающиеся в формуле.

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

Оптимизация с помощью команды Подбор параметра выполняется так:

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

4. Выделите ячейку, в которой содержится формула (в диалоговом окне Подбор параметра этой ячейке соответствует поле Установить в ячейке).

5. Выполните команду Подбор параметра из меню Сервис. Открывается диалоговое окно Подбор параметра.

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

7. Переместите курсор в текстовое поле Изменяя значение ячейки и выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выделенная ячейка выделяется на листе рамкой.

8. Щелкните на кнопке ОК, чтобы запустить поиск решения. После завершения итерационного цикла в диалоговом окне Результат подбора параметра появляется сообщение о результате поиска решения.

9. Закройте диалоговое окно Результат подбора параметра.

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

Примеры применения команды Подбор параметра приведены на рис. 21.

7.2. Использование команды Поиск решения

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

Если команда Поиск решения отсутствует в меню Сервис, выполните команду Надстройки из меню Сервис и установите флажок Пакет анализа в диалоговом окне Надстройки.

Рис. 21. Примеры применения команды Подбор параметра

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

Предположим, что в кафетерии продается кофе трех разновидностей:

- обычный кофе (9 руб. 99 коп.);

- особый кофе со сливками (15 руб.);

- особый кофе с шоколадом (24 руб.).

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

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

7.2.1. Постановка задачи при работе с командой Поиск решения

1. Создание специализированного листа.

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

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

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

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

2. Выяснение или задание ограничений.

- Ограничением называется граничное условие или руководящий принцип, которому должен подчиняться бизнес. Например, в нашей задаче: складские помещения и условия продажи позволяют производить за неделю не более 500 чашек кофе (как обычного, так и особого). Кроме того, существуют ограничения на поставку сливок и шоколада, которые не позволяют производить в неделю более 125 чашек кофе с шоколадом и 350 чашек особого кофе обоих видов (рис. 22).

-
На листе должны содержаться ячейки, в которых вычисляются ограничиваемые величины (в нашем примере – ячейки с F7 по F9). Численные значения самих ограничений приведены в ячейках с F12 по F14 для наглядности.

Рис. 22. Использование команды Поиск решения

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

7.2.2. Непосредственный поиск решения

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

1. Выделите на листе целевую ячейку (F4).

2. Выполните команду Поиск решения из меню Сервис. Открывается диалоговое окно Поиск решения (рис. 23).

3. в группе Равной установить переключатель Максимальному значению, поскольку нас интересует именно максимальное значение целевой ячейки.

4.

Рис. 23. Диалоговое окно Поиск решения

5.

Рис. 24. Диалоговое окно Добавление ограничений

6. Щелкая на кнопке Добавить, вводим все ограничения. Щелкните на кнопке 0К, чтобы занести все ограничения в диалоговое окно Поиск решения.

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

Оптимизационная задача готова к выполнению. Щелчок на кнопке Выполнить открывает диалоговое окно Результаты поиска решения (рис. 25) или выводит на экран сообщение об ошибке, если при поиске решения возникли проблемы.

Рис. 25. Диалоговое окно Результаты поиска решения

7.2.3. Изменение условий оптимизационных задач

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

8. Построение диаграмм и графиков

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

8.1. Планирование диаграммы

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

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

- внедренные диаграммы– помещается на том же рабочем листе, где и данные, по которым они построены;

- диаграммы в формате полного экранана новом рабочем листе.

Оба вида диаграмм связаны с данными рабочего листа и автоматически обновляются при изменении данных.

Необходимо также выбрать тип создаваемой диаграммы. Excel поддерживает 14 основных типов диаграмм, каждый из которых может иметь несколько разновидностей. Основные типы диаграмм приведены в табл. 8.

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

- Какие данные будут представлены диаграммой.

- В какой документ войдет диаграмма (отчет, презентация и т.д.)

- Как должна выглядеть диаграмма (тип).

8.2. Создание диаграммы

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

Таблица 8

Основные типы диаграмм, поддерживаемые Excel.

8.2.1. Выбор типа диаграммы

Рис. 29. Диалоговое окно Мастер диаграмм (Шаг 1 из 4): тип диаграммы

8.2.2. Выбор данных

Второй этап работы Мастера (Шаг 2 из 4) служит для выбора данных, по которым будет строиться диаграмма (рис. 30). Если диапазон данных был выбран заранее, то в области предварительного просмотра в верхней части окна Мастера появится приблизительное отображение будущей диаграммы. Если данные образуют единый прямоугольный диапазон, то их удобно выбирать при помощи вкладки Диапазон данных. Если данные не образуют единой группы, то информацию для отрисовки отдельных рядов данных задают на вкладке Ряд. Предварительное представление диаграммы автоматически обновляется при

Рис. 30. Диалоговое окно Мастер диаграмм (Шаг 2 из 4): исходные данные

8.2.3. Оформление диаграммы

Третий этап работы Мастера (Шаг 3 из 4) состоит в выборе оформления диаграммы (рис.31). На вкладках окна задаются:

- название диаграммы, подписи осей (вкладка Заголовки);

- отображение и маркировка осей координат (вкладка Оси);

- отображение сетки линий, параллельных осям координат (вкладка Линии сетки);

- описание построенных графиков (вкладка Легенда);

- отображение надписей, соответствующих отдельным элементам данных на графике (вкладка Подписи данных);

- представление данных, использованных при построении графика, в виде таблицы (вкладка Таблица данных).

Рис. 31. Диалоговое окно Мастер диаграмм (Шаг 3 из 4): параметры диаграммы

8.2.4. Размещение диаграммы

Рис. 32. Диалоговое окно Мастер диаграмм (Шаг 4 из 4): расположение диаграммы

После щелчка на кнопке Готово диаграмма строится автоматически и вставляется на указанный рабочий лист.

8.3. Выделение элементов диаграммы

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

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

8.4. Форматирование диаграммы

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

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

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

Форматирование любого элемента диаграммы может быть осуществлено различными способами:

- Двойной щелчок на любом элементе диаграммы вызывает соответствующее диалоговое окно форматирования.

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

- Щелкните на элементе диаграммы правой кнопкой мыши и выберите нужный пункт из контекстного меню.

9. Обработка информации в списках

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

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

9.1. Использование формы для ввода данных

Достаточно часто записи вводятся непосредственно на листе, но для облегчения обработки данных в списках в Excel имеется команда Форма в меню Данные, предназначенная для добавления, удаления и поиска записей. При ее выполнении появляется диалоговое окно с полями из вашего списка и несколькими управляющими кнопками (рис.34). Вертикальная полоса прокрутки позволяет перейти к любой записи. Новые записи добавляются в конец списка командой Добавить.

Рис. 34. Вид диалогового окна при использовании формы (работа со списками)

9.2. Проверка данных при вводе

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

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

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

2. Выполните команду Проверка из меню Данные – открывается диалоговое окно Проверка вводимых значений. Перейдите на вкладку Параметры (рис. 35).

3.
В области Условие проверки выберите формат значений из списка Тип данных (Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого вы можете ввести собственную формулу).

Рис. 35. Вкладки диалогового окна Проверка вводимых значений

4. При выборе значения из списка Тип данных внизу появляются дополнительные текстовые поля для ввода дополнительных условий или ограничений.

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

Рис. 36. Вид листа Excel при работе со списками

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

- Из раскрывающегося списка Вид выберите нужное значение: Останов - для блокировки ввода, Предупреждение – для выдачи предупреждения с возможностью продолжить ввод или Сообщение – для выдачи сообщения с продолжением ввода.

- В поле Сообщение введите текст, который должен выводиться при вводе пользователем неверных данных (рис. 36).

7. Закройте диалоговое окно Проверка вводимых значений, щелкнув на кнопке ОК.

9.3. Сортировка строк и столбцов

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

Сортировка списка по значениям из одного столбца выполняется так:

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

- Выполните команду Сортировка из меню Данные. Excel выделяет все записи списка и открывает диалоговое окно Сортировка диапазона.

- В списке Сортировать по выбрать заголовок того столбца, по которому будет произведена сортировка данных.

- Выберите тип сортировки, установив переключатель По возрастанию или По убыванию.

- Щелкните на кнопке ОК, чтобы запустить сортировку.

В случае сортировки по нескольким столбцам в списке Затем по выберите столбец для вторичной сортировки, а в списке В последнюю очередь, по выберите столбец для окончательной сортировки (рис. 37).

9.3.1. Мгновенная сортировка списка

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

9.4. Задание пользовательского порядка сортировки

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

Для этого необходимо:

- Выполнить команду Параметры из меню Сервис и перейти на вкладку Списки (рис. 38).

- Выбрать из перечня Списки значение НОВЫЙ СПИСОК – в поле
Элементы списка следует ввести значения, образующие пользовательский порядок сортировки. Значения можно разделять запятыми или вводить их с новой строки. Щелчок на кнопке Добавить

- Новый порядок отображается в перечне Списки.

-

Рис. 38. Диалоговое окно Параметры (вкладка Списки)

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

9.5. Использование автофильтра для поиска записей

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

Применение команды Автофильтр для поиска записей происходит так:

- Выделите любую ячейку в списке.

- Выполните команду Автофильтр подменю Фильтр меню Данные.

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

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

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

9.5.1. Создание пользовательского автофильтра

Когда возникает необходимость в отборе записей по диапазону числовых значений или иной настройке критерия, выберите значение Условие из раскрывающегося списка автофильтра – открывается диалоговое окно Пользовательский автофильтр, в котором осуществляется настройка критериев (рис. 39).

Рис. 39. Диалоговое окно Пользовательский автофильтр

10. Создание сводных таблиц.

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

10.1. Использование Мастера сводных таблиц и диаграмм

Сводная таблица создается следующим образом:

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

2. Выполните команду Сводная таблица из меню Данные.

3. Запускается Мастер сводных таблиц и диаграмм, который на первом шаге просит указать источник данных для таблицы. Установите переключатель на В списке или базе данных Microsoft Excel и щелкните на кнопке Далее
(рис. 42).

4. На втором шаге Excel предлагает задать диапазон данных. Выделите данные списка при помощи мыши (не забудьте включить в выделение заголовки столбцов) и щелкните на кнопке Далее.

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

6. Щелкните на кнопке Готово, чтобы продолжить построение таблицы. Мастер сводных таблиц открывает новый рабочий лист, создает пустую сводную таблицу и выводит на экран панель инструментов Сводные таблицы.

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

10.2. Работа со сводной таблицей

10.2.1. Перестановка полей в сводной таблице

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

Рис. 42. Диалоговые окна Мастера сводных таблиц

Рис. 43. Вид листа Excel при построении сводной таблицы

10.2.2. Изменение функции сводной таблицы

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

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

- Откройте лист, на котором находится сводная таблица.

- Дважды щелкните на имени поля данных в левом верхнем углу сводной таблицы. Откроется диалоговое окно Вычисление поля сводной таблицы
(рис. 44).

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

На рис. 45 приведен пример варианта сводной таблицы.

Рис. 45. Пример сводной таблицы

СОДЕРЖАНИЕ

1. Запуск Excel.. 1

2. Перемещение по листу.. 2

3. Ввод информации.. 3

3.1. Ввод числовых значений.. 4

3.2. Ввод текстовых значений.. 4

3.3. Ввод даты и времени.. 5

3.4. Ввод примечаний.. 5

3.5. Ввод формул. 7

3.6. Вставка специальных объектов. 7

3.7. Вставка фоновых изображений.. 7

3.8. Вставка гиперссылок. 8

3.9. Сохранение рабочей книги.. 8

3.10. Закрытие рабочей книги.. 8

3.11. Завершение работы с Microsoft Excel. 8

4. Редактирование листа Excel.. 9

4.1. Выделение ячеек и диапазонов. 9

4.2. Очистка и удаление ячеек. 10

4.3. Отмена, возврат и повторение команд. 10

4.4. Вырезание, копирование и вставка для перемещения данных. 10

4.5. Перемещение ячеек перетаскиванием.. 11

4.6. Добавление строк и столбцов к листу.. 12

4.7. Заполнение рядов текстовых величин, чисел и дат. 12

4.8. Использование автозаполнения для создания рядов. 13

4.9. Использование команд подменю Заполнить. 14

4.10. Диалоговое окно Прогрессия. 15

5. Форматирование листа.. 16

5.1. Форматирование ячеек. 16

5.1.1. Изменение числовых форматов. 16

5.1.2. Создание пользовательских числовых форматов. 19

5.1.3. Изменение шрифта и цвета текста. 20

5.1.4. Добавление границ к ячейкам.. 20

5.1.5. Добавление заливки к ячейкам.. 20

5.1.6. Копирование атрибутов формата кнопкой Формат по образцу. 20

5.2. Изменение ширины столбцов и высоты строк. 21

5.3. Выполнение комплексного форматирования командой Автоформат. 21

5.4. Условное форматирование. 22

6. Обработка чисел в формулах и функциях.. 24

6.1. Некоторые замечания по использованию и написанию формул в Excel. 24

6.2. Встроенные функции.. 24

6.3. Некоторые примеры финансовых функций: 27

6.4. Ошибки в функциях.. 29

6.5. Ссылки абсолютные и относительные. 29

6.6. Использование имен диапазонов в функциях.. 31

6.7. Установка связей между листами.. 34

7. Анализ деловых данных.. 34

7.1. Оптимизация с помощью команды Подбор параметра.. 34

7.2. Использование команды Поиск решения. 35

7.2.1. Постановка задачи при работе с командой Поиск решения. 37

7.2.2. Непосредственный поиск решения. 38

7.2.3. Изменение условий оптимизационных задач. 40

8. Построение диаграмм и графиков.. 41

8.1. Планирование диаграммы.. 41

8.2. Создание диаграммы.. 41

8.2.1. Выбор типа диаграммы.. 43

8.2.2. Выбор данных. 43

8.2.3. Оформление диаграммы.. 44

8.2.4. Размещение диаграммы.. 45

8.3. Выделение элементов диаграммы.. 46

8.4. Форматирование диаграммы.. 46

9. Обработка информации в списках.. 47

9.1. Использование формы для ввода данных.. 47

9.2. Проверка данных при вводе. 48

9.3. Сортировка строк и столбцов. 51

9.3.1. Мгновенная сортировка списка. 51

9.4. Задание пользовательского порядка сортировки.. 53

9.5. Использование автофильтра для поиска записей.. 54

9.5.1. Создание пользовательского автофильтра. 54

10. Создание сводных таблиц. 55

10.1. Использование Мастера сводных таблиц и диаграмм.. 55

10.2. Работа со сводной таблицей.. 56

10.2.1. Перестановка полей в сводной таблице. 56

10.2.2. Изменение функции сводной таблицы.. 58