русс | укр

Языки программирования

ПаскальСиАссемблерJavaMatlabPhpHtmlJavaScriptCSSC#DelphiТурбо Пролог

Компьютерные сетиСистемное программное обеспечениеИнформационные технологииПрограммирование

Все о программировании


Linux Unix Алгоритмические языки Аналоговые и гибридные вычислительные устройства Архитектура микроконтроллеров Введение в разработку распределенных информационных систем Введение в численные методы Дискретная математика Информационное обслуживание пользователей Информация и моделирование в управлении производством Компьютерная графика Математическое и компьютерное моделирование Моделирование Нейрокомпьютеры Проектирование программ диагностики компьютерных систем и сетей Проектирование системных программ Системы счисления Теория статистики Теория оптимизации Уроки AutoCAD 3D Уроки базы данных Access Уроки Orcad Цифровые автоматы Шпаргалки по компьютеру Шпаргалки по программированию Экспертные системы Элементы теории информации

ОМСК 2008


Дата добавления: 2013-12-23; просмотров: 1192; Нарушение авторских прав


MICROSOFT EXCEL

ОБРАБОТКА ДАННЫХ С ПОМОЩЬЮ ПРОГРАММЫ

А. Г. ПАТЕЮК

 

 

 


Министерство транспорта Российской Федерации

Федеральное агентство железнодорожного транспорта

Омский государственный университет путей сообщения

––––––––––––––––––––––––––––––––

 

 

А. Г. Патеюк

 

 

ОБРАБОТКА ДАННЫХ С ПОМОЩЬЮ ПРОГРАММЫ

MICROSOFT EXCEL

 

 

Конспект лекций

 

Омск 2008


УДК 004.4(075.8)

ББК 32.988-5я73

П20

 

Обработка данных с помощью программы Microsoft Excel: Конспект лекций / А. Г. Патеюк; Омский гос. ун-т путей сообщения. Омск, 2008. 64 с.

 

 

Рассмотрены основные понятия, методы и приемы, относящиеся к
обработке данных средствами электронных таблиц с помощью программы Microsoft Excel.

Конспект лекций предназначен для студентов 1-го курса всех специальностей очного и заочного обучения, изучающих дисциплину «Информатика», а также может быть использован как самоучитель начинающими пользователями персональных компьютеров при самостоятельной подготовке.

 

 

Библиогр.: 6 назв. Табл. 8.Рис. 40.

Рецензенты: доктор техн. наук, профессор А. В. Бубнов;

доктор техн. наук, профессор В. А. Нехаев.

 

________________________

© Омский гос. университет

путей сообщения, 2008

 

 


ОГЛАВЛЕНИЕ

Введение........................................................................................................... 5

1. Запуск Excel 6

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

6.1. Основные правила использования и написания формул в Excel 28

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

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

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

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

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

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

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

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

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

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

8.1. Основные сведения о диаграммах. 44

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

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

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

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

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

9.2. Проверка данных при вводе информации. 51

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

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

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

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

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

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

Библиографический список. 63


ВВЕДЕНИЕ

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

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

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

Конспект лекций представлен в виде блоков теоретического материала, в которых излагается общая концепция работы с электронными таблицами, рассмотрены основные особенности работы с программой Microsoft Excel.

Материал данного учебного издания предназначен для более глубокого освоения курса «Информатика», способствует выработке навыков самостоятельной работы с новым материалом.

 


1. Запуск Excel

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

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

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

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

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

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

 
 

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

Рис. 1. Вид экрана Excel

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

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

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

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

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

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

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

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


Таблица 1

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

Клавиша или сочетание клавиш Перемещение
→, ↓, ←, ↑ К следующей ячейке в выб-ранном направлении
Ctrl + →, Ctrl + ↓ К следующей ближайшей в выбранном направлении ячейке, содержащей данные (непустой)
Enter На одну ячейку вниз
Shift+Enter На одну ячейку вверх
Tab На одну ячейку вправо
Shift+Tab На одну ячейку влево
Ноmе К столбцу А текущей строки
Page Up На один экран вверх
Page Down На один экран вниз
Alt+Page Up На один экран влево
Alt+Page Down На один экран вправо
Ctrl+Home К ячейке А1
Ctrl+End К ячейке последней строки и последнего столбца, в которых содержатся данные
Ctrl+Backspace Возврат к активной ячейке (или выделенному диапазону), скрытой при прокрутке листа

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

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

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

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

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

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

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

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

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

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

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

Числовое значение может быть целым (32), десятичной дробью (499,95), обыкновенной дробью (3/4) или экспоненциальной научной нотацией (4.09Е+13). По умолчанию числовые значения выравниваются по правому краю ячейки.

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

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

 
 

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

Рис. 2. Ввод числовых значений и текста

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

Если необходимо, чтобы в Excel сохранилось определенное значение (число, дата или номер) в виде текста, достаточно начать ввод значения с апострофа. Например, если ввести в ячейку '55, там появится число 55, выровненное по левому краю без апострофа.

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

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

Excel хранит дату и время в виде серийных чисел (значение серийного числа определяется на основе подсчета количества прошедших дней, начиная с величины 1, соответствующей полуночи 1 января 1900 г.).

Для изменения формата даты или времени следует выбрать команду Ячейки из меню Формат, щелкнуть на вкладке Число и выбрать образец в категории Дата и время (табл. 2).

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

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

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

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

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

Таблица 2

Форматы даты и времени, поддерживаемые Excel

 

Формат Шаблон Пример
Дата m/d/yy 10/1/99
Дата d-mmm-yy 1- 0kt-99
Дата d-mmm 1- 0kt
Дата mmm-yy Okt - 99
Дата m/d/yyyy 10/1/1999
Дата d-mmm-yyyy I - Okt - 1999
Время h:mm AM/PM 10:15 PM
Время h:mm:ssAM/PM 10:15:30 PM
Время h:mm 22:15
Время h:mm:ss 22:15:30
Время mm:ss.O 15:30.3
Комбини-рованный m/d/yy h:mm 10/1/99 22:15

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

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

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

Примеры формул: =(А4+В8)*С6, =F7*34+B12, =СУММ(А1:А5)

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

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

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

Работая с Excel, можно вставлять в таблицу картинки, фотографии, фоновые изображения, организационные диаграммы или иллюстрации, выполненные от руки. Основной принцип ввода подобных объектов одинаков для всех приложений Office: выбор команды из подменю Рисунок меню Вставка.

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

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

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

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

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

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

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

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

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

Для закрытия рабочей книги необходимо выбрать в меню Файлкоманду Закрытьили выполнить щелчок по кнопке r окна книги.

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

Для завершения работы с Microsoft Excel необходимо закрыть окно программы (щелкнуть по кнопке r окна программы или нажать комбинацию клавиш Alt + F4).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

 
 

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

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

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

У пользователя всегда есть возможность отменить последние сделанные изменения, для этого служат команда Отменить из меню Правка, кнопка Отменить панели инструментов Стандартная и комбинация клавиш Ctrl+Z.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 
 

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

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

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

Таблица 3

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

Тип последовательности ячеек Алгоритм последовательности Пример
Текст Изменения отсутствуют, текст дублируется Урок, Урок, Урок
Числа Возрастание в зависи-мости от правила изменения чисел 15, 30, 45
Текст с числами Ряд создается изменением чисел в зависимости от правила Урок 1, Урок 2, Урок 3
Дни недели Ряд создается в соответствии с форматом дней недели Вторник, Среда, Четверг
Месяцы Ряд создается в соответ-ствии с форматом месяцев Янв, Фев, Мар

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

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

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

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

 
 

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

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

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

 
 

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

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

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

 
 

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

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

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

- форматирование ячеек;

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

- условное форматирование;

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

- работу с шаблонами;

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

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

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

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

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

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

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

 
 

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

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

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

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

 

Таблица 4

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

Формат Комментарии Примеры
Общий Стандартный числовой формат, выравнивание по правому краю, без спец. кодов форматирования 15.75
Числовой Гибкий числовой формат, в котором числа оформляются запятыми, переменным количеством разрядов после десятичной точки 3.14159 (1,575.32)
Денежный Общий денежный формат, в котором числа оформляются знаками доллара, переменным количеством разрядов после десятичной точки $75.35 ($1,234.10)
Финансовый Специальный денежный формат, предназначенный для выравнивания столбцов денежных сумм (у левого края ячеек выводится знак доллара) $75.00 $500.75
Дата Общий формат даты, отображающий календар-ные даты в нескольких стандартных вариантах 1/15/97 Янв-15-97
Время Общий формат времени, отображающий значения времени в нескольких стандартных вариантах 3:30 РМ 15:30:58
Процентный Формат, умножающий значение в ячейке на 100 и отображающий результат со знаком процента (%) 175 % 15.125 %
Дробный Формат, представляющий числа в виде дробей (задаются количество цифр и знаменатель) 1/8 2/16
Экспоненци-альный Экспоненциальная запись чисел с большим количеством цифр 1.25Е-08 4.58Е+12
Текстовый Формат, в котором числа интерпретируются как текст (выравниваются по левому краю ячейки и отображаются точно в том виде, в котором они были введены) 500.35 12345.0
Дополнитель-ный Набор полезных форматов, основанных на алфавитно-цифровых шаблонах, среди которых – почтовый индекс, номер телефона и табельный номер 98109-1234 535-65-2342
Все форматы Перечень всех стандартных и пользовательских форматов ИНВ-0075 150.50 р.

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

 
 

 

 


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

 

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

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

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

Если необходимо часто вводить числовые значения в незнакомых для Excel форматах, можно создать пользовательский числовой формат, выполнив команду Ячейки из меню Формат, затем перейти на вкладку Число и выбрать
вариант Все форматы из списка Числовые форматы. После этого нужно изменить существующий формат, выделив его и отредактировав в поле Тип; ввести новый формат в поле Тип при помощи стандартных и специальных символов, принятых в Excel (табл. 5).

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

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

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

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

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

Таблица 5

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

Символ Назначение символа Пример формата Введенное число Результат применения формата
# Позиция для цифры, которая при необходимости округляется ##.### 50.0048 2.30 50.005 2.3
Позиция для цифры, которая при необходимости округляется и в которой отображаются нули при нехватке значащих цифр 00.00 50.1 50.10 05.00
? Позиция для цифры. Действие этого символа аналогично действию предыдущего символа (0), но при нехватке значащих цифр происходит заполнение пробелами вместо нулей ??.?? 5.6 .456 5.6 .46
«текст» Включение символов в формат «№» ## № 75
Запятая Отделение разрядов тысяч в числах #,## 5,600
$, –, + , : , /, (,), пробел Стандартные символы форматирования, которые появляются в указанном месте $#,000 500,5 $500,500
% Умножение значения на 100 и добавление знака процента ##% .25 25%

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 
 

Рис. 10. Диалоговое окно «Автоформат»

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

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

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

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

 
 

Пример применения условного форматирования приведен на рис. 12.

 
 

Рис. 11. Диалоговое окно «Условное форматирование»

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

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

6.1. Основные правила использования и написания формул в Excel

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

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

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

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

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

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

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

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

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

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

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

 

Таблица 6

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

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

 

 
 

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

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

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

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

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

Существуют еще такие математические функции:

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

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

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

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

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

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

Например:

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

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

 

5 + X при X < 0

Y = Х – 10 при Х > 10

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

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

 
 

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

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

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

Определение размера ссудных платежей с помощью функции ППЛАТ. Функция ППЛАТ возвращает размер периодических платежей, необходимых для погашения ссуды за определенный период времени, т. е., к примеру, можно определить размер ежемесячных платежей при ссуде в 1 000 000 р. на срок в
10 лет под 10 % годовых (рис. 15).

Вычисление будущего значения капитала с помощью функции БЗ. Функ-ция БЗ определяет будущее значение капиталовложений, например, можно подсчитать значение чьего-то капитала через 20 лет, если на счет будет вноситься по 10000 р. в год при 10 % годовых (рис. 16).

 

 
 

Рис. 15. Пример применения функции ППЛАТ

 
 

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

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

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

Наиболее распространенные коды ошибочных значений приведены в табл. 7.

Таблица 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, выполняется так:

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

 
 

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

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

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

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

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

 
 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. Курсор перемещается в текстовое поле Изменяя значение ячейки и выделяет ту ячейку, в которой должен содержаться ответ (переменная ячейка). Содержимое этой ячейки будет подобрано и подставлено в формулу командой Подбор параметра. На листе ячейка выделяется рамкой.

6. Для запуска поиска решения нужно щелкнуть на кнопке ОК. После завершения итерационного цикла в диалоговом окне Результат подбора параметра появляется сообщение о результате поиска решения.

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

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

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

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

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

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

Предположим, что в кафетерии продается кофе трех разновидностей: обычный (9 р. 99 коп.), особый со сливками (15 р.) и особый с шоколадом (24 р.).

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

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

 


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

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

Создание специализированного листа производится в следующей последовательности.

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

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

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

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

 
 

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

 

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

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

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

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

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

1. На специализированном листе выделяется целевая ячейка (F4).

2.

 
 

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

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

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

 
 

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

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

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

 
 

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

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

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

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

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

8.1. Основные сведения о диаграммах

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

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

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

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

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

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

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

Таблица 8

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

Тип диаграммы Назначение диаграммы
Гистограмма Вертикальное сравнение различных категорий данных
Линейчатая Горизонтальное сравнение различных категорий данных
График Показ тенденций по категории за период времени
Круговая Описание отношений между составными частями одного целого
Точечная Изображение связи между двумя видами взаимосвязанных данных
С областями Выделение относительной важности значений за период времени
Кольцевая Более гибкий вариант круговой диаграммы с отверстием в центре; используется для сравнения составных частей одного целого по одной или нескольким категориям данных
Лепестковая Показ изменений данных или их частоты относительно центральной точки
Поверхность Трехмерная диаграмма, удобная для отслеживания значений двух переменных с изменением третьей (обычно времени)
Пузырьковая Разновидность точечной диаграммы, позволяющая отображать значения трех переменных
Биржевая Комбинация гистограммы с графиком, разработанная специально для отображения биржевых цен
Цилиндрическая Гистограмма или линейчатая диаграмма со столбцами в виде цилиндров
Коническая Гистограмма или линейчатая диаграмма, позволяющая выделить пиковые значения данных
Пирамидальная Разновидность конической диаграммы, предназначенная для выделения пиковых значений

 

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

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

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

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

 
 

На первом этапе работы (шаг 1 из 4) выбирают тип диаграммы (рис. 26). Доступные типы диаграмм перечислены в списке Тип на вкладке Стандартные. Для выбранного типа диаграммы справа имеется несколько вариантов представления данных (палитра Вид), из которых следует выбрать наиболее подходящий. На вкладке Нестандартные отображается набор полностью сформированных типов диаграмм с готовым форматированием. После задания формы диаграммы следует щелкнуть на кнопке Далее.

Рис. 26. Диалоговое окно «Мастер диаграмм (шаг 1 из 4)»: выбор типа диаграммы

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

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

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

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

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

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

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

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

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

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

 
 

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

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

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

 
 

На последнем этапе работы (шаг 4 из 4) указывается, что следует использовать для размещения диаграммы – новый рабочий лист или один из имеющихся (рис. 29).

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

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

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

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

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

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

 
 

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

Рис. 30. Команды меню «Диаграмма» и кнопки панели инструментов «Диаграммы»

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

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

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

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

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

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

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

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

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

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

 
 

Рис. 31. Вид диалогового окна при использовании команды «Форма»
(работа со списками)

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

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

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

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

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

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

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

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

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

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

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

7. Щелчком на кнопке ОК

 
 

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

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

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

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

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

выполняется команда Сортировка из меню Данные, по которой Excel
выделяет все записи списка и открывает диалоговое окно Сортировка диапазона;

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

установкой переключателя По возрастанию или По убыванию выбира-ется тип сортировки;

для запуска сортировки необходимо щелкнуть на кнопке ОК.

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

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



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

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

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

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

 
 

закрыть диалоговое окно Параметры щелчком на кнопке ОК.

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

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

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

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

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

выделяется любая ячейка в списке;

выполняется команда Автофильтр подменю Фильтр меню Данные;

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

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

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

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

 
 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 
 

поля следует перетащить его с панели инструментов в сводную таблицу.

Рис. 37. Диалоговые окна надстройки «Мастер сводных таблиц»:
а – шаг 1 из 3; б – шаг 2 из 3; в – шаг 3 из 3

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

 
 

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

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

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

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

открывается лист, на котором находится сводная таблица;

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

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

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

 
 

Рис. 39. Диалоговое окно «Вычисление поля сводной таблицы»

Рис. 40. Вид листа Excel с вариантом сводной таблицы


Библиографический список

1. Microsoft Office 2000: Справочник / Под ред. Ю. Колесникова.
СПб: Питер, 1999. 480 с.

2. Информатика. Базовый курс. 2-е изд. / Под ред. С. В. Симоновича. СПб: Питер, 2005. 640 с.

3. Информатика для юристов и экономистов / С. В. Симонович,
Г. А. Юксеев и др. СПб: Питер, 2001. 688 с.

4. Экономическая информатика / Под ред. П. В. Конюховского и Д.Т. Колесова. СПб: Питер, 2001. 560 с.

5. Додж М. Эффективная работа с Microsoft Excel 2000 / М. Додж,
К. Стинсон. СПб: Питер, 2002. 1056 с.

6. Хэлворсон М. Эффективная работа с Microsoft Office / М. Хэлворсон, М. Янг; Пер. с англ. Т. Федоровой. СПб: Питер, 2003. 1232 с.



<== предыдущая лекция | следующая лекция ==>
Материнская плата | 


Карта сайта Карта сайта укр


Уроки php mysql Программирование

Онлайн система счисления Калькулятор онлайн обычный Инженерный калькулятор онлайн Замена русских букв на английские для вебмастеров Замена русских букв на английские

Аппаратное и программное обеспечение Графика и компьютерная сфера Интегрированная геоинформационная система Интернет Компьютер Комплектующие компьютера Лекции Методы и средства измерений неэлектрических величин Обслуживание компьютерных и периферийных устройств Операционные системы Параллельное программирование Проектирование электронных средств Периферийные устройства Полезные ресурсы для программистов Программы для программистов Статьи для программистов Cтруктура и организация данных


 


Не нашли то, что искали? Google вам в помощь!

 
 

© life-prog.ru При использовании материалов прямая ссылка на сайт обязательна.

Генерация страницы за: 0.077 сек.