Сортировка данных

Оглавление

1. Электронные таблицы.. 2

2. Выбор приложения из пакета Microsoft Office 95 для создания таблицы.. 2

3. Редактор электронных таблиц MS Excel

Основные приёмы работы.. 4

3.1. Общие сведения о табличном процессоре Excel 4

3.2. Базовые термины и понятия Excel 4

3.3. Типы данных, используемые в MS Excel 6

3.4. Создание и обработка электронных таблиц. 7

3.4.1. Ввод информации в ячейки. 8

3.4.2. Редактирование содержимого ячейки. 8

3.4.3. Сохранение таблиц на диске. 9

3.4.4. Построение формул и использование функций Excel 9

3.4.5. Выделение блоков ячеек. 11

3.4.6. Копирование формул. 11

3.4.7. Расчет итоговых сумм с помощью функций. 12

3.4.8. Форматирование данных. 12

3.4.9. Выравнивание данных. 13

3.4.10. Форматирование с помощью рамок и цветов. 14

3.4.11. Вставка строк и столбцов электронной таблицы Excel 15

3.4.12. Удаление ячеек, строк и столбцов. 16

3.4.13. Основные приемы обработки рабочей книги. 16

3.5. Объединение и связывание электронных таблиц в Excel 16

3.6. Консолидация данных в электронных таблицах. 17

3.7. Работа со структурированной таблицей. 19

3.8. Графическое представление данных в виде диаграмм.. 19

3.9. Создание диаграмм.. 22

3.10. Редактирование вида диаграммы.. 26

3.11. Трендовый анализ и анализ погрешностей. 31

1. Электронные таблицы

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

Электронные таблицы - это двумерные массивы (которые обычно называют рабочими листами), состоящие из столбцов и строк.

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

Функции табличных процессоров весьма разнообразны:

· создание и редактирование электронных таблиц;

· оформление и печать электронных таблиц;

· создание многотабличных документов, объединенных формулами;

· построение диаграмм, их модификация и решение экономических задач графическими методами;

· работа с электронными таблицами как с базами данных: сортировка таблиц, выборка данных по запросам;

· создание итоговых и сводных таблиц;

· использование при построении таблиц информации из внешних баз данных;

· решение экономических задач типа «что - если» путем подбора параметров;

· решение оптимизационных задач;

· статистическая обработка данных;

· создание слайд-шоу;

· разработка макрокоманд, настройка среды под потребности пользователя и т.д.

2. Выбор приложения из пакета Microsoft Office 95 для создания таблицы

Прежде чем создать таблицу, необходимо определить, какое из приложений Microsoft Office 95 наиболее подходит для этой работы. Чтобы создать таблицу, используйте Word 7.0, Excel 7.0 или Access 7.0. Все три приложения автоматически форматируют таблицу.

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

· Для создания таблицы, включающей сложные расчеты, статистический анализ, запросы или диаграммы необходимо использовать табличный процессор Excel. Однако если длина значения будет превышать 255 символов, следует использовать Word или применять систему управления базами данных (СУБД) Access.

· Для таблиц со сложными сортировками и поиском целесообразно СУБД Access или Excel.

· Если требуется использовать все возможности реляционной базы данных, таблицу создается в среде СУБД Access.

· Для создания таблицы, которую необходимо включить в презентацию, создаваемую в среде редактора PowerPoint, следует использовать текстовый процессор Word.

3. Редактор электронных таблиц MS Excel.

Основные приёмы работы

3.1. Общие сведения о табличном процессоре Excel

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

По оценкам фирмы PC Data of Reston, наиболее популярными электронными таблицами для персональных компьютеров являются табличные процессоры Excel (фирма Microsoft), Lotus 1-2-3 (фирма Lotus Development) и Quattro Pro (фирма WordPerfect - Novell Applications Group). Если после своего появления в 1982 году Lotus 1-2-3 был фактически эталоном для разработчиков электронных таблиц, то в настоящее время он утратил свои лидирующие позиции. Результаты тестирования, проведенные лабораторией журнала «Home PC», продемонстрировали явное преимущество Excel по многим параметрам. Единственное превосходство Lotus 1-2-3 - это скорость работы, но, опять же, превышение небольшое.

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

Фирма Microsoft уделяет первостепенное внимание совершенствованию набора функциональных средств Excel, и в этом, безусловно, ее пакет явно лидирует среди всех электронных таблиц. В Excel многие функции разработаны более тщательно, чем в других электронных таблицах. Например, средства создания перекрестных таблиц в Lotus 1-2-3 не настолько богаты, как Мастер сводных таблиц в Excel. Кроме того, возможность использования массивов в Excel обеспечивает эффективные, формульные решения многих задач, а применение имен диапазонов в формулах и функциях обеспечивает большую гибкость при работе с таблицами.

3.2. Базовые термины и понятия Excel

При запуске MS Excel на экране мониторав рабочем окне документа появляется пустая рабочая книга.

Рабочая книга представляет собой электронный эквивалент папки-скоросшивателя. Книга состоит из листов, имена которых выводятся на ярлычках в нижней части экрана. По умолчанию книга открывается с 16 рабочими листами Лист1, Лист2,..., Лист16, однако их число можно увеличить или уменьшить. В книгу можно поместить несколько различных типов документов, например рабочий лист с электронной таблицей, лист диаграмм, лист макросов и т. п.

Рабочий лист служит для организации и анализа данных. Одновременно на нескольких листах данные можно вводить, править, производить с ними вычисления. В книгу можно вставить листы диаграмм для графического представления данных и модули для создания и хранения макросов, используемых при выполнении специальных задач. Каждый рабочий лист состоит из 256 столбцов и 16 384 строк. Столбцы обозначены буквами латинского алфавита (А, В, С,...,Z, AA, AB,...,AZ, BA, BB,...,BZ и т.д. до IV), расположенными в заголовочной части таблицы. Строки пронумерованы цифрами (1, 2, 3,...,16 384), расположенными в первой колонке.

Место пересечения столбца и строки называется ячейкой. Каждая ячейка имеет свои уникальный адрес, состоящий из имени столбца и номера строки, например А28, Р1556 и т. п. В электронных таблицах можно работать как с отдельными ячейками, так и с группами ячеек, которые образуют блок. Имена ячеек в блоках разделяются двоеточием (:), например блок А1:В4 включает в себя ячейки А1, А2, A3, А4, В1, В2, ВЗ и В4. С блоками ячеек в основном выполняются операции копирования, удаления, перемещения, вставки и т. п.

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

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

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

· строк заголовков (программы и открытого документа),

· строки меню,

· панелей инструментов,

· строки ввода,

· рабочего листа (окна документа),

· полос прокрутки (вертикальной и горизонтальной),

· строки состояния.

Рис.1. Рабочее окно редактора электронных таблиц Excel.

3.3. Типы данных, используемые в MS Excel

В электронных таблицах используются следующие данные:

· Text - Текст и числа, не требующие проведения расчетов, например, имена и адреса, номера телефонов и почтовые индексы. Текстовое поле может содержать от 0 до 255 символов. При описании поля с типом Text необходимо задать для свойства Size значение, равное или превосходящее максимальную длину помещаемых в поле значений.

· Memo - Длинный текст, например комментарии и пояснения. Размер поля Memo ограничивается общим размером базы данных.

· Числа (разные) -Числовые значения, для которых предполагается проведение математических расчетов, за исключением расчетов с участием денежных значений.

· Date/Time - Значения даты и времени. Такие значения сохраняются как комбинация значения даты и значения времени.

· Currency - константа, используемая для полей, содержащих денежные значения. Не следует использовать для денежных значений числовой тип данных, поскольку для числовых типов в расчетах выполняется округление дробной части. В расчетах для денежного типа данных поддерживается фиксированное число разрядов справа от десятичной точки. Этот тип данных используется в вычислениях, включающих денежные величины или величины с фиксированной точкой, для которых особенно важна точность вычислений. Этот тип данных позволяет хранить числа, имеющие до 11 цифр в целой части и до 4 цифр в дробной.

· Counter - поле счетчика, т.е. увеличивающиеся на 1 последовательные номера, автоматически вставляемые в поле Нумерация начинается с 1.

· Yes/No - логические данные, в которых выводится одно значение из пары Yes/No, True/False, On/Off.

· OLE Object - Объекты, создаваемые в других программах, поддерживающих протокол OLE.

· Binary - Любое значение, которое может быть выражено в двоичном представлении с размером до 1,2 Гбайт. Этот тип используется для сохранения рисунков, файлов или других данных, требующих специальной обработки.

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

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

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

3.4. Создание и обработка электронных таблиц

Любая электронная таблица (ЭТ) состоит из следующих элементов:

· заголовка таблицы;

· заголовков столбцов («шапки» таблицы);

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

В общем виде процесс создания ЭТ включает следующие шаги:

· ввод заголовка ЭТ;

· ввод названии графического документа;

· ввод исходных данных;

· ввод расчетных формул;

· форматирование ЭТ с целью придания ей профессионального вида;

· подготовку ЭТ к печати и ее печать.

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

Рассмотрим основные приемы работы в среде Excel.

3.4.1. Ввод информации в ячейки

Перед вводом информации в ячейку ее надо активизировать, для чего следует подвести указатель мыши к данной ячейке и дважды щелкнуть левой кнопкой мыши. Активная ячейка выделяется черной рамкой, в левой части строки состояния появится сообщение “Ввод”, а в левой части строки ввода появятся три кнопки: “Отмена ” (X), “Ввод” (Ú) и “Мастер функций” (f). Далее вводятся с клавиатуры данные в активную ячейку. При этом следует заметить, что набранные данные сначала отображаются в строке ввода. Передать текст из строки ввода в активную ячейку можно одним из трех способов: нажать клавишу < Enter >, выполнить щелчок по кнопке с изображением «галочки» (Ú) в строке ввода или щелкнуть мышью вне строки ввода.

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

Следует учитывать, что обычно по умолчанию при вводе информации в ячейки устанавливается формат данных “Общий”, однако, если установлен другой формат, то пользователь может восстановить требуемый ему формат данных. Для этого следует выполнить команду “Формат/Ячейки”, открыть закладку “Формат ячеек”, (рис.2), выбрать нужную строку в окне “Числовые форматы” (“ Общий”, “Числовой” или другой), произвести необходимые установки в поле “Образец” и нажать кнопку “OK”.

Рис.2. Диалоговое окно для установки формата данных.

3.4.2. Редактирование содержимого ячейки

Если при просмотре таблицы вы обнаружили ошибки, их необходимо отредакти­ровать. Microsoft Excel позволяет редактировать содержимое ячеек таблицы дву­мя способами: набором новой информации поверх ошибочной (используется, как правило, при полной замене информации) или активизацией строки ввода и внесе­нием необходимых изменении. Для активизации строки ввода и перехода в ре­жим редактирования используется клавиша < F2 >. При этом в левой части строки ввода появляются символы “Ввод” (Ú) и “Отмена” (X).

3.4.3. Сохранение таблиц на диске

Excel предоставляет следующие команды в меню “Файл”, предназначенные для со­хранения данных:

· “Сохранить как” - используется для первоначальной записи документа и при­сваивания ему имени, которое дает пользователь. При активизации этой команды на экране монитора появляется диалоговое окно следующего вида (рис.3):

Рис.3. Выбор места сохранения файла.

· “Сохранить” - используется для сохранения изменений в активном документе. Она сохраняет документ под тем же именем и на том же месте, что и прежде. На стандартной панели инструментов есть кнопка “Сохранить”, выполняющая дей­ствия, аналогичные команде “Сохранить” в меню “Файл”. По умолчанию Excel уста­навливает стандартное расширение имени файла — XLS. Оно присоединится к имени файла автоматически.

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

3.4.4. Построение формул и использование функций Excel

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

Ввод формул и функций имеет общие правила:

· в формулу, кроме числовых величин, могут входить в качестве аргументов адреса ячеек или блоков, а также функции или другие формулы;

· если формула начинается с адреса ячейки, перед первым символом необходи­мо набрать = (знак равенства) или знак + (плюс);

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

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

Для ввода расчетной формулы необходимо активизировать ту ячейку, куда будет вводится формула и ввести в нее формулу, например: =Е5+F5, и нажать < Enter > или щелкнуть символ “Ввод” (Ú)в строке ввода. В ре­зультате в ячейке, в которой программируется формула появится число, равное сумме чисел, находящихся в ячейках E5 и F5, а в строке ввода отобразится сама фор­мула.

Ввод функций в Excel можно осуществлять несколькими способами:

· ручным набором имени функции и ее аргументов;

· выбором функции из списка функции с помощью команды “Вставка/Функция”;

· выбором функции из списка вызванного щелчком по кнопке (f)при условии, что строка ввода активна.

При выборе второго или третьего способа функция вводится с помощью “Мастера функций”, окно которого имеет следующий вид (рис.4а и рис.4б)

Рис.4а. “Мастер функций” на первом шаге выбора функции.

На первом шаге выбирается класс функций и затем конкретный вид функции. После этого следует нажать кнопку “Далее”.

После ввода аргументов на втором шаге следует нажать кнопку “ Готово”. Окно “Мастера функций” снимется, а в строке ввода появится запись программируемой функции.

Рис.4б. “Мастер функций” на втором шаге выбора функции.

3.4.5. Выделение блоков ячеек

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

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

Для работы с блоками Excel предоставляет большой набор команд: копирование, перемещение, вставка, удаление, форматирование и т. д.

3.4.6. Копирование формул

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

В Excel копирование может производиться несколькими способами.

1.Выделите копируемую ячейку, содержащую например формулу для копирования. Выберитекоманду “Правка/Копировать” и щелкните левой кнопкой мыши. Excel окружит исходную ячейку движущейся рамочкой. Выделите блок ячеек как область вставки. Далее выполните команду “Правка/Вставить” и затем снимите выделение блока. При копировании Excel автоматически корректирует адреса ячеек.

2.Можно копировать с помощью кнопки “Копировать в буфер” на панели инстру­ментов “Стандартная”. Для этого активизируйте исходную ячейку, щелкните кнопку “Копировать в буфер” на панели инструментов, выделите область для вставки и щелкните кнопку “Вставить из буфера” на этой же панели инструментов. Далее нажмите <Enter> и < Esc > на клавиатуре, чтобы выйти из режима копирования, и наконец щелкните кнопкой мыши вне выделенного блока для снятия выделения.

3.4.7. Расчет итоговых сумм с помощью функций

Ввод функций в Excel можно осуществлять несколькими способами:

· ручным набором имени функции и ее аргументов;

· выбором функции из списка функции с помощью команды “Вставка/Функция”;

· выбором функции из списка вызванного щелчком по кнопке (f)при условии, что строка ввода активна.

При программировании функции подсчета итоговых сумм по столбцам можно воспользоваться кнопкой “Автосумми-рование” на панели инструментов “Стандартная”. Для этого следует активизировать ячейку для автосуммирования, щелкнув по ней, а затем дважды щелкнуть кнопку “Автосуммирование”. Значение суммы данных в ячейка суммируемого столбца появится в выделенной ячейке, а в строке ввода отобразится формула суммы содержимого диапазона ячеек, например, =СУММ(Е5:Е11). Изменение содержимого ячеек данного столбца приведет к автоматическому пересчету результата в ячейке итоговой суммы.

Второй способ ввода функции при расчете итоговой суммы столбца заключается в следующем. Выберите команду “Вставка/Функция”. На экране появится диалоговое окно со списком встроенных функций, сгруппированных по категориям. Выберите функцию “СУММ” (в группе “ Математичес-кие”). Excel поместит па экране диалоговое окно, позволяющее ввести аргументы, необходимые для этой функции. Щелкните кнопку “ Далее” и введите аргументы, например — F5:F11, или отметьте их кнопкой мыши в ЭТ. Щелкните кнопку “Готово” или нажмите < Enter >. В ячейке итоговой величины появится значение суммы.

3.4.8. Форматирование данных

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

При работе с командой “Формат/Столбец/Ширина” можно выбирать и устанавливать необходимые форматы из диалогового окна (рис.5).

Рис.5. Диалоговое окно при установке ширины столбца. Размер устанавливается в пикселях.

Если выбрать опцию “ Формат/Столбец/Автоподбор Ширины”, то ширина выделенного столбца будет автоматически определена и установлено встроенными средствами Excel.

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

Для изменения высоты строк можно воспользоваться командой “ Формат/Строка/Высота” и устанавливать необходимые форматы из диалогового окна (рис.6).

Рис.6. Диалоговое окно при установке высоты строки. Размер устанавливается в пикселях.

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

При изменении размера шрифта высота строки изменяется автоматически.

3.4.9. Выравнивание данных

В Microsoft Excel (при стандартных параметрах) текст автоматически выравнивается по левому краю ячейки, а числа — по правому краю. Но пользователь может менять расположение данных в ячейках электронной таблицы по своему усмотрению.

Это можно сделать различными способами:

· с помощью кнопок панели инструментов “Форматирование”: “По левому краю”, “По центру” и “По правому краю”;

· с помощью команды “Формат/Ячейки” и установки на закладке “Выравнивание” (рис.7.)соответствующих команд, которые расширяют возможности управления расположением текста или данных в ячейке.

Рис.7. Диалоговое окно “Формат ячейки” с открытой закладкой “Выравнивание”

3.4.10. Форматирование с помощью рамок и цветов

Excel позволяет изменять цвета ячеек и блоков, а также заключать их в рамки. Можно нарисовать произвольную комбинацию горизонтальных и вертикальных линий слева, справа, вверху или внизу ячейки. Изменение внешнего вида электронной таблицы производится с помощью команд “Формат/Ячейки” и далее команд закладки “Рамка” (рис.8).

Рис.8. Диалоговое окно “Формат ячейки” с закладкой “Рамка”.

Можно изменить вид начертания шрифта, тип шрифта и т.д., для чего следует поработать с закладкой “Шрифт” после выполнения команды “Формат/Ячейки” (рис.9).

Рис.9. Диалоговое окно “Формат ячейки” с закладкой “Шрифт”.

Для измененияцветового фона заливки ячейки, строки, столбца либо блока ячеек необходимо выделить соответствующие элементы таблицы и выполнить команду “Формат/Ячейки”, открыть закладку “Вид” (рис.10)и в ней выбрать нужный цвет, после чего нажать кнопку “OK”.

Рис.10. Диалоговое окно “Формат ячейки” с закладкой “Вид”.

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

3.4.11. Вставка строк и столбцов электронной таблицы Excel

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

При вставке строк нужно учитывать следующие особенности:

· новые строки вставляются над строкой, в которой находится указатель мыши;

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

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

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

· все остальные столбцы сдвигаются вправо на количество вставленных столбцов и получают новые адреса.

3.4.12. Удаление ячеек, строк и столбцов

Для удаления элементов таблицы выделить удаляемый объект (ячейку, строку или столбец) выбратькоманду “Правка/Удалить” после чего снять выделение.

Если же необходимо только очистить содержимое ячеек, строк или столбцов, то после их выделения следует выполнить команду “Правка/Очистить”.

3.4.13. Основные приемы обработки рабочей книги

При необходимости создания нескольких однотипных таблиц с разной информацией в ячейках можно создать новые рабочие листы с в рамках первой книги для чего следует выделить таблицу на Листе1 скопировать ее в буфер с помощью команды “Правка/Копировать” (или кнопки “Копировать в буфер”). Далее открыть другой лист нажатием ярлычка с соответствующим именем (например Лист3)и выполнить команду “Правка/Вставить” (или нажать кнопку “Вставить из буфера”). В результате на Листе3 появится копируемая таблица, в которой можно осуществить редактирование информации.

3.5. Объединение и связывание электронных таблиц в Excel

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

При объединении и связывании таблиц используется абсолютная адресация. При­знаком абсолютного адреса является $ (знак доллара). В зависимости от позиции этого знака в адресе ячейки соответствующая область данных не модифицируется. Например: $А$1, $А1, А$1. Сделать адрес абсолютным можно несколькими спосо­бами:

· ввести знак $ с клавиатуры;

· выделить ячейку, затем дважды щелкнуть мышью в строке формул на адресе ячейки, чтобы выделить его, а затем нажать клавишу <F4>.

Объединять листы рабочей книги можно:

· суммированием содержимого ячеек соответствующих рабочих листов с ис­пользованием функции “Автосуммиро-вание”;

· консолидацией электронных таблиц;

· созданием сводных таблиц с помощью “Мастера сводных таблиц”, вызов которого производится с помощью команды “Данные/Сводная таблица” (далее необходимо следовать указаниям “Мастера”).

3.6. Консолидация данных в электронных таблицах

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

Консолидируем данные из столбцов G и Н из нескольких (например, трех) таблиц. Откроем все таблицы, данные из которых необходимо консолидировать. Пусть это будут таблицы со следующими именами “Книга1.xls”, “Книга2.xls”, “Книга3.xls”. Для перехода их одной таблицы в другую можно воспользоваться командой “Окно/Книга1.xls” - откроется первая таблица, “Окно/Книга2.xls” - вторая таблица, “Окно/Книга3.xls” - третья.

Для решения задачи консолидации выделим об­ласть назначенияв таблице итоговых результатов (пусть имя этой таблицы - “Книга4.xls”), например, ячейки В5:С11, далее

Рис.11. Диалоговое окно “Консолидация”

выберем команду “Данные/Консолидация” и в появившемся диалоговом окне “Консолидация” (рис.11) в поле “Функ-ция” установим функцию “Сумма”.

Затем в поле “Ссылка” определяем области-источники, которые нужно консолидировать. Для этого перейдем на лист с первой таблицей “Книга1.xls” с помощью команды “Окно/Книга1.xls” и выделим ячейки G5:H11. В окне “Консолидация” нажмем кнопку “Добавить”. В результате в поле “Список диапазона” сформировалась ссылка на выделенную область: [Книга1.xls]Лист1!$G$5:$Н$14 (рис.12). Excel использует здесь абсолютные ссылки на ячейки.

Рис.12. Диалоговое окно “Консолидация” с заполненной первой ссылкой.

Затем перейдем на лист “Книга2.xls” (с помощью команды “Окно/Книга2.xls”), выделим ячейки G5:H11 и в окне “Консолидация” нажмем кнопку “Добавить”. Далее перейдем на лист “Книга3.xls” (аналогично), выделим ячейки G5:H11 и также в окне “Консолидация” нажмем кнопку “Добавить”. В итоге окно “Консолидация” примет вид (рис.13).

Рис.13. Диалоговое окно “Консолидация” после заполнения ссылок (списка диапазона).

Далее устанавливаем флажок “Создавать связи с ис-ходными данными” и нажимаем кнопку <ОК>. Сводная таблица готова. При консолидации без установления связей изменение данных в таблицах-источниках не приведет к обновлению данных в итоговой таблице, поэтому при внесении изменений в таблицы-источники консолидацию необходимо повторить. Если флажок “Создавать связи с исходными данными” установлен, то область назначения в итоговой таблице будет автоматически обновляться при внесении изменений в областях-источниках.

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

3.7. Работа со структурированной таблицей

Структурирование данных рабочих листов используется в Excel при создании итоговых отчетов. Оно позволяет показывать или скрывать уровни структурированных данных, выводя на экран данные с большими или меньшими подробностями.

Обратимся к полученной итоговой таблице (“Кни-га4.xls”) и познакомимся с символами структуры (рис.14). Их два типа: кнопки с номерами уровней (в таблице это кнопки <1>, <2> и <3> находя­щиеся в левом верхнем углу рабочей области экрана) и знаки <+> (плюс) и/или <—> (минус), позволяющие соответственно раскрывать или скрывать детали структурированного документа.

Рис.14. Символы структуры итоговой таблицы.

Щелкнем, например, по кнопке <2>. Итоговая таблица «распахнулась», предоставив возможность просмотреть консолидируемые данные. Щелкнув по кнопке <1>, можно скрыть исходные данные из таблиц-источников. Щелкнем по любому из значков <+> (плюс). Результатом будет открытие одной из составляющих итоговой таблицы. Щелкнем по значку <-> (минус), в результате скроются исходные данные из таблиц-источников.

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

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

Диаграммы можно строить либо на рабочем листе таблицы, либо на новом рабочем листе. Создать диаграмму в Excel можно по шагам с помощью “Мастера диаграмм”, вызов которого осуществляется с панели инструментов “Диаграмма” (рис.15). Эта панель инструментов может быть открыта двумя способами:

· выполнить команду “Вид/Панели инструментов”, в открывшемся списке панелей пометить строку “Диаграмма” и нажать кнопку <OK>;

· щелкнуть правой кнопкой мыши в области открытых панелей инструментов за пределами кнопок и в открывшемся списке панелей пометить строку “Диаграмма”.

Рис.15. Панель инструментов “Диаграмма”.

Панель инструментов “Диаграмма” содержит следующие кнопки (слева направо):

· “Тип диаграммы” - Используется для изменения типа диаграмм отдельного ряда данных, набора рядов данных заданного элемента или диаграммы в целом;

· “Создать диаграмму текущего типа” - Используется для преобразования текущей диаграммы к формату, принятому по умолчанию;

· “Мастер диаграмм” - Используется для запуска мастера диаграмм, позволяющего за несколько последовательных шагов построить на листе новую внедренную диаграмму или изменить существующую диаграмму;

· “Горизонтальная сетка” -Используется для отображения или скрытия на диаграмме горизонтальных линий сетки;

· “Легенда” - Используется для вставки легенды справа от области построения. Размер области построения при этом изменяется. Если легенда уже отображается, то при нажатии на кнопку она будет удалена.

“ Мастер диаграмм” предоставляет широкий выбор различных типов диаграмм (рис. 16).

Рис.16. Типы диаграмм, которые можно построить с помощью “Мастера диаграмм”.

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

· просмотра, когда диаграмма выделена но периметру прямоугольником;

· перемещения, изменения размера и удаления, когда диаграмма по периметру выделена прямоугольником с маленькими квадратиками;

· редактирования, когда диаграмма выделена по периметру синим цветом или выделен синим цветом заголовок.

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

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

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

Добавить данные к диаграмме можно одним из двух способов.

1. Выделить данные на рабочем листе и перетащить их на диаграмму.

2. Выделить данные на рабочем листе, скопировать их в буфер и затем вставить в диаграмму с помощью команд “Правка/Копировать” и “Правка/Вставить” (или кнопок панели инструментов “Стандартная”: “Копировать в буфер” и “Вставить из буфера”).

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

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

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

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

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

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

Способ 1. Он заключается в открытии панели инструментов “Диаграмма” (рис.15), выделении на рабочем листе в таблице области исходных данных для диаграммы и нажатии кнопку “Создать диаграмму текущего типа”. Курсор мыши изменит свою форму и при повторном нажатии левой кнопки мыши на экране появится окно “Мастера диаграмм” (рис.17).

Рис.17. Шаг 1 “Мастера диаграмм”.

Далее для построения простейшей диаграммы следует нажать кнопку “Готово”, после чего на экране появится диаграмма следующего вида (рис.18).

Рис.18. Вид диаграммы по умолчанию.

Щелкнем кнопку “Горизонтальная сетка” (на панели “Диаграмма”)- на графике исчезла сетка. Повторный щелчок по кнопке восстанавливает сетку.

Щелкнем на панели “Диаграмма” кнопку “Легенда” и с графика исчезает легенда (условные обозначения Ряд1, Ряд2 на рис 18). Повторный щелчок восстанавливает легенду.

Щелкните кнопку “Тип диаграммы” (на стрелочку слева от кнопки). На экране появятся иконки возможных типов диаграмм, выберите один из них, и ваша диаграмма изменит свой вид. Просмотрите несколько типов диаграмм и остановитесь на том, который необходим.

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

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

Для удаления диаграммы выделите ее, а затем нажмите клавишу <Del>, и диаграмма будет удалена с листа. Для удаления диаграммы можно также выбрать команду “Правка/Очистить/Все”.

Способ 2. Сначала указываются исходные данные для построения диаграммы. Первая строка из этой области, то есть заголовки граф, будет использована в качестве легенды. Затем щелкните кнопку “Мастер диаграмм” на панели инструментов “Стандартная”.

Вид курсора изменится так же, как в предыдущем примере. После этого необходимо указать область для размещения диаграммы и нажать левую кнопку мыши. На экране появится первое окно “Мастера диаграмм” с описанием шага 1 (см. рис.17). Убедитесь, что область исходных данных задана правильно, а затем нажмите кнопку “Далее”.

На экране появится следующее диалоговое окно “Мастера диаграмм” с описанием шага 2 (рис.19).

Рис.19. Шаг 2 “Мастера диаграмм”.

Выберите тип диаграммы - гистограмма и перейдите к следующему шагу, нажав кнопку “Далее”.

На экране появляется следующее диалоговое окно “Мастера диаграмм” (рис.20).

Рис.20. Шаг 3 “Мастера диаграмм”.

Выбираем требуемый формат гистограммы и нажимает кнопку “Далее”. На шаге 4 на экран выводится диалоговое окно (рис. 21). Здесь можно посмотреть вид графика, а также определить, как строится график по исходным данным: по строкам пли столбцам. Выбирая поочередно эти два варианта можно сравнить получаемые графики и остановиться на том, который больше устраивает.

Рис.21. Шаг 4 “Мастера диаграмм”.

Если вид полученного графика устраивает, то можно переходить к следующему шагу, нажав кнопку “Далее”. На экране появится диалоговое окно шага 5 “Мастера диаграмм”. В этом окне в соответствующих строках вводятся: название диаграммы, название данных по оси “X” (категорий) и название данных по оси “Y” (значений), после введения которых окно принимает вид, подобный показанному на рис.22.

Рис.22. Диалоговое окно ”Мастера диаграмм” на шаге 5.

После выполнения действий шага 5 следует нажать кнопку “Готово” - в результате диаграмма выводится на то место рабочего листа, где после запуска ”Мастера диаграмм” был установлен указатель мыши. Например, диаграмма может иметь вид, подобный представленному на рис.23.

При построении диаграммы на любом шаге имеется возможность возвратиться к предыдущему шагу, нажав кнопку “Назад”. Пользователь может при этом оперативно изменить вид диаграммы, другие вводимые данные на любом шаге создания диаграммы, просматривая результаты изменений в окне “Образец” на шагах 4 или 5 до того момента как нажата кнопка “Готово”. После нажатия этой кнопки “Мастер диаграмм” закрывается и диаграмма после этого редактируется другими приемами.

Рис.23. Конечный вид диаграммы (один из вариантов).

Способ 3. Этот способ практически повторяет все шаги способа 2, однако вызов “Мастера диаграмм” осуществляется после ввода команды из меню “Вставка/Диаграмма/На этом месте”. Если же требуется построить диаграмму на отдельном рабочем листе, то необходимо ввести команду “Вставка/Диаграмма/На этом месте” или нажать клавишу “F11”. Далее на экране последовательно появляются диалоговые окна “Мастера диаграмм” и после нажатия кнопки “Готово” на любом шаге создаваемая диаграмма появляется на новом рабочем листе.

3.10. Редактирование вида диаграммы

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

Рис.24. Контестное меню диаграммы.

Далее следует выделить в контекстном меню строку “Редактировать объект” (рис.24)и нажать левую кнопку мыши. Диаграмма примет следующий вид (рис.25).

Рис.25. Вид диаграммы после активизации режима редактирования объекта.

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

Рис.26. Вид контекстного меню в режиме редактирования диаграммы.

Рассмотрим основные команды редактирования диаграммы.

· Команда “Очистить” приводит к полной очистке выделенной области.

· Команда “Вставить название” вызывает на экран диалоговое окно “Названия элементов диаграммы” (рис.27), с помощью которого имеется возможность при установке соответствующих флажков ввести или снять названия отдельных элементов диаграммы. После выбора или снятия соответствующих флажков следует нажать кнопку “OK”. Вставляемый элемент появляется на диаграмме в рамочке в обобщенной форме (“Название”, “Y”, “X”), вместо которых можно ввести с клавиатуры требуемое название.

Рис.27. Диалоговое окно редактирования названий элементов диаграммы.

· Команда “Вставить оси” приводит к появлению на экране диалогового окна “Оси диаграммы” (рис.28).

Рис.28. Диалоговое окно редактирования осей диаграммы.

Устанавливая и снимая соответствующие флажки (после нажатия кнопки “OK”) пользователь может по своему усмотрению снять или установит отдельные оси диаграммы.

· Команда “Сетка” позволяетвызвать диалоговое окно “Сетка диаграммы” (рис.29),с помощью которого имеется возможность установки или снятия сетки по осям диаграммы.

Рис.29. Диалоговое окно редактирования сетки диаграммы.

· Команда “Метки значений” приводит к появлению на экране одноименного окна (рис.30).

Рис.30. Вид диалогового окна “Метки значений”.

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

· Команда “Формат области диаграммы” выводитна экране одноименное диалоговое окно следующего вида (рис.31).

Рис.31. Диалоговое окно “Форматирование области диаграммы”

С помощью опций данного окна в закладках “Вид” и “Шрифт” пользователь может изменять параметры рамки, заливки, шрифта и тем самым видоизменять внешний вид диаграммы.

· Команда “Тип диаграммы” вызывает на экран одноименное диалоговое окно (рис.32), в котором можно задать тип диаграммы(“Плоская” или “Объемная”), а также определить область изменении (вся диаграмма или ее часть).

Рис.32. Диалоговое окно “Тип диаграммы”.

Рассмотрим некоторые возможности. Выберем тип диаграммы — “Объемная” (при этом несколько изменяется вид образцов диаграмм в окне на рис.32) и нажмем кнопку “ Параметры”. В открывшемся диалоговом окне “Элементы диаграммы с областями” (рис.33) предоставляется возможность в закладках “Вид”, “Порядок рядов”, “Параметры”, “Ось” изменять различные установки и параметры так, что в результате вид объемной диаграммы принимает желаемый образ (поворачивается, наклоняется, изменяет угол визирования и т.д.).

· Команда “Автоформат” позволяет быстро выбрать любое удобное представление графика, которое после нажатия кнопки “OK” в диалоговом окне “Автоформат” (рис.34)

Рис.33. Закладка “Вид” в диалоговом окне “Элементы диаграммы с областями”

Рис.34. Диалоговое окно “Автоформат”.

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

· Команда “Объемный вид” позволяет открыть диалоговое окно “Форматирование объемного вида” (рис.35), с помощью которого можно специальным образом редактировать вид объемных диаграмм.

Рис.35. Диалоговое окно редактирования объемного вида диаграммы.

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

· Команда “Элементы диаграммы с областями” открывает доступ к одноименному диалоговому окну (рис.33).

3.11. Трендовый анализ и анализ погрешностей

Excel имеет специальный аппарат для графического анализа моделей, в том числе возможность построения линий тренда, которые могут использоваться для анализа и прогнозирования данных. Трендовый анализ проводится по данным, представленным в виде линейного графика. Отметим область данных для построения графика, например, А17:В28 н щелкнем левую кнопку “Мастера диаграмм”. Укажем область для построения графика - пусть это будет С17:Н31, и по шагам выполним построение диаграммы, при этом выберем тип диаграммы — “График”, формат — 2 (это линейная диаграмма).

Построим линию тренда. Для этого дважды щелкнем по графику для перехода в режим редактирования, затем выделим ряд данных для построения тренда, щелкнув по линии графика (выделение будет произведено черными квадратиками), выберем пункт меню “Вставка/Линия тренда”, и на экране появится диалоговое окно (рис.36) - закладка “Тип”. На этой закладке следует установить способ аппроксимации линии тренда, степень аппроксимирующего полинома, количество точек аппроксимации.

Рис.36. Вид диалогового окна “Линия тренда” - вкладка “Тип”.

Для осуществления прогноза следует открыть вкладку “Параметры” (рис.37) и установить следующие параметры: “Прогноз вперед на <количество > периодов”, “Показать уравнение на диаграмме”. Затем нажать кнопку “OK”. На диаграмме появится линия тренда и прогноз изменения данных на установленное число периодов.

Рис.37. Вид диалогового окна “Линия тренда” - вкладка “Параметры”.

Линию тренда можно форматировать. Для этого выделим линию тренда, щелкнув но ней мышью (выделенный объект будет помечен квадратиками), затем щелкнем правой кнопкой мыши по линии тренда и из появившегося контекстного меню выберем пункт“ Формат линии тренда". Далее с помощью опций вкладок ”Вид”, “Тип”, “Параметры” одноименного диалогового окна (рис.38) можно установить желаемый вид линии тренда, тип аппроксимации и другие параметры.

Рис.38. Диалоговое окно “ Форматирова­ние линии тренда".

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

Рис.39. Диалоговое окно “ Планки погрешностей".

Планки погрешностей можно редактировать аналогично как и линии тренда для чего следует их выделить щелкнув левой кнопки мыши, затем щелкнем правой кнопкой мыши по выделенному объекту и из появившегося контекстного меню выберем пункт “Формат планок погрешностей”. Появится диалоговое окно (рис.40), с вкладками “Вид” и “Y-погрешности”, через опции которого можно изменить вид и другие параметры вывода на график планок погрешностей.

Рис.40. Диалоговое окно “Форматирование планок погрешностей".

Сортировка данных

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

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

В диалоговомокне “Сортировка диапазона” имеется режим“ Параметры”. Он позволяет установить порядок сортировки по первому ключу — обычный или определяемый пользователем, задать учет строчных и прописных букв (учет регистра символов), а также направление сортировки — по возрастанию или по убыванию.

Рис.41. Диалоговое окно “Сортировка диапазона”

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

Рис.42. Диалоговое окно “Параметры сортировки”.

Для осуществления быстрой сортировки на панели инструментов “Стандартная” имеются две кнопки, перед нажатием которых необходимо выделить столбец или строку таблицы подлежащих сортировке. Эти кнопки следующие:

· “По возрастанию” — сортировка данных по возрастанию;

· “По убыванию” — сортировка данных по убыванию.

Ключом сортировки в этом случае является столбец с текущей ячейкой.