36826

Получить навыки работы с электронной таблицей Microsoft Excel

Лабораторная работа

Информатика, кибернетика и программирование

Откройте меню настройки панелей управления Вид Панели инструментов и убедитесь в том что включено отображение только двух панелей: Стандартная и Форматирование. Чтобы настроить масштаб отображения войдите в меню Вид Масштаб. Войдите в меню Сервис Параметры. Для этого достаточно воспользоваться командой меню Правка Отменить.

Русский

2013-09-23

170 KB

7 чел.

Лабораторная работа №2

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

Краткие теоретические сведения.

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

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего Рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами от 1 до 65 536.

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

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

  1.  Запустите Excel, выбрав Пуск Программы Microsoft Excel. На экране появится чистый Лист1 новой Рабочей книги.
  2.  Откройте меню настройки панелей управления (Вид Панели инструментов) и убедитесь в том, что включено отображение только двух панелей: Стандартная и Форматирование.
  3.  Чтобы настроить масштаб отображения, войдите в меню Вид Масштаб. В появившемся диалоговом окне установите флажок напротив желаемого масштаба. Если подходящего масштаба нет в списке, выберите необходимое значение в полосе прокрутки Произвольный. Можно также использовать раскрывающийся список Масштаб на панели инструментов Стандартная. Если желаемого масштаба нет в списке, введите нужное значение непосредственно в поле списка и нажмите клавишу Enter.
  4.  Войдите в меню Сервис Параметры.
  5.  Выберите вкладку Вид и измените параметры, влияющие на изображение окна документа, следующим образом. Установите флажки в перечисленных ниже окошечках, а во всех остальных - сбросьте.

Отображать:
          строку формул
          строку состояния
Примечания:
          только индикатор
Объекты:
          отображать
Параметры окна:
          сетка
          нулевые значения
          заголовки строк и столбцов
          символы структуры
          горизонтальная полоса прокрутки
          вертикальная полоса прокрутки
          ярлычки листов
Цвет: Авто

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

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

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

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

  1.  Выберите вкладку Общие и измените параметры следующим образом. Установите флажки в перечисленных ниже окошечках, а во всех остальных - сбросьте.

Параметры:
   защита от макровирусов
   список ранее открывавшихся файлов содержит элементов, не более: 4
Листов в новой книге: 3
Стандартный шрифт: Arial Cyr
Размер: 10

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

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

ABS( Х ) - вычисление модуля числа X
ЕХР( Х ) - число "е", возведенное в степень Х (е=2,7182..)
ЦЕЛОЕ( Х ) - целая часть числа X, например: ЦЕЛОЕ(2,5)=2; ЦЕЛОЕ(-5,6)=-6
ОКРУГЛ( X; К ) - округление Х до К знаков после запятой (К - целое число),
          например: ОКРУГЛ(2,86;1)=2.9,
          ОКРУГЛ(156,2;-1)=160
КОРЕНЬ(X) - извлечение корня из числа X. Например: КОРЕНЬ(25)=5
ПИ() - число ПИ = 3.1415…

ЗАДАНИЕ 1

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

  1.  Выберите на Листе1 ячейку A2, для этого щелкните левой кнопкой мыши на той ячейке, которая располагается на пересечении ряда номер 2 и колонки A. Введите в активной ячейке следующее: "Лабораторная работа по MS Excel студента (студентки) группы номер_вашей_группы ваше_имя_и_фамилия". Нажмите Enter.
  2.  Отредактируйте только что введенное предложение, добавив в его конец дату проведения занятия. Для этого наведите курсор на ячейку A2, сделайте ее активной. Далее дважды щелкните по ячейке (или нажмите клавишу F2). В тексте после вашей фамилии укажите текущую дату. Нажмите Enter.
  3.  Введите в ячейку B3 число, например 123456. Нажмите Enter. Отредактируйте данное число, удалив две последние цифры, но не выходите из режима редактирования (не нажимайте Enter). Восстановите предыдущее значение числа. Для этого, удерживая нажатой клавишу Ctrl, нажмите Z.
  4.  Введите в ячейку C4 число 31415. Нажмите Enter. Замените значение числа в ячейке на 62830. Снова нажмите Enter. Вернемся к предыдущему значению числа. Для этого достаточно воспользоваться командой меню Правка Отменить.
  5.  Очистите ячейку С4, для этого сделайте ее активной и нажмите клавишу Del.
  6.  Введите в ячейки G3 - G7 числа от 1 до 5. Удалите за один раз значения в первых трех ячейках. Для этого выделите блок, содержащий эти ячейки: нажмите левой кнопкой мыши на первой из них и ведите курсор, не отпуская кнопки мыши, до третьей. Теперь все операции, которые вы будете проводить, будут одинаково применены ко всем ячейкам выбранного блока. Воспользуйтесь командой меню Правка Очистить Содержимое.
  7.  Отмените предыдущее действие с помощью команды Правка Отменить. Выделите вышеописанным методом ячейки G4 - G6. Скопируйте содержимое выделенного блока в буфер обмена, для этого воспользуйтесь меню Правка Копировать.
  8.  Вставьте скопированные данные в столбец H. Для этого сделайте активной ячейку, с которой начнется вставка (например H5), и используйте меню Правка Вставить.
  9.  Отмените предыдущую операцию. Скопируйте в буфер обмена данные из блока ячеек G5 - G7, выделив их и выбрав в меню, вызываемом нажатием правой клавиши мыши, команду Копировать. Теперь выделите блок ячеек B8 - F10. Нажмите правую клавишу мыши и в появившемся меню выберите команду Вставить. Данные распределятся по всему блоку ячеек.
  10.  Выделите диапазон ячеек G4 - G7. Переместите данные из этих ячеек в ячейки I4 - I7. Для этого выберите в меню, вызываемом нажатием правой кнопки мыши, команду Вырезать. Сделайте активной ячейку I4, щелкните на ней правой кнопкой мыши и выберите команду Вставить. Выделите ячейки I4 - I7, наведите курсор на рамку выделения и, удерживая левую клавишу мыши, переместите рамку в другое место листа. Это другой способ перемещения данных.
  11.  Заполните ряд от B18 до H18 числом 65536. Для этого введите это число в ячейку B18, а затем, ухватившись за черную точку в правом нижнем углу рамки, выделяющей активную ячейку, растянем выделение до H18.
  12.   Перейдите на Лист2 Рабочей книги Excel. Для этого щелкните левой кнопкой мыши по ярлычку с надписью Лист2. Заполните столбец от В1 до В10 числовым рядом. Для этого задайте первый член ряда в ячейке В1 равным 1. В ячейке В2 укажите член ряда, указывающий его шаг, например, 2. Теперь выделите обе заполненные ячейки и за черную точку в правом нижнем углу растяните рамку выделения до ячейки В10. Проделайте аналогичную операцию со столбцом С1-С10 для названий месяцев (в этом случае достаточно указать только начальное значение), начиная с месяца вашего рождения.
  13.  Очистите содержимое Листа2. Для этого подведите указатель мыши к кнопке, лежащей на пересечении названий столбцов и строк. Затем нажмите правую кнопку мыши и выберите в появившемся меню команду Очистить содержимое.
  14.  Постройте геометрическую прогрессию, начиная с ячейки С3. Для этого введите в нее число 12. Сделайте ячейку активной. Войдите в меню Правка Заполнить Прогрессия. Выберите Расположение - По строкам; Тип - Геометрическая; в поле Шаг введите значение 1,5; а Предельное значение - 100. Нажмите ОК.
  15.  Постройте прогрессию для дат, начиная с ячейки D4. Введите начальное значение 01/01/98, укажите Расположение - По столбцам, Тип - Дата, Единицы - Рабочий день, Шаг - 2, Предельное значение - 15/01/98.
  16.  Перейдите к Листу3. Составьте маленькую таблицу со сведениями о себе. Расположите по горизонтали, начиная с ячейки В3, следующие названия граф: Фамилия, Имя, Отчество, Год рождения, Месяц рождения, Число, Хобби. В строке, начиная с В4 под названиями граф впишите соответствующие значения. Выровняйте ячейки по ширине. Для этого выделите обе заполненные строки, войдите в меню Формат Столбец Автоподбор ширины. Если у вас после этого появились свободные столбцы, вы можете избавиться от них, перетащив соседние столбцы на пустое место либо выделив пустой столбец и выбрав меню Формат Столбец Скрыть.
  17.  Войдите в меню Формат Ячейки. В появившемся диалоговом окне выберите вкладку Граница, а в ней установите внешние и внутренние границы.
  18.  Добавьте новый лист к Рабочей книге Excel. Для этого щелкните правой кнопкой мыши на корешке с названием последнего листа. В появившемся меню выберите Добавить, а в диалоговом окне Вставка выберите Лист. Нажмите ОК. Переместите его в начало рабочей книги, перетащив левой клавишей мыши. Переименуйте его заголовок на Лабораторная работа, для этого щелкните по его корешку правой кнопкой мыши и в появившемся меню выберите Переименовать.
  19.  Вставьте в добавленный лист рисунок. Для этого выберите Вставка Рисунок Из файла. В появившемся окне выберите файл  и нажмите Вставить. Два раза щелкните по рисунку левой клавишей мыши. В диалоговом окне Формат рисунка выберите вкладку Размер и установите масштаб по высоте и по ширине 60%. Нажмите ОК.
  20.  Переместите таблицу с вашими данными на лист с названием Лабораторная работа с помощью меню Правка Вырезать в исходном листе и Правка Вставить в листе Лабораторная работа. Снова выровняйте ячейки таблицы по ширине, как это было показано выше.
  21.  Заполните столбец, начиная с ячейки В9, числами от 1 до 10. Вставьте в С9 сумму значений из ячеек B9 и B10, для чего запишите в этой ячейке следующее: =В9+В10.
  22.  Вставьте в С10 разность значений из ячеек В10 и В11, введя =В11-В10.
  23.  Вставьте в ячейку С11 косинус суммы значений из ячеек В14 и В15. Для этого сделайте активной ячейку С11, выберите Вставка Функция, а в окне мастера функций выберите категорию Математические и функцию COS( ). Нажмите ОК. В появившемся окне в поле Число введите В14+В15.
  24.  Аналогичным образом вставьте в С12 синус от значения в ячейке В9*/2 (вводите число  как ПИ()).
  25.  . Постройте график функции. Для начала, создайте новый лист Excel и назовите его Построение графика функции.
  26.  В ячейки столбца А, начиная с первой, введите значения констант, соответствующих Вашему варианту. В следующую ячейку этого столбца введите численное значение шага. В ячейку В1 - начало отсчета.
  27.  В ячейку В2 введите формулу изменения шага в виде = В1 + "ячейка, в которую введено значение шага в виде, А$6, например". В ячейку С1 введите формулу вычисления функции, в виде = А$1*(A$2+B1), например (заметьте, что все значения, кроме В1, пишутся через $, поскольку В1 - относительный адрес, а А$1 и A$2 - абсолютные адреса).
  28.  Копируйте ячейку В2 в столбец В от В3 до конца вашего диапазона (в зависимости от значения, указанного в таблице), а С1 - в столбец С от С2 до конца интервала соответственно.
  29.  Выберите меню Вставить Диаграмма. В появившемся окне Мастера диаграмм - Шаг 1 из 4 выберите тип График и вид - График с маркерами, отмечающими точки данных. Нажмите кнопку Далее.
  30.  В окне Шаг 2 из 4 выберите вкладку Диапазон данных. Установите флажок в окошечке Ряды в: столбцах. Нажмите Далее.
  31.  В окне Шаг 3 из 4 в закладке Заголовки в поле Название диаграммы впишите номер вашего варианта. Нажмите Далее.
  32.  В окне Шаг 4 из 4 выберите Поместить диаграмму на отдельном листе и нажмите Готово.
  33.  Выделите получившийся график функции, скопируйте его в буфер обмена (Правка Копировать).
  34.  Не закрывая Excel, запустите программу MS Word. Вставьте в документ Word данные из буфера обмена (Правка Вставить).
  35.  Сохраните вашу работу.

Часть 1. Формирование таблиц.

Задание:

1. Сформировать шапку таблицы:

Ведомость отгрузки товаров за (месяц) 2006 год

№ п/п

Наименование товара

Получатель

Дата отгрузки

Количество

Цена

Сумма

Заголовок таблицы зависит от вводимого наименования.

Последовательность действий:

  1.  Создать новую рабочую книгу с помощью команды меню Файл/Создать, выбрать шаблон Книга.
    1.  Сохранить рабочую книгу с помощью команды Файл/Сохранить, ввести имя файла, расширение файла .XLS (присваивается автоматически).
    2.  Формирование заголовка таблицы:
  •  Установить курсор в ячейку А1;
  •  Ввести заголовок таблицы;
  •  Выделить ячейки А1:G1 (ширина таблицы);
  •  Выполнить команду меню Формат/Ячейки вкладка Выравнивание указать выравнивание по горизонтали и вертикали – По центру, отображение – Объединение ячеек (или пиктограмма на панели Форматирование).
    1.  Формирование шапки таблицы:
  •  Установить курсор в ячейку А3;
  •  Ввести № п/п;
  •  Установить курсор в ячейку В3;
  •  Ввести Наименование товара;
  •  Аналогично ввести название следующих колонок;
  •  Выделить ячейки A3:G3;
  •  Выполнить команду меню Формат/Ячейки вкладка Выравнивание указать выравнивание по горизонтали и вертикали – По центру, отображение – Переносить по словам.
    1.  Изменение ширины столбца:
  •  Установить курсор мыши на границу между столбцами, курсор примет вид вертикальной полоски, пересеченной горизонтальной двунаправленной стрелкой;
  •  Нажать левую клавишу мыши;
  •  Изменить ширину столбца;

Или

  •  Выделить столбец;
  •  Выполнить команду меню Формат/Столбец/Ширина ввести необходимую ширину.
  1.  Заполнить таблицу данными, необходимо ввести пятнадцать записей.
    1.  Ввод порядковых номеров:

1-й способ:

  •  Установить курсор в ячейку А3, ввести 1;
  •  В ячейку А4, ввести 2;
  •  Выделить ячейки А3:А4;
  •  С помощью маркера заполнения, протянуть до нужной ячейки.

2-й способ:

  •  Установить курсор в ячейку А3, ввести 1;
  •  Установить курсор в ячейку А3;
  •  Выполнить команду меню Правка/Заполнить/Прогрессия;
  •  Введите: расположение – По столбцам; шаг – 1, предельное значение – 15;
    1.  Графы Наименование товаров и Получатель содержат текстовую информацию, выравниваются по левому краю.
    2.  Дата отгрузки форматируется следующим образом: Формат/Ячеек вкладка Число/Дата. Введите дату поступления за указанный месяц.
    3.  Графы Цена и Сумма содержат цифровую информацию, выравниваются по правому краю.
  •  Выделите ячейки F3:G17;
  •  Присвойте ячейкам денежный формат:
    1.  Ввод формулы в графу Сумма:
  •  Установить курсор в ячейку G3;
  •  Введите = (формула начинается со знака =), щелкните указателем мыши ячейку E3, введите знак *, щелкните указателем мыши ячейку F3;
    1.  Копирование формулы.

1-й способ:

  •  Установить курсор в ячейку G3;
  •  С помощью маркера заполнения, протянуть до нужной ячейки.

2-й способ:

  •  Выделить ячейки G3:G17;
  •  Введите формулу в ячейку G3;
  •  Нажмите Ctrl+ОК.

3-й способ:

  •  Выделить ячейки G3: G17;
  •  Выполнить команду меню Правка/Заполнить/Вниз.
    1.  Ввести данные в созданную таблицу можно с помощью команды меню, Данные/Форма (для этого необходимо выделить всю таблицу с названиями столбцов). Выполните самостоятельно.
    2.  Подвести итог по графе Сумма:
    •  Установить курсор в ячейку G18;
    •  Выбрать пиктограмму Автосумма на панели Стандартная.
    1.  Сделайте обрамление таблицы:
  •  Выделите ячейки А3:G17;
  •  Выбрать пиктограмму Границы на панели Форматирование (меню Формат/Ячейки вкладка Граница).
    1.  Переименуйте данный лист:
  •  Установить курсор на ярлычок листа;
  •  Нажмите правую клавишу;
  •  В контекстном меню выберите пункт Переименовать;
  •  Введите имя – соответствующей вашей таблицы месяц.
  1.  Сформировать аналогичную таблицу за следующий месяц. Для этого:
    1.  Скопировать таблицу на Лист2;
  •  Установить указатель мыши на прямоугольнике пересечения столбцов и строк;
  •  Щелкнуть левой кнопкой мыши;
  •  Выполнить команду меню Правка/Копировать, или вызов контекстного меню правой кнопкой мыши – команда Копировать, или пиктограмма Копировать на панели Стандартная;
  •  Перейдите на Лист2;
  •  Выполнить команду меню Правка/Вставить, или вызов контекстного меню правой кнопкой мыши – команда Вставить, или пиктограмма Вставить на панели Стандартная.

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

3.2.Внести необходимые корректировки, а именно:

Изменить месяц в графе Дата отгрузки:

  •  Выделить ячейки D3:D17;
  •  Выполнить команду меню Правка/Заменить;
  •  В открывшимся окне указать в строке Найти: .09 (т.е. текущей месяц), в строке Заменить на : .10 (на нужный месяц);

Изменить дату в графе Дата отгрузки:

  •  Выделить ячейки D3:D17;
  •  Выполнить команду меню Правка/Очистить/Все или контекстное меню Очистить содержимое;
  •  Не снимая выделения, введите нужную дату в ячейку D3;
  •  Выполнить команду меню Правка/Заполнить/Прогрессия, указать тип – Даты, шаг – 3, единицы – День, предельное значение – 31.10.06 (т.е. последнее число введенного месяца);

3.3.Переименуйте лист в названия месяца.

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

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

Часть 2. Фильтрация данных. Графики.

  1.  Фильтрация данных.

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

1.1.Установка автофильтра.

Последовательность действий:

  •  Установить курсор в диапазон таблицы;
  •  Выберем команду Данные/Фильтр/Автофильтр;

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

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

Если необходимо найти записи, содержащий похожий, но не совпадающий полностью текст, на помощь проходят специальные символы, называемые символами шаблона (* -звездочка и ? – знак вопроса).

Для наложения нескольких условий поиска используется логические операции и, или (and, or).

  •  Удалить Автофильтр - командой Данные/Фильтр/Автофильтр.

1.2.Установить расширенный фильтр.

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

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

  •  Скопировать шапку таблицы в ячейку А20 для ввода условий фильтрации;
  •  Ввести условия для фильтрации:
  1.  Вывести данные равные, какому-либо количеству;
  2.  Вывести данные по количеству или цене больше или меньше, заданного значения;
  3.  Вывести количество, находящиеся в определенном промежутке;
  4.  Вывести получателей, начинающихся на какую-либо первую букву;
  5.  Вывести получателей, начинающихся на какую-либо вторую букву;
  6.  Вывести товары, начинающиеся на две какие-либо буквы;
  7.  Вывести данные за определенный месяц.
  •  Выберем команду Данные/Фильтр/Расширенный фильтр…;
  •  В открывшимся окне установить переключатель Обработка/фильтровать список на месте или скопировать результат в другое место;
  •  Задайте Исходный диапазон: диапазон таблицы вместе с шапкой и Диапазон условий: ячейку шапки и ячейку или ячейки с условием;
  •  Задайте, при необходимости, Поместить результат в диапазон: указать диапазон новой таблицы вместе с шапкой, куда будет выводиться результат;
  •  Отметить фильтрацию командой Данные/Фильтр/Отобразить все.

  1.  Построить три вида диаграмм (гистограмма, график, круговая).

Графики и диаграммы лучше всего строить по шагам и для построения сформировать отдельную таблицу.

Последовательность действий:

  •  Выполнить команду меню Вставка/Диаграмма;
    •  Загрузиться Мастер диаграмм, шаг 1Тип диаграммы, выбрать нужный тип по вкладкам Стандартные и Нестандартные;
      •  Нажмите Далее;
      •  Шаг 2Источник данных диаграммы; отмечается диапазон данных, для построения графика ли диаграммы;
      •  Нажмите Далее;
      •  Шаг 3Параметры диаграммы; выбираются параметры диаграммы; Заголовки, Подписи данных, Легенда и т.д.;
      •  Нажмите Далее;
      •  Шаг 4Размещение диаграммы, устанавливается переключатель Поместить диаграмму на листе: отдельным, имеющимся;
      •  Нажмите Готово.

Диаграмма построена. Редактирование ее осуществляется, с помощью меню Вставка/Диаграмма, при этом диаграмма должна быть выделена или с помощью Контекстного меню (выделить диаграмму, нажать правую клавишу мыши, в появившимся окне, выбрать необходимое, для редактирование меню).

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

  1.  С помощью установки связи, создать “Ведомость отгрузки товаров (продукции) за (квартал) 2006 год”. Для этого:
    1.  Если в рабочий книге только три листа, то добавить еще один лист (выполнить команду Вставка/Лист и после того как новый лист появится, захватить мышью ярлычок этого листа и перенести его на последнее место). Переименовать его.

1.2.Выделите на первом листе диапазон всей таблице без заголовка.

1.3.Выполнить команду Правка/Копировать.

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

1.5.Не снимая выделения, выполнить на том же листе команду Правка/Специальная вставка, в появившимся окне (установив переключатель Вставить в положение форматы) нажать кнопку ОК (т.е. скопировать форматы).

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

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

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

1.8.Вставить колонку Е, озаглавить колонку Месяц отгрузки.

Для заполнения колонки Месяц отгрузки вызвать функцию Месяц. Для этого:

  •  Выполнить команду Вставка/Функция главного меню или пиктограмма Вставка функции панели Стандартная;
  •  В открывшимся окне выбрать: Категория - Дата и время, Функция - Месяц;
  •  Введите в диапазон Дата_как_число ячейку D3;
  •  Скопируйте функцию до конца таблицы.

1.9.Отобразить формулы (Сервис/Параметры/Формулы) и зависимость ячеек (Сервис/Зависимости/Влияющие ячейки).

  1.  Подвести промежуточные итоги, вложенные промежуточные итоги.

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

2.1.Подвести промежуточные итоги по полю, например Наименование товара.

  •  Отсортировать таблицу по заданному полю:

Последовательность действий:

  •  Выделить всю таблицу вместе с шапкой (лучше с помощью мыши);
    •  Выполнить команду меню Данные/Сортировка;
    •  В открывшимся окне, в строке Сортировка по, выбрать заданное поле Наименование товара;
    •  Нажмите ОК.

Сортировка будет произведена.

  •  Подвести итоги по заданному полу:

Последовательность действий:

  •  Выделить всю таблицу вместе с шапкой;
    •  Выполнить команду меню Данные/Итоги;
      •  В открывшимся окне, в строке При каждом изменении в: выбрать заданное поле (по которому таблица отсортирована - Наименование товара);
        •  В строке Операция выбрать любую операцию, например, Сумма;
        •  В строке Добавить итоги по поставить галочки против тех полей, по которым необходимо получить итоги (например по полю Количество и Сумма);

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

  •  Нажмите ОК.

2.2. Подвести вложенные промежуточные итоги по поле, например Наименование товара и по Получателю.

  •  Отсортировать таблицу по заданным полям:

Последовательность действий:

  •  Выделить всю таблицу вместе с шапкой;
    •  Выполнить команду меню Данные/Сортировка;
    •  В открывшимся окне, в строке Сортировка по, выбрать заданное первое поле - Наименование товара;
    •  В строке Затем, выбрать второе заданное поле - Получатель;
    •  Нажмите ОК.

Сортировка будет произведена.

  •  Подвести вложенные итоги по заданным полям:

Последовательность действий:

  •  Выделить всю таблицу вместе с шапкой;
    •  Выполнить команду меню Данные/Итоги;
      •  В открывшимся окне, в строке При каждом изменении в: выбрать заданное первое поле (по которому таблица отсортирована - Наименование товара);
        •  В строке Операция выбрать любую операцию, например, Сумма;
        •  В строке Добавить итоги по поставить галочки против тех полей, по которым необходимо получить итоги (например по полю Количество и Сумма);

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

  •  Нажмите ОК.

В таблице подведены итоги по первому отсортированному полю.

  •  Выделить всю таблицу вместе с шапкой;
  •  Выполнить команду меню Данные/Итоги;
  •  В открывшимся окне, в строке При каждом изменении в: выбрать заданное второе поле (по которому таблица отсортирована - Получатель);
    •  В строке Операция выбрать любую операцию, например, Сумма;
  •  В строке Добавить итоги по поставить галочки против тех полей, по которым необходимо получить итоги (например по полю Сумма);
  •  Снять галочку в строке Заметить текущие итоги;
  •  Нажмите ОК.

Подвелись промежуточные итоги по двум полям.

2.3.Подвести вложенные промежуточные итоги по Месяцу и Наименованию товара; Месяц и Получатель; по Наименование товара и Месяцу; по Получателю и Месяцу.

Часть 4. Консолидация. Сводные таблицы.

  1.  Консолидация данных.

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

Последовательность действий:

  •  Добавить, если необходимо, еще один лист (выполнить команду Вставка/Лист);
    •  Установить курсор в ячейку А1;
      •  Выберем в меню Данные/Консолидация;
      •  В появившимся диалоговом окне, в выпадающем списке Функция выберем, например, функцию Сумма, т.е. данные, будут суммироваться. Но можно выбрать и другую итоговую функцию: посмотрите список;
      •  Следующее поле: Ссылка. Выполним последовательность действий: установив фокус ввода в этом поле, будем по очереди выделять диапазоны для консолидации; для этого перейдем на лист первого месяца, выделим диапазон таблицы вместе с шапкой, без поля № п/п и без итогов, появится выделенный диапазон, щелкнем кнопку Добавить – адрес диапазона переместиться в окно Список диапазонов. Перейдем на лист второго месяца и также выделим диапазон, щелкнем кнопку Добавить, таким же образом выделите диапазон третьего месяца;
      •  В диалоговом окне имеется блок Использовать в качестве имен из двух флажков подписи верхней строки и значения левого столбца. Установим оба параметра. Эти флажки нужно установить, потому что информация в таблице будет идентифицироваться по названиям строк и столбцов.
      •  Флажок Создавать связи с исходными данными. Если он не будет установлен, то при внесении изменений в один из диапазонов месяца, данные в консолидируемой таблице не изменяться.
      •  Сделать консолидацию по минимуму, максимуму, среднему. Для этого можно, выделить лист, Удалить содержимое лист, выполнить команду Данные/Консолидация. В появившимся диалоговом окне, поменять Функцию, т.к. диапазоны сохраняться.
  1.  Создание сводной таблицы в списке или базе данных.

Последовательность действий:

  •  Выберем меню Данные/Сводная таблица. Запускается Мастер сводных таблиц;
    •  Шаг 1 из 3, выбираем источник данных. Создать таблицу на основе данных, находящихся: установим переключатель в положении в списке или базе данных Microsoft Excel (по умолчанию этот пункт уже выделен); Вид создаваемого отчета: переключатель в положении Сводная таблица;
      •  Нажмем кнопку Далее;
      •  Шаг 2 из 3, предлагается Укажите диапазон, содержащий исходные данные. Диапазон: базы данных – данные по кварталу, полученные, с помощью установки связей.
      •  Нажмем кнопку Далее.
      •  Шаг 3 из 3, Поместить таблицу в: переключатель новый лист;
      •  Нажмем кнопку Макет;
      •  В диалоговом окне, мы видим, еще не заполненный макет. Из группы кнопок полей, расположенной справа, перетащите нужные поля в области формируемой таблицы. В область поля Данные обычно помещают числовые поля;
      •  Для создания таблицы нажмите кнопку Готово.

2.1.Обновление сводной таблицы.

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

2.2.Изменение итоговой функции.

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

  1.  Создание сводной таблицы в нескольких диапазонах консолидации.

Последовательность действий:

  •  Выберем меню Данные/Сводная таблица. Запускается Мастер сводных таблиц;
    •  Шаг 1 из 3, выбираем источник данных. Создать таблицу на основе данных, находящихся: установим переключатель в положении в нескольких диапазонах консолидации; Вид создаваемого отчета: переключатель в положении Сводная таблица;
      •  Нажмем кнопку Далее;
      •  Шаг 2а из 3, переключатель в положении Создать одно поле страницы;
      •  Нажмем кнопку Далее;
      •  Шаг 2а из 3, Где расположены диапазоны страниц, которые следует консолидировать? Укажите Диапазон: Проделайте это точно так, как в диалоговом окне Консолидация;
      •  Нажмем кнопку Далее;
      •  Шаг 3 из 3, Поместить таблицу в: переключатель новый лист;
      •  Нажмем кнопку Макет;
      •  Перетащите нужные поля в области диаграммы Строка и Столбец (в шаблоне поля уже расположены, но можно поменять их местами);
      •  Для создания таблицы нажмите кнопку Готово.
      •  В поле Страница1, объектам присвойте названия ваших страниц, т.е. месяцев (название месяцев присваиваются по алфавиту).

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



КОНТРОЛЬНЫЕ ВОПРОСЫ

  1.  Для каких целей применяются электронные таблицы?
  2.  Возможности Excel.
  3.  Какие электронные таблицы вы знаете?
  4.  Основные термины прикладной программы Excel.
  5.  Что такое содержимое ячейки, значение содержимого ячейки, формат ячейки и ее адрес?
  6.  Какие операции и функции используют при написании формул в ячейках электронных таблиц?
  7.  Данные каких типов могут быть записаны в ячейку?
  8.  Какие значения может принимать содержимое ячейки?
  9.  Как изменить размер ячейки?
  10.  Как записываются абсолютные и относительные адреса ячеек?
  11.  Когда необходимо использовать абсолютные адреса ячеек?
  12.  Основные команды системы.
  13.  Форматы файлов. Вывод на печать.


 

А также другие работы, которые могут Вас заинтересовать

55598. РЕЧЕВЫЕ ФОРМЫ ВЕДЕНИЯ УРОКА 38.5 KB
  Wir gehen jetzt wie folgt vor. Wir bilden Zweier/Dreiergruppen. Diese Gruppen setzen sich zusammen und spielen (schreiben) einen Dialog. Versucht erst einmal, alte Dialoge abzuwandeln.
55600. ТРАВМАТИЗМ НА ЗАНЯТИЯХ ФИЗКУЛЬТУРЫ 63 KB
  Основными причинами травматизма являются организационные недостатки при проведении занятий. При проведении уроков по метаниям неправильно проложенная лыжня или неподготовленная трасса для кросса; неправильное комплектование групп по уровню подготовленности возрасту...
55601. Селянська реформа в Наддніпрянській Україні. Зміни в сільському господарстві 55 KB
  Зміни в сільському господарстві з використанням мультимедійного обладнання Мета навчальна: з’ясувати особливості соціальноекономічного розвитку українських земель напередодні селянської реформи 1861р. ознайомити учнів із проведенням особливостями і наслідками реформи формувати уявлення про реформу як явище.
55602. ЛИНГВИСТИЧЕСКАЯ РЕГАТА 186 KB
  Команды в соответствующих костюмах представляют название флаг под которым отправляются в плавание девиз. 3 из них мели рифы айсберги попав на которые команда пропускает 1 ход 16 объектов предполагают 34 вопроса или задания для участников.
55603. ВИКОНАННЯ КАСОВИХ ОПЕРАЦІЙ. РОЗРАХУНКИ З ПІДЗВІТНИМИ ОСОБАМИ 491 KB
  Звідси випливає завдання істотного поліпшення системи вищої освіти, якості підготовки фахівців. Все це викликає необхідність перегляду мети, змісту і технологій навчання, а, остаточно, самих уявлень про кваліфікованих працівників – випускників вищих навчальних закладів – високоосвічених, компетентних, здатних приймати правильні обґрунтовані рішення в умовах, що постійно змінюються, які можуть знайти застосування своїм знанням та вмінням у різних сферах діяльності.
55604. РЕКЛАМА 200.5 KB
  Мета проекту: активувати творчу ініціативу вихованців через пошукову діяльність; розвивати інтерес до економічного і політичного розуміння свого місця і значення в суспільному житті; сприяти вихованню в учнів активної життєвої позиції; познайомити дітей із терміном рекламa...
55605. Реклама – как карьера. Её роль в бизнесе 42 KB
  Advertising has rapidly rushed into our life. TV- programs, radio-news, newspapers deal with the advertising every day, using different ways of its presentation. So the topic of our lesson is the role of advertising in business and everyday life. Let’s recollect words and expressions we need for our discussion.
55606. Рекомендации для учителей начальных классов по использованию групповых форм работы на уроках чтения и письма 57.5 KB
  Чтение слов анаграмм составление учащимися слов анаграмм обмен зашифрованными словами Налво волна клво волк Ронова ворона вемеддь медведь Швиня вишня бейрово воробей Упражнение БимБом слоговая зарядка: 1ученик называет слог...