Основы Microsoft Excel.

Лекция.

Работа с мастером функций

Мастер функций

Построение диаграммы на отдельном листе

Создание внедренных диаграмм

Чтобы создать диаграмму на рабочем листе, нужно:

1. Выделить данные, которые будут в ней использованы;

2. В ленте Вставка в группе Диаграммы щелкните на кнопке Создать диаграмму и выберите нужный вид диаграммы.

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

При вставке диаграммы в главном меню появились обозначения лент Конструктор, Макет, Формат с общим заголовком Работа с диаграммами.

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

В линейчатых диаграммах горизонтальная ось является осью Х, или осью категорий.

3. В ленте Макет в разделе Подписи щелкните на кнопке Название диаграммы. Выберите пункт Над диаграммой и наберите с клавиатуры название диаграммы. Нажмите клавишу Enter. Появится название диаграммы.

Часто требуется создать диаграмму на отдельном листе.

1. Если диаграмма не выделена, выделите её.

2. В ленте Конструктор нажмите пункт Переместить диаграмму. В появившемся окне Перемещение диаграммы на листе включите опцию на отдельном листе. В соответствующем поле будет указано название листа Диаграмма1. Нажмите кнопку ОК.

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

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

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

1. Запуск Excel

Excel запускается так же, как и большинство программ Office: клавиша Пуск на Панели задач Программы щелчок на значке Microsoft Excel.

Файлы с книгами Microsoft Excel имеют расширение xls и соответствующие значки.

Microsoft Excel позволяет работать с таблицами в двух режимах:

- Обычный – наиболее удобный для выполнения большинства операций;

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

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

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

Рабочая область Excel содержит набор листов – такой набор называется книгой. При первом запуске Excel на экране появляется принятая по умолчанию книга (Книга1), из которой отображается первый лист (Лист1). Лист делится на строки и столбцы (листы могут содержать до 65 536 строк и 256 столбцов). Каждому столбцу книги назначается своя буква латинского алфавита, а каждой строке – число. Пересечения строк со столбцами образуют ячейки листа, на которые можно ссылаться по их адресу. Например, ячейка на пересечении столбца А и строки 1 – это ячейка А1.

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

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

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

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

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

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

Для быстрого продвижения по документу служат определенные сочетания клавиш (табл. 1).

Таблица 1

«Горячие» клавиши для перемещения по листу

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

Excel разрешает вводить в ячейки следующие виды информации:

- числовые значения (например, числа 15,000, $29.95 и 33 %);

- текстовые значения (например, слова «Итого», «1-й квартал» и «Офис»);

- даты и время суток (например, Янв-96, 11/12/63 или 1:00 РМ);

- примечания, предназначенные для вас или других пользователей (например, «Этот регион лидирует по объемам продаж»);

- формулы (например, =В5*1.081 или =СУММ(ВЗ:В7));

- гиперссылки на адреса Интернета и другие документы;

- картинки, фотографии, карты и иллюстрации.

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

К основным приемам редактирования относятся:

- выделение ячеек и диапазонов;

- очистка и удаление ячеек;

- копирование данных из одной ячейки в другую;

- использование новой панели инструментов Буфер обмена;

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

- добавление к листу новых строк и столбцов;

- отмена и повторение команд.

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

Выделение ячейки означает, что она становится активной, а ее имя появляется в поле Имя слева от строки формул.

Для обозначения диапазонов ячеек в Excel используется особая форма записи. Например, обозначение А1:Е1 соответствует ряду из пяти ячеек, расположен­ных вдоль верхнего края листа, а Е5:Е8 – вертикальному столбцу из четырех ячеек.

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

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

2. Установите указатель над первой ячейкой, которую требуется выделить.

3. Удерживая нажатой кнопку мыши, перетащите указатель по остальным ячейкам диапазона. Отпустите кнопку мыши.

4. Если необходимо выделить дополнительные, несмежные(то есть несоприкасающиеся) диапазоны, нажмите клавишу Ctrl и, удерживая ее нажатой, повторяйте шаги 1 и 2 до тех пор, пока не будут выделены все ячейки. После завершения выделения отпустите клавишу Ctrl.

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

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

2. Удерживая нажатой клавишу Shift, нажимайте клавиши перемещения для выделения оставшихся ячеек диапазона. Отпустите клавишу Shift.

3. Если требуется выделить дополнительные, несмежные диапазоны ячеек, нажмите клавиши Shift+F8. В строке состояния появляется индикатор ДОБ, означающий, что вы можете добавить к выделенному диапазону новые ячейки. Для этого повторите шаги 1 и 2.

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

Для удаления содержимого группы ячеек просто выделите ее и нажмите клавишу Delete. Командой Очистить из меню Правка (рис. 3) Excel вызывает подменю с командами, позволяющими удалить форматирование, содержимое ячеек, примечания или все сразу.

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

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

- команда Отменить из меню Правка

- Кнопка Отменить панели инструментов Стандартная

- Комбинация клавиш Ctrl+Z

Команда Повторить позволяет повторить последнюю команду в другом месте листа (клавиша F4 или комбинация клавишCtrl+Y).

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

Для перемещения данных из одного места на листе в другое можно воспользоваться командами Вырезать и Вставитьв меню Правка. При вырезании диапазона ячеек командой Вырезать Excel окружает их пунктирной линией, после чего помещает их содержимое (включая примечания и форматирование) в буфер обмена Windows. Когда вы выбираете новое место для данных и выполняете команду Вставить, ячейки вставляются из буфера обмена Windows в новое место, заменяя исходные ячейки Чтобы отменить перемещение после появления пунктирной линии, нажмите клавишу Escape.

Рис. 3. Команды меню Правка

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

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

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

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

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

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

Добавление новых строк к листу происходит так:

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

2. Выполните команду Строки из меню Вставка.

Добавление новых столбцов к листу выполняется так:

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

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

Excel позволяет вставлять отдельные ячейки в строки или столбцы листа командой Ячейки из меню Вставка.

Укажите, куда следует сдвигать ячейки при вставке – вниз или вправо.

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

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

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

Для ввода повторяющихся или последовательно изменяющихся данных используется средство Excel, называющееся автозаполнением. Автозаполнение включается при перетаскивании по ячейкам маленького черного квадратика, называемого маркером заполнения. Он находится в правом нижнем углу активной ячейки или выделенного диапазона. При установке указателя ячейки над маркером заполнения вид указателя меняется на знак «плюс», означающий, что автозаполнение разрешено. Для создания последовательности текстовых значений, чисел или дат достаточно перетащить указатель по ячейкам, заполняемым информацией, и потом отпустить кнопку мыши (рис. 4).

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

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

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

Таблица 3

Правила заполнении ячеек данными автозаполнением

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

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

Заполнение диапазона командой Заполнить происходит так:

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

2. Выполните команду Заполнить из меню Правка, после чего выберите команду подменю, соответствующую направлению копирования (Вниз или Вправо) (рис. 5).

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

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

Рис. 7. Использование команд подменю Прогрессия

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

К основным приемам форматирования можно отнести:

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

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

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

- Использование стилей.

- Работа с шаблонами.

- Разрывы страниц.

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

Команда Ячейки из меню Формат открывает диалоговое окно Форматячеек (рис. 8). В нем имеется шесть вкладок с атрибутами формата, которыми можно пользоваться для изменения внешнего вида ячеек листа. Самые распространенные команды форматирования выполняются также кнопками панели инструментов Форматированиелибо через контекстное меню.

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

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

- По центру выделения _– выравнивает данные из первой ячейки по центру всего диапазона выделенных столбцов.

На вкладке Выравнивание можно также настроить вертикальное выравнивание и изменить ориентацию текста.

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

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

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

Рис. 8. Вкладки диалогового окна «Формат ячеек»

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

Денежный формат Процентный Уменьшить разрядность

Формат с разделителями Уменьшить разрядность

Рис. 9. Кнопки быстрого форматирования.

Таблица 4

Числовые форматы на вкладке Число диалогового окна Формат ячеек.

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

Если вам приходится часто вводить числовые значения в незнакомых для Excel форматах, можно создать пользовательский числовой формат, выполнив команду Ячейки из меню Формат, затем перейдите на вкладку Число и выберите вариант Все форматы из списка Числовые форматы, а затем:

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

- введите новый формат в поле Тип при помощи стандартных и специальных символов, принятых в Excel (табл. 5).

Таблица 5

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

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

Для выделения заголовков и различных видов информации на листе можно воспользоваться вкладкой Шрифт диалогового окна Формат ячеек. Вкладка Шрифт позволяет изменять шрифт, начертание, размер и цвет данных в выделенных ячейках. Также в ней можно задать подчеркивание и создать специальные эффекты форматирования –перечеркивание, верхние и нижние индексы.

Если необходимо вернуться к стандартным атрибутам шрифта, установите флажок Обычный на вкладке Шрифт.

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

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

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

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

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

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

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

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

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

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

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

В стандартной ячейке хватает места для 8 цифр при использовании шрифта Arial размером 10 пунктов. При вводе больших чисел Excel автоматически расширяет ширину ячейки. Чтобы указать на нехватку места для отображения числа при намеренном сокращении ширины ячейки Excel заполняет ячейку символами «решетки» (########).

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

Для изменения ширины ячейки следует перетащить мышью ее правый край (указатель мыши должен принять вид двусторонней стрелки) или выполнить команду Ширина из подменю Столбец меню Формат и указать требуемое значение. Чтобы изменить высоту ячейки, перетащите ее нижний край или выполните команду Высота из подменю Строка меню Формат и укажите требуемое значение.

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

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

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

При форматировании блока ячеек Excel позволяет задавать несколько параметров формата одной командой Автоформат из меню Формат.

Команда Автоформат открывает диалоговое окно, где в списке Список форматов приведены некоторые заранее определенные типы таблиц (рис.10).

По умолчанию команда Автоформатавтоматически устанавливает флажки Формат чисел,Рамки,Шрифт,Узоры,Выравнивание,Ширину и высоту.

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

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

Одной из новых возможностей Excel является условное форматирование листов, при котором оформление ячеек автоматически меняется в зависимости от их содержимого. Условия, при которых происходит изменение параметров формата, задаются в диалоговом окне Условное форматирование (рис. 11)
командой Условное форматирование меню Формат.

Рис. 12. Пример Условного форматирования

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

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

- Каждая из формул начинается со знака равенства.

- В каждой формуле присутствуют арифметические операторы для выполнения действий над числами или ссылками на ячейки.

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

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

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

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

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

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

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

Аргументами функций могут быть выражения, а также другие функции.

В состав Excel входит свыше 300 функций. Самый простой и удобный способ использования встроенных функций – использование Мастера функций,в котором все функции разделены на несколько категорий (рис. 13, табл. 6).

Рис. 13. Диалоговое окно «Мастер функций – шаг 1 из 2»

Таблица 6

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

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

Одна из самых часто используемых математических функций – функция СУММ, которая автоматически вычисляет сумму значений из выделен­ного диапазона. Эта функция может иметь до 30 аргументов, причем каждый аргумент может быть числом, адресом ячейки или блока ячеек, содержащих число или формулу. Например,

СУММ(А2; В2:К2; 500; КОРЕНЬ(9))

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

Хотелось бы обратить внимание на другие математические функции:

ОКРУГЛ– округляет число до указанного количества десятичных знаков;

ОТБР – отбрасывает дробную часть числа;

ЦЕЛОЕ – округляет число до ближайшего меньшего целого.

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

ЕСЛИ (<условие>; <результат 1>; <результат 2>)

Результат 1 – результат, который должен возвращаться функцией, если условие выполняется.

Результат 2 – результат, который должен возвращаться функцией, если условие не выполняется.

Например:

= ЕСЛИ (СУММ(В1:В6)>0; СУММ(В1:В6); 0)

Иногда после проверки одного какого-то условия требуется проверка дополнительных условий. В этом случае используют вложенные функции ЕСЛИ (можно использовать до 7 уровней вложения функции ЕСЛИ).

Рис. 14. Диалоговое окно «Мастер функций – шаг 2 из 2»

Например, вычислить значение функции Y (в ячейке В2) в зависимости от значения аргумента X(ячейка (А2):

5 + X при X < 0

Y = Х- 10 при Х > 10

5 при остальных X

В этом примере в ячейку В2 запишем формулу:

= ЕСЛИ (А2<0; 5+А2; ЕСЛИ (А2>10; А2-10; 5))

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

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

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

Таблица 7

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

При работе с книгой из нескольких листов довольно часто возникает необходимость использовать данные одного листа при создании формулы на другом. Установка таких связей между листами в 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. Пример сводной таблицы