MICROSOFT OFFICE 2007:
ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL
| Учебно-методическое пособие для проведения лабораторных и практических занятий по курсу «Информатика» |
УФА 2010
Программа Microsoft Office Excel 2007 обладает эффективными средствами обработки числовой информации, представленной в виде электронных таблиц. Она позволяет выполнять математические, финансовые и статистические вычисления, выводить числовую информацию в виде графиков и диаграмм, связывать данные разных рабочих листов, преобразовывать данные в вид удобный для просмотра и анализа.
Пособие содержит цикл заданий и методических указаний к ним для освоения навыков работы с электронными таблицами.
Учебно-методическое пособие предназначено для подготовки бакалавров и инженеров очной и заочной формы обучения по всем специальностям и направлениям.
Составители: Кирлан Л.Д., доцент
Рецензент Мухамадеев И.Г., доцент
ã Уфимский государственный нефтяной
технический университет, 2010
Оглавление
Понятия электронной таблицы... 4
Задание № 1. ВВОД и редактирование ДАННЫХ.. 5
Упражнение 1. Ввод данных в ячейки. 5
Упражнение 2. Работа с функциями Дата и время. 6
ЗАДАНИЕ № 2. Формирование таблицы «Продажа товаров». 8
Основные понятия. 8
Методические указания к выполнению задания. 11
ЗАДАНИЕ № 3. Формирование таблицы «Ведомость по начислению зарплаты». 13
Основные понятия. 13
Методические указания к выполнению задания. 15
Задание №4. ПОСТРОЕНИЕ ДИАГРАММ - ГРАФИКОВ.. 17
Методические указания. 18
Методические указания построения графиков-диаграмм. 20
Задание № 5. Управление данными и их анализ. 21
Методические указания. 22
Сортировка данных. 23
Фильтрация данных. 24
Приемы работы с расширенным фильтром. 25
Задание № 6. Связывание данных разных рабочих листов 26
Методические указания. 26
ЗАДАНИЕ № 7. АНАЛИЗ ДАННЫХ.. 29
Вычисление промежуточных итогов. 29
Построение сводной таблицы.. 31
Работа с данными в сводной таблице. 33
Список РЕКОМЕНДУЕМОЙ литературы... 34
Понятия электронной таблицы
Документ приложения Excel называется рабочей книгой. Книга состоит из листов, которые представляют собой ячеек с числами, формулами, текстовой информацией и т.д. таблицы Excel похожи на базы данных, но предназначены не столько для хранения информации, сколько для проведения математических и статистических расчетов.
Для запуска программы необходимо:
· Щелкнуь на кнопке Пуск панели задач.
· В стартовом меню переместить указатель мыши на пункт Все программы.
· В открывшемся меню щелкнуть на команду Microsoft Excel 2010.
На экран монитора будет выведено главное окно программы, в котором отображается рабочая книга. Основные элементы рабочего окна Excel представлены на рис.1.
Рис.1. Главное окно программы Excel и его элементы
Данные ( текст, текстовые и числовые константы, табличные функции и формулы) можно ввести в любые ячейки. Для этого необходимо:
§ выделить ячейку,
§ набрать, используя клавиатуру, значение данного,
§ нажмите клавишу Enter или щелкните указателем мыши на другую ячейку.
Для редактирования выберите ячейку, данные которой вы хотели бы изменить, и щелкните в строке формул. Содержимое ячейки появится в строке формул. После редактирования нажмите клавишу Enter .
Чтобы очистить содержимое ячейки, выделите ее и нажмите клавишу Del.
Задание № 1. ВВОД и редактирование ДАННЫХ
Цель задания
1. Запуск и завершение работы с программой Excel.
2. Ввод и редактирование данных.
3. Использование Мастера функций при создании формулы.
Таблица 1
Таблица 2
В ячейки столбца А введите формулы и функции, назначения которых представлены в столбце В. Информацию в столбец В вводить необязательно.
Закройте рабочую книгу сохранив выполненное упражнение командой Office Сохранить. При этом выберите диск и имя файла для сохранения книги.
ЗАДАНИЕ № 2. Формирование таблицы «Продажа товаров»
Цель задания
1. Практика в создании таблиц.
2. Создание текстовой последовательности.
3. Копирование содержимого интервала ячеек методом Перетащить и оставить, а также с помощью Буфера обмена.
4. Форматирование данных в таблицах.
Основные понятия
1. Для заполнения интервала ячеек текстовой последовательностью необходимо выполнить следующие действия:
n выбрать ячейку, ввести в нее начальное значение последовательности, затем выделить ее;
n пометить маркером Автозаполнителя область соседних ячеек. Для этого необходимо отбуксировать маркер (маленький квадратик в нижнем правом углу активной ячейки) на область, которую вы хотите заполнить последовательностью;
n отпустить кнопку мыши. Программа Excel заполнит выбранный интервал ячеек соответствующими последовательностями.
2. В программе Excel предлагается несколько способов копирования данных рабочего листа. Можно копировать данные методом Перетащить и оставить, использовать буфер обмена или копировать данные в смежные ячейки с помощью Автозаполнителя. Копирование с помощью Автозаполнителя выполняется аналогично заполнению ячеек последовательностью данных. Для копирования методом Перетащить и оставить необходимо выполнить следующие действия:
n выделите интервал ячеек, которые вы хотите скопировать;
n поместите курсор мыши на границу выделения. При этом курсор превратится в стрелку;
n нажмите клавишу Ctrl и левую кнопку мыши и, удерживая их, перемещайте курсор мыши на новое место. При этом появится «бегущая» рамка, определяющая размер и положение копируемых данных;
n отпустите кнопку мыши и клавишу, копируемые данные займут выбранное положение в таблице.
Копирование данных с использованием Буфера обмена выполняют следующим образом:
n выделите интервал ячеек, содержимое которых необходимо скопировать;
n выберите вкладку Главная в группе Буфер обмена щелкните на кнопку Копировать или щелкните правой кнопкой мыши и из контекстного меню выберите команду Копировать. Вокруг выделенной области появится бегущая рамка, и в строке состояния отобразится сообщение о том, что вам необходимо выбрать новое место для копируемых данных;
n выделите ячейку, от которой вправо и вниз разместятся копии данных;
n выберите вкладку Главная à Буфер обмена à Вставить или щелкните правой кнопкой мыши и из контекстного меню выберите команду Вставить.
3. В программе Excel существуют различные возможности и средства для оформления таблиц. Приведем некоторые из них.
Ширину столбца устанавливают следующим образом:
n поместите указатель мыши на правую границу заголовка столбца, ширину которого вы хотите изменить. Когда вы правильно установите указатель мыши, он превратится в горизонтальную двунаправленную стрелку;
n для увеличения или уменьшения ширины столбца нажмите кнопку мыши и, удерживая ее, переместите указатель вправо или влево соответственно. Пунктирная линия будет показывать ширину столбца, если вы отпустите кнопку;
n отпустите кнопку мыши, если ширина столбца соответствует вашим требованиям.
Высоту строки устанавливают следующим образом:
n поместите указатель мыши на нижнюю границу заголовка той строки, высоту которой вы хотите изменить. Указатель мыши примет вид вертикальной двунаправленной стрелки;
n нажмите кнопку мыши и, не отпуская ее, переместите указатель вверх или вниз для уменьшения или увеличения высоты строки соответственно;
n отпустите кнопку мыши.
Средства программы Excel позволяют изменять внешний вид данных рабочего листа. Например, вы можете изменить вид выравнивания чисел и текста в ячейках. Можно переносить длинные тексты по строкам внутри ячейки, центрировать текст в столбцах или располагать текст вертикально в ячейке. Для этого выделите ячейку или интервал ячеек нажмите правую кнопку мыши и из контекстного меню выберите команду Формат Ячеек.. Для выравнивания заголовка относительно столбцов необходимо выделить ячейку, содержащую текст, и интервал соседних ячеек, в которые вы хотите центрировать текст. Дополнительные ячейки интервала должны быть пустыми. Из контекстного меню Формат Ячеек... в диалоговом окне Форматирование ячеек, выберите корешок Выравнивание и выберите опцию Объединение ячеек и щелкните на кнопке OK. Можно воспользоваться для этих целей кнопкой на вкладке Главная в группе Выравнивание ® Объединить и поместить в центре.
На третьем листе вашей рабочей книги сформируйте таблицу «Продажа товаров», макет которой приведен в табл. 3. При выполнении работы обязательно следуйте методическим указаниям к выполнению задания
Таблица 3
Исходными данными являются значения дохода от продажи определенного типа товара по месяцам для двух городов Киева и Москвы.
Необходимо подсчитать итоговые значения величин дохода по месяцам для каждого города.
Методические указания к выполнению задания
1. Запустите программу Excel .
2. Откройте рабочую книгу кнопкой Office à Открыть либо щелчком мыши на кнопке Открыть панели быстрого доступа. В появившемся диалоговом окне Открытие документа выберите диск, папку и файл, который необходимо открыть.
3. Щелкнув на ярлычок Лист3, вы откроете третий лист рабочей книги. Смените имя листа на новое Продажа. Для этого установите указатель мыши на ярлычок листа, щелкните правой кнопкой мыши. Из контекстного меню выберите команду Переименовать. Введите новое имя Продажа, предварительно удалив прежнее имя.
4. Введите в ячейки таблицы информацию, которая представлена в макете табл.3 следующим образом:
n текст Продажа товаров введите в ячейку A 1, затем измените размер шрифта, установив его равным 14, а начертание Ж. Выполните объединение и центрирование заголовка на интервал ячеек A 1: G 1;
n для обозначения названий колонок таблицы введите в ячейку B 3 текст Янв, выделите эту ячейку и Автозаполнителем создайте последовательность из названий месяцев первого полугодия;
n введите название товаров и текст Всего в ячейки с A 4 по A 7;
n скопируйте содержимое интервала ячеек A 3: G 7 в интервал начиная с ячейки A 12;
n введите значения продаж товара для Киева и Москвы;
n используя Мастер функций введите формулу =СУММ( B 4: B 6) в ячейку B 7. Автозаполнителем скопируйте формулу из ячейки B 7 на интервал C 7: G 7. Обратите внимание на то, как изменятся ссылки на ячейки с относительным типом адресации в формулах .
n скопируйте формулы из ячеек B 7: G 7 в интервал ячеек (начиная с ячейки B 16), используя кнопки группы Буфер обмена вкладки Главная;
n выделите интервалы ячеек A 3: G 7, A 12: G 16 и выберите для них любой тип рамки (вкладка Главная группа Шрифт кнопка Граница);
n для ячеек с названием месяцев выберите фон светло-зеленый, а для названий товара - светло-оранжевый;
n для чисел в таблице установите формат с разделением групп разрядов и округленных до целых значений (вкладка Главная группа Число либо использовать кнопки этой группы либо включить окно Формат ячеек à Число à Ч исловой à Разделитель групп разрядов à Число десятичных знаков установить 0).
5. Перезапишите свою рабочую книгу, щелкнув на кнопке Сохранить панели быстрого доступа, с тем же именем.
6. Все задания должны быть выполнены в одной рабочей книге (один файл) на разных рабочих листах.
ЗАДАНИЕ № 3. Формирование таблицы «Ведомость по начислению зарплаты»
Цель задания
1. Практика в создании таблиц.
2. Построение формул с использованием ссылок на ячейки с абсолютной адресацией.
3. Работа с числами, представленными в процентном формате.
4. Использование кнопки Автосумма для быстрого подсчета итогов.
5. Форматирование данных таблицы.
6. Вставка и удаление записей.
7. Пересчет таблицы с новыми значениями исходных данных.
Основные понятия
1. При копировании формул из одной ячейки в другую автоматически изменяются адреса ячеек (ссылки) в формулах. Такие ссылки называются с относительной адресацией. В большинстве случаев эти изменения удобны. Но иногда в копируемых формулах есть ссылки на ячейки, которые не должны изменяться (в них находятся константы). В этом случае они должны быть с абсолютной адресацией, т.е. не подлежащие изменению при копировании. Для объявления ссылок с абсолютной адресацией используют символ «$».
2. Если при вводе после числа был указан знак процента , то автоматически к ячейке будет применен формат процентный.
3. Для того чтобы добавить в таблицу новую запись, необходимо вставить строку (лучше после первой или перед последней записью в списке). Затем ввести исходные данные для новой записи и скопировать, если это необходимо, расчетные формулы. Для удаления записи из списка необходимо удалить строку, содержащую удаляемую запись (интервал ячеек). После выполнения этих операций восстанавливают последовательность порядковых номеров записей.
На следующем листе вашей рабочей книги сформируйте таблицу начисления зарплаты. Исходные данные и расчетные формулы для формирования таблицы представлены в макете табл. 4. Измените имя рабочего листа на Ведомость. Если список свободных листов исчерпан, необходимо добавит новый лист в книгу. Для этого установите указатель мыши на корешок любого рабочего листа и нажмите правую кнопку мыши выберите команду Вставить ® Лист.
Таблица 4
Исходные данные :
n величина оклада каждого сотрудника (окл);
n ставка уральского коэффициента (ур._коэф.) и подоходного налога (под._нал.).
Расчетные формулы :
n начисление районных на заданную сумму оклада (район.=окл.* ур._коэф).;
n налог подсчитаем на всю сумму дохода: ( налог=(окл.+район.)* под._нал);
n значение зарплаты (зарпл.=окл.+район.-налог);
n значения итогов для содержимого столбцов таблицы подсчитываем, используя копку на вкладке Главная в группе Редактирование å Сумма.
Методические указания к выполнению задания
1. Запустите программу Excel и откройте свою рабочую книгу.
2. Щелкните на ярлычок свободного листа (вставьте новый лист в книгу),. Смените имя листа на новое имя Ведомость.
3. Введите в ячейки исходные данные и расчетные формулы, как это представлено в макете табл. 4. Для этого выполните следующее:
n текст «Ведомость по начислению зарплаты» введите в ячейку A 1. Выполните форматирование ее содержимого (измените размер шрифта и выполните объединение ячеек и центрирование по ширине таблицы);
n тексты «Ставка уральского коэффициента» и «Ставка подоходного налога» введите соответственно в ячейки A 2 и A 3. Значения ставок в процентах - в ячейки E2 и E3;
n оформите шапку таблицы. Для представления надписей в ячейке в несколько строк воспользуйтесь вкладкой Главное в группе Выравнивание используйте кнопку Переносить по словам или из контекстного меню выберите команду Формат à Ячейки..., а затем на вкладке Выравнивание установите необходимые опции (текст будет расположен в несколько строк в одной ячейке в том случае, если ширина столбца недостаточная для полного его отображения иначе формат установится а изменений не произойдет);
n порядковые номера для записей введите, используя один из способов ввода последовательности в смежные ячейки. Для этого:
n введите 1 в ячейку A5;
n сделайте ее активной;
n курсор мыши установите на маркер автозаполнителя и при нажатой клавиши Ctrl создайте последовательность до цифры 5.
n в ячейки B 5: B 9 введите фамилии, в ячейки C 5: C 9 - значения окладов;
n формулу начисления районных (= C 5*$ E $2) введите в ячейку D 5 (для первого сотрудника в списке). Ссылка C 5 указывает на ячейку со значением оклада Авдеева, абсолютная ссылка $ E $2 - на ячейку со значением ставки уральского коэффициента.. После копирования формулы из ячейки D 5 на интервал D 6: D 9 номера строк в ссылке C 5 изменятся соответственно на C 6 для формулы в ячейке D 6, на C 7 - для D 7 и т.д. Ссылка же $ E $2 останется неизменной во всех скопированных формулах (ставка районных и налога для всех сотрудников одинакова).
n формулу для вычисления налога (=( C 5+ D 5)*$ E $3) введите в ячейку E 5. При копировании формулы из ячейки E 5 на интервал E 6: E 9 ссылки C 5 и D 5 изменятся на C 6 и D 6 и т.д., а $ E $3 также останется неизменной;
n формулу для подсчета зарплаты (= C 5+ D 5- E 5) введите в ячейку F 5, а затем скопируйте на интервал F 6: F 9. Все ссылки на ячейки в скопированных формулах изменятся автоматически;
n подсчет суммы значений зарплат выполните, используя встроенную функцию СУММ( ). Для этого активизируйте ячейку F 10 и щелкните на кнопке å Автосумма, которая находится на вкладке Формулы в группе Библиотека функций. В ячейке F 10 и в строке формул отобразится формула =СУММ( F 5: F 9). Нажмите клавишу Enter для завершения ввода. Скопируйте формулу из ячейки F 10 на интервал C 10: E 10. В строке Итого в соответствующих ячейках будут отображены суммы значений содержимого столбцов таблицы. Этот пункт можно выполнить используя кнопку Сумма вкладки Главная.
4. Выполните форматирование данных таблицы. Для этого сначала выделите ячейку или интервал ячеек, а затем используйте на вкладке Главная кнопки в группах Шрифт, Выравнивание, Число, Ячейки или из контекстного меню выберите команду Формат à Ячейки... Выбирайте соответствующие вкладки и опции диалогового окна Форматирование ячеек. Выделяйте интервалы ячеек и подбирайте для них подходящий вид рамок.
5. Перезапишите рабочую книгу на диске.
6. Выполните дополнительные задания
· Вставьте одну строку перед девятой. На вкладке Главная в группе Ячейки щелкните стрелку рядом с командой Вставка, а затем выберите в списке команду Вставить строки на лист.
· Введите в ячейки пустой строки свою фамилию и значение оклада (любого в рамках разумного). Скопируйте формулы для расчета районных, налога и зарплаты для добавленной записи.
· Удалите из таблицы сведения о Петровой. Для этого выделите шестую строку. На вкладке Главная в группе Ячейки щелкните стрелку рядом с командой Удалить, а затем выберите в списке команду Удалить строки с листа.
7. Перезапишите рабочую книгу на диске.
8. Измените ставку уральского коэффициента на величину 30%. Обратите внимание на автоматический пересчет формул в ячейках.
ПРИМЕЧАНИЕ. Если какое-то действие вы выполнили неверно, сразу же откажитесь от него щелчком на кнопке Отменить 
на панели быстрого доступа (раскрыв список можно отменить несколько последних действий выделив их в списке). Чтобы повторно выполнить отмененное действие, нажмите кнопку Вернуть 
.
Задание №4. ПОСТРОЕНИЕ ДИАГРАММ - ГРАФИКОВ
Цель задания
1. Изучить приемы построения диаграмм-графиков, иллюстрирующих данные листа Excel 2007.
2. Использование функции СЛЧИС() (генерации случайного числа) для быстрого заполнения ячеек данными.
Запустите Excel и откройте свою рабочую книгу. Вставьте новый лист и назовите его Акции. Затем выполните задание.
1. Сформируйте таблицу «Курс акций и диаграммы». Исходные данные и расчетные формулы представлены в макете табл.5.
Методические указания
1. Введите текстовую информацию в ячейки. Затем в интервал ячеек A 6: A 18 введите последовательность значений из дат с шагом в 7 дней. Для этого:
· в ячейку A6 запишите начальную дату 02.01.10 а в ячейку А7 9.1.10;
· выделите эти ячейки с датами и используя мышь и маркер автозаполнителя создайте последовательность из дат с шагом 7 дней на первый квартал 2010 года.
· 2. В ячейку B6 введите расчетную формулу, определяющую стоимость акций на 02.01.2010 компании "Рога и копыта". Величина стоимости определяется как произведение функции СЛЧИС() на величину 1000, т.е. =1000*СЛЧИС(). Эту формулу скопируйте в ячейки для всех компаний и для всего рассматриваемого интервала времени с помощью мыши и маркера Автозаполнителя (можно использовать другой способ копирования). Этот прием используется для того, чтобы не вводить большое количество исходных данных. Имейте в виду, что при нажатии функциональной клавиши F 9 или выполнении любой команды ввода, значения стоимости акций компаний пересчитываются (генерируется новое значение числа при выполнении функции СЛЧИС()).
· 3. В ячейки B2,B3 и B4, используя Мастер функций, введите формулы, рассчитывающие высший, низший и средний курс акций: в B2 = МАКС(B6:B18), в B3 = МИН(B6:B18), в B4 = СРЗНАЧ(B6:B18).
· Формулы из интервала ячеек B 2: B 4 скопируйте на интервал C 2: F 4 (для всех компаний).
· Выполните операции форматирования данных. Числовой массив значений округлите до целых командой из контекстного меню Формат à Ячейки... à Число à Числовой формат à 0 знаков à OK.
·
Таблица 5
5. Задание 4а. Постройте график, отображающий динамику изменения курса акций компании Инко относительно даты и разместите его на текущем листе.
Задание № 5. Управление данными и их анализ
Цель работы
1. Вывод на панель быстрого доступа кнопки реализующую возможность ввода данных в режиме Форма.
2. Создание и редактирование списка с помощью Формы.
3. Сортировка данных таблицы.
4. Фильтрация данных таблицы.
Для работы в качестве базы данных воспользуемся таблицей «Ведомость по начислению зарплаты", которая находится на листе с именем Ведомость.
Методические указания
1. Запустите Excel и откройте свою рабочую книгу.
2. Щелкните на ярлычок листа с именем «Ведомость».
3. Выделите и скопируйте часть данных таблицы (без итоговых значений) на новый вставленный рабочий лист, который назовите База. Для этого выполните следующее:
· выделите содержимое ячеек A 1: F 9, командой Главная à Буфер обмена à щелкните на кнопку Копировать;
· щелкните на ярлычок База, затем на ячейку A1 этого листа;
· по команде командой Главная à Буфер обмена à щелкните на кнопку à Вставить (данные таблицы появятся на листе);
Рабочим листом занятия будет лист с именем База. Дополним Ведомость по начислению зарплаты новыми записями, используя Форму.
Кнопки Форма нет на ленте, однако эту функцию можно использовать в Office Excel 2007, добавив кнопку Форма 
на панель быстрого доступа Для этого:
· щелкните стрелку на панели быстрого доступа и выберите элемент Добавить команды ;
· в поле Выбрать команды из щелкните элемент Все команды;
· в списке выберите кнопку Форма и щелкните элемент Добавить. На панели быстрого доступа появится кнопка Форма.
Дополните базу, содержащую пять записей еще пятью записями. Для этого:
· выделите базу (интервал ячеек A4:F9;
· выберите щелкните на кнопку Форма панели быстрого доступа. На экране появится форма данных листа База с именами полей и текстовыми полями справа от них. В форме отображено содержимое первой записи;
· просмотрите все имеющиеся записи, щелкая на кнопку Далее;
· после просмотра последней записи щелкните на кнопку Добавить;
В пустые текстовые поля (каждый раз щелкая на них соответственно) введите данные для новых записей:
· 6 Егоров 800;
· 7 Понятовский 645;
· 8 Алмазова 765;
· 9 Тараторкин 967;
· 10 Кузьмин 589;
· закройте форму щелкнув на кнопке Закрыть.
Обратите внимание, в таблице для вновь введенных записей формулы расчета районных, налога и зарплаты копируются автоматически. Если есть необходимость, выполните форматирование новых записей.
4. Сохраните рабочую книгу на диске.
Сортировка данных
Результаты сортировки после выполнения каждого пункта задания копируйте в нижележащие пустые строки, отстоящие от базы минимум на 3 строки. При этом над результатами копирования записывайте постановку той задачи, которую выполнили.
Фильтрация данных
Результаты фильтрации также необходимо копировать в нижележащие свободные области, надписывая содержание выполненной задач.
Задание № 6. Связывание данных разных рабочих листов
Цель работы
1. Навыки в построении формул, использующих данные различных рабочих листов (книг).
2. Работа с несколькими открытыми окнами.
Для работы в качестве исходного материала воспользуемся таблицей "Ведомость по начислению зарплаты".
Запустите Excel и откройте свою рабочую книгу.
Методические указания
1. Вставьте четыре новых рабочих листа. Назовите листы соответственно Январь, Февраль, Март и Квартал_1.
2. Откройте рабочий лист Ведомость.
3. Скопируйте полностью таблицу «Ведомость по начислению зарплаты» на листы с названиями месяцев. Для этого:
· выделите на листе Ведомость интервал ячеек с A1 до конца таблицы;
· выберите команду Главная à Копировать;
· щелкните на корешок Январь, затем на ячейку A1 этого листа;
· выберите команду Главная à Вставить. На Листе появится таблица.
3. Аналогично выполните операцию копирования ведомости на листы Февраль и Март. При необходимости на новых листах выполните форматирование данных.
Рабочими листами занятия будут листы с именами Январь, Февраль, Март и Квартал_1.
4. Сформируйте на рабочем листе Квартал_1 Итоговую ведомость за 1 квартале по зарплате, макет которой представлен в табл.6.
Таблица 6.
 |
5. Итоговые значения Окладов, Районных и Подоходных налогов необходимо для каждого месяца считывать из соответствующих ячеек таблиц, находящихся на листах Январь, Февраль и Март соответственно. Для этого необходимо организовать связь данных листа Квартал_1 с данными, находящимися на соответствующих листах. Для этого:
· откройте лист Январь. Выделите ячейку C10. Скопируйте содержимое ячейки в буфер (команда Главная à Копировать);
· откройте лист Квартал_1. Щелкните на ячейку B3;
· выберите вкладку Главная в группе Буфер обмена раскрыть список у кнопки Вставить выбрать Специальная вставка;
· в диалоговом окне Специальной вставки щелкните на кнопку Вставить связь. В ячейке B3 появится результат, вычисленный по формуле связи =Январь!$C$10. Аналогично введите формулы связи в ячейки C 3 и E3 для суммарного значений районных и подоходного налога за Январь.
· Расчетные формулы Всего начислено и Зарплата простые ( =B3+C3 и =D3-E3 ) вводите в ячейки D3 и F3.
6. Повторите описанные действия ввода формул связи для данных с листов Февраль и Март.
7. Скопируйте расчетные формулы для Всего начислено и Зарплата для расчетов за Февраль и Март. Полученные результаты по месяцам одинаковы, так как использованы одинаковые данные.
8. Используя кнопку Автосумма панели инструментов, подведите итоги за квартал по столбцам.
9. Выполните операции форматирования результатов.
10. Проверьте правильность выполненных действий. Для этого откройте новое окно выбрав вкладку Вид из группы Окно щелкните на кнопку Новое окно. В имени файла появится двоеточие и цифра два (Имя_файла:2). Затем расположите окна один над другим щелкните в группе Окно кнопку Упорядочить все. В окне Расположение окон Расположить à Сверху вниз. На экране отобразятся два одинаковых окна. В верхнем окне щелкните на ярлычок Январь, а в нижнем - Квартал_1.
11. Измените значение оклада за Январь Авдееву на порядок. Обратите внимание на изменения значений за месяц Январь листа Квартал_1.
12. В верхнем окне щелкните на ярлычок листа Февраль. Измените на порядок оклад за февраль Марушкину. Обратите внимание на пересчет значений в итоговой таблице за Февраль.
13. В верхнем окне отобразите ведомость за Март месяц. Измените себе оклад на порядок. Следите за пересчетом расчетных формул в нижнем окне.
14. Закройте текущее окно и разверните книги на весь экран. Сохраните книгу.
ЗАДАНИЕ № 7. АНАЛИЗ ДАННЫХ
Таблица 7
Построение сводной таблицы
Сводные таблицы - это вспомогательные таблицы, с помощью которых данные анализируемой таблицы можно выборочно представить в виде, позволяющем наилучшим образом отобразить зависимости между ними.
В качестве примера будем использовать данные табл. 7, которые находятся на листе с именем Итоги.
Пользуясь сводной таблицей, можно создавать и анализировать различные группы данных. С помощью такой таблицы можно упорядочить значения оборота по фамилиям продавцов, а марки автомобилей - по годам выпуска и по фамилиям продавцов, таблицу также удобно использовать для определения дневного оборота для каждого продавца.
Вид представления данных в сводной таблице можно корректировать путем показа/скрытия отдельных групп данных и изменения ориентации строк и столбцов - это позволяет достичь большей наглядности при анализе данных.
Создание сводной таблицы. Создание и обработка сводных таблиц осуществляется с помощью программы Мастер сводных таблиц. Для этого выделите на листе Итоги интервал ячеек A 3: E 13.
n Запустите программу Мастер сводных таблиц выбрав вкладку Вставка в группе щелкните на кнопку Сводная таблица. На экране появится первое окно Создание сводной таблицы. В этом окне следует выбрать источник данных для сводной таблицы, если они ранее не были выделены. Следует выбрать куда поместить отчет сводной таблицы (укажите На новый лист).
n В следующем окне следует определить структуру будущей сводной таблицы. В центре диалогового окна представлена так называемая область сведения, которая подразделяется на области полей страниц,строк, столбцов, и область данных. Найденные в источнике данных поля представлены справа. Каждое поле данных с помощью операции перетаскивания можно поместить в область сведения (сводную таблицу). Поместим поле Продавец в область полей страниц. Таким образом, мы задали представление данных для каждого продавца на «отдельной странице». Имена продавцов будут содержаться в поле списка. Для отображения на экране данных для конкретного продавца следует выбрать фамилию продавца из списка.
n Поместите поля Дата и Марка в область строк, а поле Год выпуска - в область столбцов, поле Оборот в область данных. Таким образом, на экране мы имеем сводную таблицу (смотри рис.4). Назовите этот лист Меню_сводн._табл.
Сохраните файл на диске.
Рис 4. Созданная сводная таблица
Для группировки отдельных элементов в качестве первого критерия используется дата продажи (т.е. данные о продажах за один день объединены в группу, и вычислена сумма оборота за день). Промежуточные итоги представлены под отдельными значениями группы.
Список РЕКОМЕНДУЕМОЙ литературы
1. Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. СПб.:Питер, 2008.
2. Стоцкий Ю., Васильев А., Телина И. Office 2007. Самоучитель. СПб.:Питер, 2008.
3. Соболь Б.В. и др. Информатика: учебник. –Ростов н/Д: Феникс, 2007.
4. Информатика: Базовый курс / С.В. Симонович и др. – СПб.: Питер, 2005.
5. Акулов О.А., Медведев Н.В. Информатика: Базовый курс. Учебник для студентов вузов. Москва: Омега-Л, 2006.
6. Курицкий Б. Поиск оптимальных решений средствами Excel 7.0. -СПб.: BHV, 1997.
7. Кирлан Л. и др. Microsoft Office 97: Электронные таблицы Excel : Учебно-методическое .пособие.- Уфа: УГНТУ, - 2000.
MICROSOFT OFFICE 2007:
ЭЛЕКТРОННЫЕ ТАБЛИЦЫ EXCEL
| Учебно-методическое пособие для проведения лабораторных и практических занятий по курсу «Информатика» |
УФА 2010
Программа Microsoft Office Excel 2007 обладает эффективными средствами обработки числовой информации, представленной в виде электронных таблиц. Она позволяет выполнять математические, финансовые и статистические вычисления, выводить числовую информацию в виде графиков и диаграмм, связывать данные разных рабочих листов, преобразовывать данные в вид удобный для просмотра и анализа.
Пособие содержит цикл заданий и методических указаний к ним для освоения навыков работы с электронными таблицами.
Учебно-методическое пособие предназначено для подготовки бакалавров и инженеров очной и заочной формы обучения по всем специальностям и направлениям.
Составители: Кирлан Л.Д., доцент
Рецензент Мухамадеев И.Г., доцент
ã Уфимский государственный нефтяной
технический университет, 2010
Оглавление
Понятия электронной таблицы... 4
Задание № 1. ВВОД и редактирование ДАННЫХ.. 5
Упражнение 1. Ввод данных в ячейки. 5
Упражнение 2. Работа с функциями Дата и время. 6
ЗАДАНИЕ № 2. Формирование таблицы «Продажа товаров». 8
Основные понятия. 8
Методические указания к выполнению задания. 11
ЗАДАНИЕ № 3. Формирование таблицы «Ведомость по начислению зарплаты». 13
Основные понятия. 13
Методические указания к выполнению задания. 15
Задание №4. ПОСТРОЕНИЕ ДИАГРАММ - ГРАФИКОВ.. 17
Методические указания. 18
Методические указания построения графиков-диаграмм. 20
Задание № 5. Управление данными и их анализ. 21
Методические указания. 22
Сортировка данных. 23
Фильтрация данных. 24
Приемы работы с расширенным фильтром. 25
Задание № 6. Связывание данных разных рабочих листов 26
Методические указания. 26
ЗАДАНИЕ № 7. АНАЛИЗ ДАННЫХ.. 29
Вычисление промежуточных итогов. 29
Построение сводной таблицы.. 31
Работа с данными в сводной таблице. 33
Список РЕКОМЕНДУЕМОЙ литературы... 34
Понятия электронной таблицы
Документ приложения Excel называется рабочей книгой. Книга состоит из листов, которые представляют собой ячеек с числами, формулами, текстовой информацией и т.д. таблицы Excel похожи на базы данных, но предназначены не столько для хранения информации, сколько для проведения математических и статистических расчетов.
Для запуска программы необходимо:
· Щелкнуь на кнопке Пуск панели задач.
· В стартовом меню переместить указатель мыши на пункт Все программы.
· В открывшемся меню щелкнуть на команду Microsoft Excel 2010.
На экран монитора будет выведено главное окно программы, в котором отображается рабочая книга. Основные элементы рабочего окна Excel представлены на рис.1.
Рис.1. Главное окно программы Excel и его элементы
Данные ( текст, текстовые и числовые константы, табличные функции и формулы) можно ввести в любые ячейки. Для этого необходимо:
§ выделить ячейку,
§ набрать, используя клавиатуру, значение данного,
§ нажмите клавишу Enter или щелкните указателем мыши на другую ячейку.
Для редактирования выберите ячейку, данные которой вы хотели бы изменить, и щелкните в строке формул. Содержимое ячейки появится в строке формул. После редактирования нажмите клавишу Enter .
Чтобы очистить содержимое ячейки, выделите ее и нажмите клавишу Del.
Задание № 1. ВВОД и редактирование ДАННЫХ
Цель задания
1. Запуск и завершение работы с программой Excel.
2. Ввод и редактирование данных.
3. Использование Мастера функций при создании формулы.
Упражнение 1. Ввод данных в ячейки
| Адрес ячейки | Вводимое значение | Адрес ячейки | Вводимое значение |
| A1 | =46,84 | B7 | =A3*A1% |
| B1 | =A1 | B8 | =A1>A2 |
| A2 | 54,35 | B9 | =A1<A2 |
| B2 | =A1+A2 | A10 | =0,48*532/421-84,6 |
| A3 | 12 | B10 | =ЕСЛИ(A1>A2;A1;A2) |
| B3 | =B2/A1 | B11 | =ЕСЛИ(A1<A2;A1;A2) |
| A4 | 64.25 | A12 | =SIN(A1+A1) |
| B4 | =A3+A4 | B12 | =СУММ(B5:B7) |
| A5 | =”Пример” | A13 | =ОКРУГЛ(A12;3) |
| B5 | =25%*80 | B13 | =МАКС(B5:B12) |
| A6 | Пример | A14 | =МИН(A10;A1:A3) |
| B6 | =16*A1% | B14 | =СРЗНАЧ(B1:B3;B10:B13) |
| B15 | =ПИ() |
Введите информацию в соответствующие ячейки Листа 1. Сверьте полученные результаты с содержимым в таблице 1. Если значения различаются, найдите и исправьте ошибки.
Таблица 1
Дата: 2018-11-18, просмотров: 550.
