Понятия Microsoft Excel

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Содержание

табличный процессор электронный ячейка

Введение

1. Структура и краткое содержание курса

1.1 Основные понятия Microsoft Excel. Ввод данных

1.1.1 Окно программы Microsoft Excel

1.1.2 Рабочая книга и ее структура

1.1.3 Типы данных в ячейках электронной таблицы

1.1.4 Ввод и редактирование данных

1.1.5 Автоматизация ввода данных

1.1.6 Операции над рабочими листами

1.2 Вычисления в Microsoft Excel

1.2.1 Составление формул

1.2.2 Типы ссылок

1.2.3 Использование именованных ячеек

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

1.2.5 Использование встроенных функций

1.2.6 Мастер суммирования

1.3 Средства форматирования таблиц

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

1.3.2 Создание собственных форматов

1.3.3 Выравнивание данных в ячейках

1.3.4 Создание обрамления и заливки

1.3.5 Копирование форматов

1.4 Графическое представление данных

1.4.1 Построение диаграмм

1.4.2 Редактирование диаграмм

1.4.3 Форматирование диаграмм

1.5 Работа со списками

1.5.1 Основные операции, выполняемые над списками

1.5.2 Сортировка списков

1.5.3 Фильтрация списков

1.5.4 Сводные таблицы

2. Методические рекомендации по выполнению контрольных работ

2.1 Теоретическая часть контрольной работы

2.2 Практическая часть контрольной работы

3. Вопросы итогового контроля

Список литературы

Введение

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

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

1. Структура и краткое содержание курса

1.1 Основные понятия Microsoft Excel. Ввод данных

1.1.1 Окно программы Microsoft Excel

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

Рис. 1. Окно программы MS Excel

1 _ Поле имени содержит адрес активной ячейки.

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

2 _ Строка формул служит для ввода и редактирования данных в ячейках. В ней отображаются введенные в ячейку формулы.

3 - Пустая кнопка для выделения всего листа.

4 - Кнопки навигации по листам.

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

Среднее значение;

Наибольшее или наименьшее значение;

Количество чисел (число ячеек, содержащих числа);

Количество значений (число непустых ячеек).

При запуске Excel для автовычислений устанавливается функция СУММ.

1.1.2 Рабочая книга и ее структура

Документ в программе Excel принято называть рабочей книгой.

Рабочая книга Excel - совокупность Рабочих листов, сохраняемых на диске в одном файле. Этот файл имеет расширение *. xls. По умолчанию в каждой книге содержится 3 листа. Это число можно изменить с помощью команды Сервис Параметры вкладка Общие на счетчике Листов в новой книге указать значение от 1 до 255. Рабочие листы можно удалять, переставлять местами, копировать, добавлять новые, переименовывать. Команды для выполнения этих действий можно найти в контекстном меню ярлыка листа. Рабочий лист состоит из 65 536 строк и 256 столбцов (колонок). Строки нумеруются числами (от 1 до 65 536), а столбцы обычно обозначаются буквами латинского алфавита А, В, С, ..., Z. После столбца Z следуют столбцы АА, АВ, АС, ВА ВВ ... IV.

Минимальным элементом Рабочего листа является ячейка. Адрес ячейки составляется из обозначений столбца и номера строки, на пересечении которых находится эта ячейка, например: Al, C24, АВ2. Для обозначения ячеек существуют два типа ссылок. Тип ссылок задается пользователем при настройке параметров работы с помощью команды меню Сервис Параметры вкладка Общие переключатель Стиль ссылок - R1C1 или А1 - по умолчанию. При установленном переключателе R1C1 строки и столбцы обозначаются цифрами (строка № 1 -- Row 1 и столбец № 1 -- Column 1). Например: R1C1.

Блок (диапазон) ячеек - группа смежных или несмежных ячеек. Ссылки на диапазон смежных ячеек состоят из адреса верхней левой и нижней правой ячеек диапазона, например: А1:С12. Ссылки на диапазон несмежных ячеек состоят из адресов ячеек, разделенных точкой с запятой, например: А1;С12. Несмежные диапазоны выделяются с нажатой клавишей Ctrl. Возможна комбинация смежных и несмежных диапазонов, например: А1:С12;А20:С25.

1.1.3 Типы данных в ячейках электронной таблицы

В ячейки рабочего листа можно вводить данные двух типов: константы и формулы. Константы могут быть следующих типов: числовые, текстовые, даты и времени, а также двух специальных типов -- логические значения и ошибочные значения. Число в Excel может состоять только из следующих символов: цифры от 0 до 9, +, -, (,), /, $, %, (.), Е, е. Запятая в числе интерпретируется как разделитель десятичных разрядов.

Существуют следующие правила ввода чисел:

Если ввод числа начинается со знака «+» или «-», Excel опускает «+» и сохраняет «-», интерпретируя введенное значение как отрицательное число.

Числовые значения, заключенные в круглые скобки, интерпретируются как отрицательные. Например, (5) интерпретируется как -5. Символ Е или е используется при вводе чисел в экспоненциальном представлении. Например, 1Е6 интерпретируется как 1000000 (единица, умноженная на десять в шестой степени). При вводе больших чисел можно вставлять пробел для отделения сотен от тысяч, тысяч от миллионов и т. д. При таком вводе числа в ячейках появляются с пробелами, а в строке формул без пробелов. Если ввод числа начать со знака денежной единицы, к ячейке будет применен денежный формат. Если ввод числа закончить знаком %, к ячейке будет применен процентный формат. Перед вводом рациональной дроби, чтобы Excel не интерпретировал ее как дату, следует ввести 0 и пробел, например ³/₄ ввести как 0 ³/₄. Числа можно вводить в различных форматах. В Excel имеется набор стандартных числовых форматов, которые при необходимости могут быть изменены. Также можно создавать собственные пользовательские форматы. Обычно ввод чисел осуществляется в общем числовом формате. В соответствии с ним числа в ячейке отражаются в привычном виде. Если длина числа не превышает ширину ячейки, то оно отображается в том виде, в котором вводится, если превышает, то число будет выведено в экспоненциальной форме. Если значение числа превышает допустимое по формату значение, то в ячейке выводится признак переполнения -- ####. Изменить стандартный формат можно на вкладке Вид команды Ячейки меню Формат.

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

Облегчить чтение длинных текстовых значений может также перенос текста. Этот режим позволяет вводить длинные текстовые значения с переносом на следующие строки без наложения текста на другие ячейки. При этом Excel увеличивает высоту строки, которая содержит ячейку с дополнительными строками. Чтобы установить это режим, следует включить флажок Переносить по словам на вкладке Выравнивание команды Ячейки меню Формат. В Excel дата и время суток интерпретируются как числа. Основной единицей измерения времени в Excel являются сутки. Они представляются последовательными десятичными значениями от 1 до 65380. Базовая дата, представляемая десятичным числом 1, _ это воскресенье, 1 января 1900 года. Максимальное десятичное значение даты 65380 представляет 31 декабря 2078 года. При вводе даты Excel сохраняет ее в виде десятичного значения, которое равно количеству дней между заданной и базовой датой. Время суток - это десятичная дробь, которая представляет часть суток между их началом (12:00 ночи) и заданным временем. Например, 12:00 дня представляется значением 0,5. Внешнее представление в ячейках рабочего листа зависит от формата, назначенного ячейке. В форматах даты и времени используются следующие разделители: «.», «/», «_» _ для даты; «:» _ для времени. Данные типа даты и времени суток могут участвовать в вычислениях (сложение, вычитание), а также входить в состав формул в виде текста (тогда их необходимо заключить в двойные кавычки).

1.1.4 Ввод и редактирование данных

Ввод данных производится в активную ячейку. Закончить ввод данных можно, нажав клавиши Enter или Tab, при этом активная ячейка смещается вниз или вправо. Отредактировать данные можно несколькими способами:

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

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

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

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

1.1.5 Автоматизация ввода данных

В Excel существует несколько способов, для ускорения ввода данных:

1. Копирование данных - выполняется протаскиванием с помощью маркера автозаполнения.

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

С помощью автозаполнения можно вводить последовательности дней недели и месяцев. Можно создать собственную последовательность для автозаполнения. Для этого необходимо:

Выбрать команду Сервис Параметры вкладка Списки.

В элементе управления Списки сделать активным пункт Новый список.

В поле Элементы списка ввести элементы списка. При этом каждый новый элемент вводится с новой строки.

Нажать кнопку Добавить.

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

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

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

Выполнить команду меню Правка Заполнить Прогрессия.

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



Рис. 2. Задание параметров создаваемой прогрессии

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

1.1.6 Операции над рабочими листами

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

выделять рабочие листы;

вставлять новые листы;

удалять листы;

переименовывать листы;

перемещать и копировать листы в пределах одной книги или в другую книгу.

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

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

с помощью команды Формат Лист Переименовать;

с помощью команды Переименовать контекстного меню ярлыка листа.

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

с помощью команды Правка Переместить/Скопировать лист;

с помощью мыши аналогично перемещению листа внутри книги.

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

1.2 Вычисления в Microsoft Excel

1.2.1 Составление формул

Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение. Формулы содержат вычисляемые элементы (операнды) и операторы. Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена, функции. По умолчанию вычисление по формуле осуществляется слева направо, начиная с символа «=». Для изменения порядка вычисления в формуле используются скобки. В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для основных математических вычислений над числами, том вычисления формул, содержащих арифметические операторы, всегда является число. К арифметическим операторам относятся: +, -, *, /, %, ^. Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, <, >=, <=, <>.Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность. Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся:

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

точка с запятой _ оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ (В5:В15,С15:С25);

пробел -- оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ (В4:С6 B4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

Приоритет выполнения операций:

операторы ссылок (адресные) «:», «,», « »;

знаковый минус «_»;

вычисление процента %;

арифметические ^, *, /, +, -;

текстовый оператор &;

операторы сравнений =, <, >, <=, >=, <>.

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

1.2.2 Типы ссылок

Ссылка является идентификатором ячейки или группы ячеек в книге. При создании формул, содержащих ссылки на ячейки, формула связывается с ячейками книги. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки, и оно изменяется при изменении содержимого этих ячеек. Можно ссылаться на ячейки, находящиеся на других листах книги, или в другой книге, или даже на данные другого приложения. Ссылки на ячейки других книг называются внешними. Ссылки на данные других приложений называются удаленными. В Excel существуют три типа ссылок: относительные, абсолютные, смешанные. Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула=А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1 -- =В1+С1. Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.

Смешанные ссылки -- это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D7. Изменение типа ссылки для редактируемого адреса производится с помощью клавиши F4. Эта клавиша работает как четырех позиционный переключатель. Первый щелчок изменяет относительный адрес (А1) на абсолютный ($A$1), второй на смешанный (A$1), третий на смешанный ($A1), четвертый на относительный (А1). Ссылки на ячейки других листов книги имеют следующий формат: <имя рабочего листа>!<ссылка на ячейку>, например, Лист2!А1:А10. Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например, 'лицевой счет'! А1:А10. Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например, Лист1:Лист5!$А$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!<ссылка на ячейку>, например: [книга2] Лист3!Е5:Е15.

1.2.3 Использование именованных ячеек

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

I способ:

1. Выделить ячейку.

2. Дать команду Вставка Имя Присвоить.

3. В диалоговом окне ввести имя.

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

1. Выделить ячейки (включая ячейку с текстом).

2. Дать команду Вставка Имя Создать.

3. В диалоговом окне выбрать вариант создания имени (см. рис. 3).

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

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

Для копирования формул можно использовать:

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

Копирование через буфер обмена.

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

1.2.5 Использование встроенных функций

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

=имя_функции(аргументы)

Существуют следующие правила ввода функций:

Имя функции всегда вводится после знака «=».

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

Между именем функции и знаком « ( » пробел не ставится.

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

Методика использования Мастера функций:

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

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

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

2 шаг. Ввести аргументы функции (щелкая по ним мышью).

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

Нажать кнопку ОК.

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

Основные категории функций:

Арифметические и тригонометрические функции.

Инженерные функции, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую). Информационные функции, предназначенные для определения типа данных, хранимых в ячейках. Логические функции, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ). Статистические функции, предназначенные для выполнения статистического анализа. Финансовые функции, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.

Функции баз данных, предназначенные для анализа, данных из списков или баз данных. Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т. д.). Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах. Одной из наиболее часто используемых функций является функция СУММ. Для нее на панели инструментов вынесена отдельная кнопка Автосумма.

Технология работы с кнопкой Автосумма:

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

щелкнуть по кнопке Автосумма;

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

щелкнуть по кнопке Автосумма.

Пример 2.1.

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

ПРОСМОТР (Искомое_значение; Вектор_просмотра; Вектор_результата)

Для корректной работы функции значения в аргументе Вектор_просмотра должны быть отсортированы в порядке возрастания. Предположим, что в ячейку С2 Листа1 будет заноситься код покупателя, а по этому коду требуется найти из имеющегося списка покупателей полное наименование организации и ее реквизиты (см. задание для выполнения контрольной работы). Условимся, что список покупателей располагается на листе Покупатели в диапазоне ячеек А2:D8. В столбце А располагаются коды покупателей, а в столбце В - полное наименование. В качестве Искомого значения будет выступать внесенное значение кода покупателя, задаваемое адресом ячейки. Т.е. первым аргументом функции будет адрес ячейки, в которую введен код покупателя, в нашем случае, С2.

Вектор просмотра - это диапазон ячеек листа Покупатели, в которых введены коды покупателей, т.е. А2:А8. Вектором результата будет соответственно диапазон В2:В8 из того же листа Покупатели. Вид окна функции ПРОСМОТР (рис. 3):

Рис. 3. Окно ввода данных для функции ПРОСМОТР

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

1.2.6 Мастер суммирования

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

Работа Мастера суммирования делится на четыре шага.

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

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

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

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

Пример 2.2.

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

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

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

Рис. 4. Второй шаг Мастера суммирования

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

Полученный результат можно скопировать на остальные организации.

1.3 Средства форматирования таблиц

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

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

I способ - с помощью кнопок на панели инструментов Форматирование.

1 - кнопка Денежный формат - служит для присвоения денежного стиля. Используемый символ валюты зависит от того, какая страна указана в диалоговом окне Свойства: язык и стандарты (Пуск Настройка Панель управления Язык и стандарты).

2 - кнопка Процентный формат - предназначена для присвоения ячейкам процентного стиля.

3 - кнопка Формат с разделителями - позволяет разделять разряды чисел при помощи пробела.

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

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

II способ - с помощью команды Формат Ячейки вкладка Число.

Вкладка Число содержит числовые форматы, объединенные в категории.

Рис. 5

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

1.3.2 Создание собственных форматов

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

выполнить команду Формат Ячейки вкладка Число.

выбрать категорию Все форматы в поле Числовые форматы.

удалить содержимое поля Тип.

ввести новый код формата (маску формата).

При создании кода можно использовать специальные символы подстановки - # и 0. Указанием символа 0 задается отображение на экране незначащих нулей, при использовании символа # незначащие нули не отображаются. Так же можно использовать пробел для задания разделителя тысяч, и запятую для задания десятичного разделителя. Символы, используемые для обозначения единицы измерения, вводятся в кавычках. Например: ##0,00 “кг”.

1.3.3 Выравнивание данных в ячейках

Задать способ отображения данных в ячейке позволяет команда Формат Ячейки вкладка Выравнивание. Она содержит три области (рис. 6):

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Рис. 6. Выравнивание ячеек

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

переносить по словам - расположение текста в ячейке на нескольких строках (левый Alt + Enter);

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

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

1.3.4 Создание обрамления и заливки

I способ - с помощью кнопок панели инструментов Форматирование.

1. Кнопка Границы открывает список вариантов обрамления таблицы.

2. Кнопка Цвет заливки открывает палитру цветов.

II способ - с помощью команды Формат Ячейки вкладка Граница (Вид). Вкладка Граница позволяет создать сложные варианты обрамления для таблицы. В поле Линия выбирают тип линии и цвет. Затем указывают местоположение линии в таблице. Если нужны все границы, то включают кнопки Внешние и Внутренние в поле Все. Если требуется провести отдельные линии, то выбирают нужную кнопку в поле Отдельные (рис. 7).

Рис. 7. Создание обрамления для таблицы

1.3.5 Копирование форматов

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

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

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

Правка Копировать (или кнопка Копировать);

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

Правка Специальная вставка Вставить форматы.

1.4 Графическое представление данных

1.4.1 Построение диаграмм

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

внедренные диаграммы - сохраняются на рабочем листе вместе с данными;

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

Для построения диаграмм в Excel используется Мастер диаграмм, вызываемый кнопкой на панели инструментов Стандартная.

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

Шаг 1. Тип диаграммы.

На этом шаге выбирается тип диаграммы. Различают стандартные и нестандартные типы диаграмм. Внутри каждого типа существуют специфические форматы диаграммы. По внешнему виду различают плоскостные и объемные диаграммы.

Основные типы диаграмм:

Шаг 2. Источник данных диаграммы.

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

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

Шаг 3. Параметры диаграммы.

На этом шаге можно:

указать заголовки диаграммы и осей (вкладка Заголовки);

выбрать оси, которые должны отображаться (вкладка Оси);

задать сетку по осям (вкладка Линии сетки);

указать наличие и местоположение легенды (вкладка Легенда);

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

задать вывод таблицы данных _ под осью Х повторяется таблица с исходными данными (вкладка Таблица данных).

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

Существует два варианта:

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

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

1.4.2 Редактирование диаграмм

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

1. Через меню Диаграмма. Когда диаграмма активна, в строке меню появляется новый пункт - Диаграмма. Он содержит команды, название которых совпадает с названиями четырех шагов, пройденных при построении диаграммы.

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

1.4.3 Форматирование диаграмм

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

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

1. Через меню Формат. Когда выделен один из элементов диаграммы, то в меню Формат появляется команда Формат… Она открывает диалоговое окно для изменения возможных параметров форматирования.

2. Через контекстное меню. Если щелкнуть правой кнопкой мыши на необходимом элементе диаграммы, то появится контекстное меню, которое содержит команду Формат…, которое также приводит к открытию диалогового окна.

1.5 Работа со списками

1.5.1 Основные операции, выполняемые над списками

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

База данных (список) - таблица прямоугольной конфигурации, столбцы которой считаются полями, строки - записями базы данных. Чтобы содержимое рабочего листа рассматривалось как база данных Excel, необходимо придерживаться следующих правил. Список должен помещается на одном рабочем листе, максимальный размер списка - 65 535 строк и 256 столбцов. Имена полей списка должны располагаться в первой строке таблицы, имена могут быть многострочными с переносами слов. Имя поля должно занимать не более одной ячейки.

Ячейки одного столбца списка содержат однородную информацию.

Список не должен содержать пустых строк и столбцов.

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

Основные операции с базами данных:

Ввод и редактирование данных с помощью специальной формы данных.

Сортировка данных - упорядочивание записей списка.

Фильтрация данных - отбор записей удовлетворяющих заданным условиям.

Средства автоматического подведения общих и промежуточных итогов.

Консолидация - объединение данных нескольких списков с формированием общих итогов).

Создание сводных таблиц, отчетов и диаграмм.

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

1.5.2 Сортировка списков

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

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

обозначениям столбцов листа -- если в качестве меток столбцов использовать заголовки столбцов рабочего листа (А, В, С и т. д.).

Рис. 8. Диалоговое окно сортировки

1.5.3 Фильтрация списков

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

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

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

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

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

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

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

Установленный фильтр можно удалить. Чтобы удалить фильтр из одного столбца списка, следует выбрать в списке элементов элемент Все. Чтобы удалить фильтры из всех столбцов списка, необходимо выполнить команду Данные Фильтр Отобразить все. Чтобы удалить автофильтр из списка, необходимо повторно выполнить команду Данные Фильтр Автофильтр

1.5.4 Сводные таблицы

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

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

Первый шаг - выбор типа источника данных и вида результата (рис. 9);

Если исходные данные расположены на одном листе установите переключатель В списке или базе данных Microsoft Excel.

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

Рис 9. 1 шаг Мастера сводных таблиц

Второй шаг - указание диапазона ячеек для построения сводной таблицы (рис. 10).

Рис 10. 2 шаг Мастера сводных таблиц

Третий шаг - формирование макета (структуры) сводной таблицы - кнопка Макет.

Макет сводной таблицы включает области (рис. 11):

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

Строка, Столбец - для размещения полей группирования.

Данные - для размещения полей итогов.

Рис. 11. Просмотр макета сводных таблиц

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

В область Страница поместить поле, в котором нужно производить фильтрацию записей.

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

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

Сумма (по умолчанию);

Количество значений;

Среднее;

Максимум и др.

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

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



Рис. 12. Выбор поля подведения итогов

По завершении формирования макета сводной таблицы закрываем окно кнопкой ОК и возвращаемся в окно третьего шага для выбора места расположения сводной таблицы:

новый лист;

существующий лист.

Кнопка Готово завершает процесс создания сводной таблицы.

После создания сводной таблицы автоматически открывается панель инструментов Сводные таблицы (Рис. 13).

Рис. 13. Панель инструментов Сводные таблицы

Кнопка Сводная таблица вызывает меню команд:

Формат отчета - для выбора одного из 10 автоформатов для отчета сводной таблицы.

Сводная диаграмма - для построения диаграммы по сводной таблице.

Мастер - для перехода к 3-му шагу построения сводной таблицы, который обеспечивает изменение макета и выбор места размещения сводной таблицы.

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

Выделить - выделить отдельные части сводной таблицы: заголовки, данные или сводную таблицу целиком.

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

Для построения сводной диаграммы необходимо:

Установить курсор в область сводной таблицы.

На панели инструментов Сводные таблицы раскрыть список Сводная таблица и выбрать команду Сводная диаграмма.

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

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

2. Методические рекомендации по выполнению контрольных работ

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

Контрольная работа состоит из двух разделов: теоретического раздела и практического задания.

2.1 Теоретическая часть контрольной работы

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

Таблица 1

/п	Наименование темы	Буква, с которой начинается фамилия
	Табличный процессор Microsoft Excel. История появления и развития электронных таблиц. Понятие электронной таблицы. Интерфейс табличного процессора: строки, столбцы, ячейки и их адреса, указание блока ячеек, текущая ячейка и экран, окно, рабочая книга, лист. Данные, хранимые в ячейках электронной таблицы, типы входных данных. Формулы. Функции. Абсолютная и относительная адресация, правило относительной ориентации клетки. Автоматическое изменение относительных ссылок при копировании и перемещении формул. Графические возможности: использование графики, карт, виды и создание диаграмм.	А, И, С, Щ
	Бизнес-анализ с использованием электронных таблиц Microsoft Excel Базы данных (списки). Создание итогового отчета. Сводные таблицы. Консолидация данных. Таблица подстановки. Подбор параметра. Поиск решения. Диспетчер сценариев. Защита данных. Макросы.	Б, К, Т, Э
	Компьютерные сети - коммуникационная среда и передача данных. Назначение и классификация компьютерных сетей. Обобщенная структура компьютерной сети. Схема "клиент-сервер". Аппаратная реализация передачи данных: способы передачи цифровой информации, аппаратные средства, характеристики коммуникационной сети. Протоколы компьютерной сети.	В, Л, У, Ю
	Локальные вычислительные сети. (ЛВС) Назначение ЛВС. Особенности организации ЛВС. Понятие сервера, рабочей станции, клиента. Типовые топологии и методы доступа ЛВС.	Г, М, Ф, Я
	Глобальная сеть Интернет. История возникновения и развития Интернета. Представление о структуре и системе адресации.	Д, Н, Х
	Основные службы Интернет. Способы организации передачи информации. Электронная почта. World Wide Web (Всемирная информационная сеть). Телеконференции Usernet. Передача файлов с помощью протокола FTP. Взаимодействие с другим компьютером (Telnet).	Е, О, Ц
	Средства поиска информации в сети Интернет. Виды поисковых систем: поисковые машины и поисковые каталоги. Особенности организации и методов работы. Язык запросов в поисковых, системах.	Ж, П, Ч
	Гипертекст. Понятие гипертекста. Гипертекстовые ссылки. Гипермедиа. Понятия Web-страницы и Web-сайта. Главная страница сайта. Язык HTML, как средство создания Web-страниц. Теги. Размещение Web-страниц в сети Интернет.	З, Р, Ш

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

2.2 Практическая часть контрольной работы

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

Постановка задачи:

На листе Товары создайте таблицу по образцу:

Код товара	Наименование товара	Ед. изм.	Цена
100	Помидоры	кг.	20
101	Огурцы	кг.	15

Введите 10 различных наименований.

На листе Покупатели создайте другую таблицу:

Код покупателя	Название организации	Адрес	Стоимость закупленного товара
1000	М-н Яблонька	ул. Кипарисовая, д. 22
1001	М-н Овощи	ул. Вишневая, д. 55

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

Таблица 1

Дата	Счет-фактуры	Код покупателя	Название организации	Код товара	Наименование товара	Ед. изм.	Цена	Кол-во	Стоимость закупленного товара

В столбцах без заливки вводятся данные. По введенному коду покупателя в столбце Название организации с помощью функции ПРОСМОТР восстанавливается название из листа Покупатели, а по введенному коду товара из листа Товары выбираются, также с помощью формул, необходимые данные по выбранному товару. Количество проставляется в зависимости от заказа покупателя. Подсчитывается Стоимость закупленного товара. Номера счет-фактур идут по порядку. По одной счет-фактуре может быть сделано несколько записей с разными товарами. На разных покупателей выписываются разные счет-фактуры введите в базу данных примерно 40 50 записей. По созданной базе данных сделайте сводную таблицу, позволяющую проводить анализ по каждому покупателю. Выведите на печать результат для одного какого-либо покупателя.

...