Лабораторные и практические работы по Excel
план-конспект урока по информатике и икт (9 класс) на тему

Тема. Освоение приемов работы с электронными таблицами.

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

Цель: 1) получить практические навыки ввода и редактирования данных в ячейки электронной таблицы;

          2) освоить операции заполнения и автозаполнения ячеек электронной таблицы.

Форма отчета: 1) Запись в тетради темы работы;

                          2) краткий конспект;

                          3) умение отвечать на контрольные вопросы;

                          4) сохраненный файл.

Теоретический материал.

1. Ввод данных в таблицу можно начинать, если в строке состояния высвечивается индикатор Готово. Во время ввода данных в строке состояния появляется индикатор ВВОД.

2. Типы данных, которые можно вводить в ячейки таблицы:

• Текст – последовательность букв, иногда цифр (автоматически выравнивается по левому краю ячейки);
• Числа – цифры и различные символы (знак $, %, р.). В качестве десятичного разделителя используется запятая (12,3);
• Дата и время вводятся в ячейки как числа и выравниваются по правому краю (12.02.2006, 12/02/2006, 12 февраля 2006);
• Формула – арифметическое и логическое выражение, которое начинается со знака =;
• Функция – программа с уникальным именем, для которой задаются конкретные значения аргументов.

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

Зафиксировать данные можно одним из способов:

- нажать клавишу ENTER;

- щелкнуть мышью в другой ячейке;

- перейти к другой ячейке с помощью клавиш управления курсором;

- щелкнуть по кнопке   в Строке формул;

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

Практика.

 Запустите программу EXCEL любым известным вам способом.

I. Ввод и редактирование данных в ячейки таблицы.

    1) На листе 1 выделите ячейку С4 и введите с клавиатуры текст  «Проба»

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

 2) Вновь выделите ячейку С4.

      Найдите на панели Форматирование кнопки выравнивания абзацев и выберите кнопку  (по центру). Прокомментируйте результат.

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

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

 3) удалите содержимое ячейки С4.

      Для этого выделите ячейку С4 и нажмите клавишу DELETE.

4) В ячейку D5 введите текст «Золотая осень».

 ! Обратите ВНИМАНИЕ, что в тех случаях, когда соседняя ячейка пуста, длинный текст распространяется на нее.

Выделите ячейку Е5 и введите в нее название первого осеннего месяца.

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

 Вновь выделите ячейку D5. В Строке формул дублируется содержимое выделенной ячейки.

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

 5) В ячейку В8 введите текст «Унылая пора!» и зафиксируйте данные, выбрав кнопку  Строки формул. Чем отличается этот способ фикзации от других?

Попробуйте продолжить стихотворную фразу «Унылая пора!..»

Выделите ячейку, содержащую начало фразы (В8) и введите с клавиатуры окончание фразы.

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

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

Для редактирования данных нужно:

1 способ

• Активизировать ячейку;
• Щелкнуть в строке формул;
• Внести в данные необходимые изменения;
• Завершить ввод.

 2 способ

• Выполнить двойной щелчок по ячейке;
• Внести в данные необходимые изменения;
• Завершить ввод.

6) Отредактируйте ячейку В8, изменив текст в Строке формул

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

II. Автозаполнение.

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

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

Все задания выполнять на листе 2

Задание 1.  Заполните диапазон ячеек А1: А10 текстом «Утро»

Методические указания.

1. Введите текст в ячейку А1. Зафиксируйте данные. Вновь выделите ячейку А1;
2. Подведите указатель мыши к маркеру заполнения;

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

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

ВАЖНО! В процессе заполнения блока ячеек указатель мыши принимает форму +

Задание 2.         В ячейки В1-В6 введите расписание уроков на сегодня.

  Выделите блок ячеек (В1-В6), рамка выделения имеет общий маркер заполнения.

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

Отрегулируйте ширину столбцов.

Задание 3.  В ячейку С15 введите название вашего любимого месяца. Протащите маркер заполнения на несколько ячеек вниз.

! Вместо ожидаемого копирования произошло заполнение ячеек в соответствии с последовательным списком месяцев года? В Excel есть несколько подобных списков

Удалите все месяцы, кроме одного.

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

Задание 4.  Заполните диапазон ячеек F1-F10 числовым рядом по образцу.

Методические указания.

1. В ячейку F1 введите число 1;
2. В ячейку F2 введите число 2;
3. Выделите обе ячейки
4. Протащите маркер заполнения на нужное количество ячеек (обратите внимание на всплывающую подсказку)

Задание 5. Заполните диапазон ячеек  G1- К1 по образцу

Методические указания .

 Воспользуйтесь указаниями к заданию №4, изменив самостоятельно содержание пунктов

Задание №6. Заполните диапазон ячеек  G3- К3  по образцу

Методические указания.

Воспользуйтесь методическими указаниями к Заданию №3.

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

Задание 7. Сохраните файл в своей папке под именем Лабораторная работа №2.

Контрольные вопросы.

1. Какие типы данных можно вводить в электронную таблицу?
2. Какие способы завершения ввода данных вы знаете?
3. Как можно отредактировать данные в ячейках?
4. Что такое Автозаполнение и как его выполнить?
5. Как заполнить ряд числовых данных, данных с определенным шагом?

Тема. Освоение приемов работы с электронными таблицами.

Ввод формул.

Цель:  получить практические навыки по выполнению вычислений  в электронной таблице;

          Форма отчета: 1) Запись в тетради темы работы;

                          2) краткий конспект;

                          3) умение отвечать на контрольные вопросы;

                          4) сохраненный файл.

Теоретический материал.

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

Формулы всегда начинаются со знака = и вводятся  английскими буквами.

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

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

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

ПРАКТИКА.

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

Методические указания.

1. В ячейки В2, В3, В4 введите соответствующий текст. Измените ширину столбца В таким образом, чтобы текст в ячейках  помещался полностью.
2. В ячейку С2 введите значение текущего курса $;
3. В ячейку С3 введите значение обмениваемой суммы в $;
4. В ячейку С4 введите формулу = С2*С3 следующим образом:

• Выделите ячейку С4 и введите с клавиатуры знак равенства =;
• Щелкните мышью по ячейке С2 (в формуле появится ссылка на эту ячейку);
• Введите с клавиатуры знак умножения *
• Щелкните по ячейке С3 (второй множитель), в формуле появится ссылка на эту ячейку. В этот момент в ячейке С4 набрана вся формула =С2*С3
• Зафиксируйте формулу нажатием клавиши Enter. В ячейке С4 появится число- результат.

Задание 2. Измените в таблице сумму обмениваемых долларов.

                  Проверьте, изменилась ли автоматически сумма в рублях.

Вывод. Для того, чтобы ввести формулу нужно:

1. Выделить ячейку, в которой будет размещен результат вычислений.
2. Ввести  знак равенства =
3. Набрать формулу. Для того, чтобы ввести адрес ячейки, достаточно щелкнуть по этой ячейке;
4. Зафиксировать формулу нажатием на клавишу Enter.

Задание 3. ( на листе 2) Измените оформление своих данных в соответствии с приведенным образцом.  

    Методические указания.

1. Выделите ячейки В2, В3 и В4.
2. С помощью Панели Форматирование измените размер шрифта (14) и начертание (К).     Для ячейки В4 начертание – полужирный курсив (сочетание Ж и К);
3.    С помощью пункта   Формат/ Ячейки/ Граница

            - Выберите тип линии;

            - границы внешние и внутренние;

            - нажмите кнопку ОК.

 4.  Выделите ячейки С2, С3, С4. Измените размер шрифта (16), для ячейки С4 задайте начертание шрифта полужирный курсив

5.    С помощью пункта горизонтального меню Формат/ Ячейки/ Граница установите тип линии  для внешних границ и  для внутренних;

Задание 4.  Подготовьте бланк заказа для мебельной фабрики  по образцу.

Методические указания:

1. В ячейки А3 – С3 введите заголовки таблицы. Выделите эти ячейки и примените к ним полужирный стиль начертания и выравнивание по центру;
2. В ячейки А4 –А8 введите перечень мебели, в ячейки В4 –В8 – цены;
3.  Задайте рамку, выделив блок ячеек от А3 до С8 (Формат/Ячейки/Граница);
4.  В ячейку В10 введите текст Сумма заказа и примените полужирный стиль начертания;
5.  Для ячейки С10 задайте рамку и примените денежный формат числа (Формат/Ячейки/Число/Денежный  число десятичных знаков-0, обозначение- р.);
6. В ячейку А1 введите заголовок Набор мебели, с помощью кнопки  объедините ячейки А1-С1 и разместите заголовок по центру таблицы;
7. В столбец Количество проставьте количество для каждого типа мебели;
8. В ячейку С10 введите формулу для вычисления суммы заказа.

Распространение формулы

Задание 5.  (на листе 3) Составьте таблицу положительных значений функции  Y=

Методические указания:

1. В ячейки А2, А3 введите соответственно текст Х и У, примените к этим ячейкам полужирный стиль начертания и выравнивание по центру;
2. С помощью маркера заполнения в ячейки В2 – I12 введите последовательность чисел от 1 до 8;
3. Выделите столбцы от А до I и с помощью команды Формат/Столбец/Ширина установите ширину столбца равную 5;
4. В ячейку В3 введите формулу =1/В2, зафиксируйте ее и с помощью маркера заполнения распространите  на остальные ячейки С3 – I3.

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

Вставка формул с помощью Мастера функций

Задание 6. Подготовьте таблицу значений функции Y= X4 для значений аргумента от -3 до 3.

Методические указания.

1. В ячейки А4, А5 введите соответственно текст «Х» и «У»;
2. Знакомым вам способом введите значения Х от -3 до 3 в ячейки В4 – Н4;
3. Выделите ячейку В5 и запустите Мастер функций, выбрав кнопку  в Строке формул (или на панели Стандартной);
4. В открывшемся окне выберите категорию Математические,  функцию Степень и ОК;

5. В следующем окне диалога курсор установлен в поле ввода Число. Нужно выделить ячейки, содержащие значения аргумента (В4 – Н4). Если окно диалога загораживает вашу таблицу, отодвиньте его в сторону. Затем установите курсор в поле ввода Степень и введите показатель степени (4). Нажмите кнопку Ок;

6. Зафиксируйте формулу и распространите ее вправо;
7. сравните ваши результаты с приведенными ниже.

Задание 7.  Самостоятельно подготовьте таблицу значений функции У= для значений аргумента от 1 до 10. Для составления формулы воспользуйтесь Мастером функций. В категории Математические выберите функцию Корень.

Автосуммирование

Задание 8. Вставьте лист. Подготовьте таблицу по предлагаемому образцу с учетом всех элементов форматирования.

Методические указания.

1. Выполните команду Вставка/Лист;
2. Введите данные в соответствующие ячейки таблицы;
3. Выделите шапку таблицы (ячейки А1 –С1), примените полужирный стиль начертания. Выполните команду: Формат/Ячейки/Выравнивание. В открывшемся окне установите переключатель Переносить по словам и Ок;
4. В ячейку А12 введите текст «Общая территория г. Москвы», а в ячейку А13 текст «Численность населения г. Москвы);
5. В ячейке В12 с помощью кнопки   (Автосумма) вычислите суммарное значение ячеек В2-В11;
6. Аналогичные расчеты выполните в ячейке С13 (сумма значений ячеек С2- С11);
7. Сохраните работу в своей папке под именем Лабораторная работа №3.

Проверьте  себя и убедитесь, что теперь вы знаете:

• Как ввести формулу;
• Как отредактировать формулу;
• Как распространить формулу;
• Как вставить формулу с помощью Мастера функций;
• Как задать рамку для таблицы;
• Как выровнять заголовок по центру выделения4
• Как расположить текст в ячейке в несколько строк;
• Как пользоваться кнопкой Автосумма.

Тема. Освоение приемов работы с электронными таблицами.

Абсолютные и смешанные ссылки

Цель:  познакомиться с абсолютными и смешанными ссылками;

          Форма отчета: 1) Запись в тетради темы работы;

                          2) краткий конспект;

                          3) умение отвечать на контрольные вопросы;

                          4) сохраненный файл.

Теоретический материал.

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

      Относительная ссылка используется в формуле для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют следующий вид: А1, В1 и т.д.

ПРАКТИКА.

Задание 1. Подготовьте таблицу по образцу

Методические указания.

1. Разместите таблицу в ячейках В3 – F5;
2. Введите наименование товара и цену за единицу;
3. Примените к ячейкам нужные способы выравнивания, установите рамки;
4. К ячейкам, содержащим цены, примените денежный формат числа с разделение на разряды и двумя десятичными знаками (Формат/Ячейки/Число/Денежный);
5. Ячейку В3 объедините с ячейкой В4 и выполните команду Формат/Ячейки/Выравнивание/ Переносить по словам;
6. Рассчитайте стоимость товара за две единицы. В ячейку D5 введите формулу =C5*D4 (цену за единицу товара умножить на количество);
7. Распространите формулу вправо (используя маркер заполнения), чтобы получить стоимость товара за 3 и 4 единицы;
8. Сравните свой результат с приведенным ниже:

9. Можно заметить, что вычисленная по формуле стоимость товара за три единицы и за четыре неверна. Если выделить ячейку Е5, то в Строке формул появится формула , должна быть формула =С5*Е4 (аналогично для ячейки F5);

Внимание! В результате распространения формулы вправо изменились и ссылки, а нам необходимо каждый раз количество товара умножать на цену за единицу, то есть на содержимое ячейки С5. В таких случаях, составляя формулу, применяют абсолютные ссылки. При перемещении или копировании абсолютные ссылки не изменяются (ячейка фиксируется). Абсолютные ссылки имеют вид: $F$9, $A$3 и т.д. Для фиксации координаты применяют знак  $.

10. Измените формулу в ячейке D5. Вместо формулы =С5*D4 должна быть формула

          Распространите ее вправо. Сравните результат.

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

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

Смешанные ссылки

 Теоретический материал.

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

Задание 2. ( на листе 2) Составьте таблицу степеней для чисел первого десятка. Для этого заготовьте таблицу по образцу.

По вертикали расположены числа, а по горизонтали – показатели степеней. 

Методические указания.

1. Заполните таблицу по образцу;
2. В ячейку В3 с помощью Мастера функций введите первую формулу для вычисления степени. В поле Число введите А3, в поле Степень – В2. В результате в ячейку В3 будет введена формула =СТЕПЕНЬ (А3;В2).
3. Попробуйте распространить эту формулу вниз. В     результате получится следующая картина:

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

Если выделить ячейку В4, то можно увидеть, что в ней размещена формула                                                      вместо формулы =СТЕПЕНЬ (А4;В2)

Следовательно, для распространения формулы необходимо зафиксировать положение строки 2 (чтобы показатели степени вставлялись в формулу только из одной строки). То есть формулу в ячейке В3 нужно заменить на =СТЕПЕНЬ (А4;В$2)

4. Измените формулу в ячейке В3 и распространите ее вниз. Для первого столбца получили правильный результат.
5. Выделите ячейку В6 и распространите формулу вправо.

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

6. В ячейке В3 измените формулу на окончательный вариант  ;
7. Распространите эту формулу вниз и вправо на все ячейки диапазона.

Внимание! Для ввода смешанных ссылок нет необходимости вводить знак $ с клавиатуры, достаточно воспользоваться клавишей  . Многократное нажатие клавиши  срабатывает как переключатель и дает возможность выбрать нужную ссылку, например: А8, $A$8, $A8, A$8.

  Задание 3.  (лист 3)     Самостоятельно составьте таблицу умножения чисел.

  Сохраните работу в своей папке под именем Лабораторная работа №4

!!! Проверьте себя и убедитесь, что теперь вы знаете:

• В каких случаях применяют абсолютные и смешанные ссылки;
• Каким образом вводят абсолютные и смешанные ссылки.

Тема.   Диаграммы в EXCEL

Работа с диаграммами

Цель: научиться строить , редактировать и форматировать диаграммы в программе EXCEL

Форма отчета: 1) Запись в тетради темы работы;

                           2) краткий конспект;

                           3) умение отвечать на контрольные вопросы;

                           4) сохраненный файл.

Теоретический материал.

1. Диаграмма – это условное изображение числовых величин или их соотношений графическим способом.

     Типы диаграмм:

     А) график функции У= F(X)

     Б) линейные диаграммы

     В) гистограмма (столбчатая диаграмма)

     Г)  круговая диаграмма

     Д) кольцевая диаграмма    

Для построения диаграмм используется Мастер диаграмм. Его можно запустить, активизировав кнопку  на панели Стандартной. Диаграмма строится за 4 шага:

Шаг 1. Запускаем Мастера диаграмм. Выбираем тип и вид будущей диаграммы (Далее);

Шаг 2. На вкладке Ряд необходимо задать исходные данные для диаграммы.

        А) Для этого приведем открывшееся окно к следующему виду ;

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

Первое – имя ряда (для ввода названия ряда необходимо щелкнуть мышью на верхней красной стрелочке, выделить  название в таблице и снова щелкнуть на стрелочке);

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

Третье – подписи оси ОХ ( открыть нижнюю строку ввода данных щелчком мыши по стрелке, выделить в таблице нужный диапазон и закрыть строку).

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

Шаг 3.  Задать название диаграммы (вкладка Заголовки), подписи данных, легенду ;

Шаг 4. Размещение диаграммы. Диаграмму можно разместить на имеющемся листе (на одном листе с таблицей) и на отдельном листе. Выбрать нужный вариант и нажать кнопку Готово.

ПРАКТИКА.

Откройте папку Мои документы/Практика. Найдите в ней и откройте файл Работа с диаграммами, Лист 1.

Задание 1. Постройте круговую диаграмму для данной таблицы

Методические указания.

1. Запустите Мастера диаграмм;
2. Выберите тип диаграммы Круговая, вид – объемная (кнопка Далее);
3. На вкладке Ряд задайте значения для диаграммы (откройте среднюю строку вода данных и в таблице выделите столбец с числовыми значениями) и подписи оси ОХ – категории (откройте нижнюю строку ввода данных и в таблице выделите столбец с названиями округов). Кнопка Далее;
4.  На следующем шаге введите название диаграммы, легенду разместите внизу и задайте подписи данных – значения.
5. Разместите диаграмму на отдельном листе. Готово.

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

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

Для этого необходимо вызвать контекстное меню области построения диаграммы (ЩП  на области диаграммы) и с помощью команд:

Тип диаграммы;

Исходные данные;

Параметры диаграммы;

Размещение

  внести необходимые изменения в диаграмму.

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

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

Задание 2.     Лист 2 . Вам дана таблица «Результаты промежуточной аттестации учащихся 5-х классов». Необходимо построить гистограмму (сравнительную столбчатую диаграмму) по итогам промежуточной аттестации и по итогам года. В качестве значений использовать данные качества обученности, подписи оси ОХ – фамилии учителей. Легенда – справа, подписи данных – значения

Задание 3. Скопируйте таблицу «Результаты промежуточной аттестации» на Лист 3. Используя данные, приведенные в таблице, построить следующие диаграммы:

А) круговую диаграмму, отражающую численность аттестуемых в 5-х классах;

Б) столбчатую диаграмму, отражающую Успеваемость по итогам пром. аттестации и итогам года;

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

Задание 4. 

Сохраните работу в своей папке под именем Лабораторная работа №5

Тема. Освоение приемов работы с электронными таблицами.

Сортировка и фильтрация данных

Цель:  ознакомиться с методами сортировки и фильтрации данных в электронных таблицах.

Форма отчета: 1) Запись в тетради темы работы;

                          2) краткий конспект;

                          3) умение отвечать на контрольные вопросы; на  1,3,5,7 письменно;

                          4) сохраненный файл.

Теоретический материал.

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

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

ПРАКТИКА.

Задание 1.  В папке Мои документы/Практика найдите и откройте файл «Сортировка и Фильтрация». Заполните  таблицу в соответствии с образцом. Выполните Сортировку .

Методические указания.

1. Перед заполнением столбцов «Время отправления из Москвы», «Время в пути», «Время прибытия в Киев» и «Время стоянки в Киеве» установите для соответствующих ячеек формат числа Время. Для этого выполните команду Формат/Число/Время, выбрав тип времени (13:00).
2. Сохраните таблицу в своей папке под именем Лабораторная работа №6;
3. Выделите таблицу и скопируйте ее в Буфер обмена;
4. Вставьте таблицу из Буфера обмена на Лист2 и Лист3;
5. С помощью контекстного меню переименуйте Лист1 в Лист Сортировка;
6. На Листе Сортировка скопируйте таблицу ниже еще два раза;
7. Выделите первый экземпляр таблицы и выполните команду Данные/Сортировка;
8. В раскрывающемся списке Сортировать по выберите «Время прибытия в Киев»;
9. Убедитесь, что переключатель стоит в положении По возрастанию; нажмите ОК.
10. Во втором экземпляре таблицы (Лист Сортировка) самостоятельно отсортируйте данные таблицы по времени нахождения поезда в пути (по убыванию);
11. В третьем экземпляре таблицы выполните сортировку по двум ключам. Для этого в окне Сортировка диапазона (Данные/Сортировка) выберите первый ключ (Сортировать по) – «Время стоянки в Киеве» - по убыванию, а второй ключ (Затем по) –«Время отправления из Москвы» - по возрастанию; ОК.

Фильтрация с помощью Автофильтра

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

1) установить курсор внутри таблицы;

2) выполнить команду: Данные/ Фильтр/ Автофильтр;

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

4) выбрать строку «Условие» и задать критерии выборки.

Задание 2. (Лист 2) Из таблицы Расписание поездов выберите все поезда, имеющие конечным пунктом назначения Киев пасс.

Методические указания.

1. С помощью контекстного меню переименуйте Лист 2 в Лист Автофильтр;
2. Щелкните мышью внутри таблицы и выполните команду Данные/Фильтр/Автофильтр;
3. Раскройте список столбца «Конечный путь следования» и выберите строку Условие;
4. В открывшемся окне с помощью раскрывающихся списков задайте критерии выборки (равно Киев пасс.), Ок;
5. Для восстановления исходной таблицы нужно щелкнуть мышью по кнопке  синего цвета и в списке выбрать строку Все.

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

Фильтрация данных с помощью расширенного фильтра

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

Фильтрация данных с использованием расширенного фильтра выполняется с помощью команды Данные /Фильтр /Расширенный фильтр

Задание 3.  (Лист 3) В таблице Расписание поездов  отберите те поезда, которые отправляются из Москвы от девяти до десяти часов вечера.

Методические указания.

1. С помощью контекстного меню переименуйте Лист 3 в Лист Расширенный;
2. Скопируйте столбец по которому будете делать выборку («Время отправления из Москвы») и вставьте его рядом с исходным;
3. Между значениями и «шапкой» таблицы вставьте 2 строки (Вставка/Строка);
4. Под названием столбца задайте условие выборки и выполните команду Данные/Фильтр/Расширенный фильтр;
5. В открывшемся окне с помощью текстовых полей задайте исходный диапазон (выделите всю таблицу) и диапазон условий (заголовки столбцов вместе с условиями) и нажмите Ок;

P.S. Для снятия фильтра необходимо выполнить команду Данные /Отобразить все 

Самостоятельно! Отберите те поезда, которые прибывают в Киев от десяти до одиннадцати часов утра.

Контрольные вопросы:

1. Как сортировать данные по одному ключу?
2. Какой порядок применяется для сортировки данных?
3. Как сортировать данные по нескольким ключам?
4. Что такое фильтрация?
5. Как пользоваться Автофильтром для отбора записей?
6. Для чего нужен расширенный фильтр?
7. Как пользоваться расширенным фильтром?

Тема.  Анализ и обобщение данных в электронных таблицах Excel

Цель:  1) освоение операции автоматического подведения итогов;

             2) работа со структурой электронной таблицы

Форма отчета: 1) Запись в тетради темы работы;

                           2) краткий конспект;

                           3) сохраненный файл.

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

ПРАКТИКА.

Задание 1. Создайте таблицу по образцу. Вставьте в нее  еще 2 листа (ВСТАВКА/Лист) и сохраните таблицу  в своей папке под именем «Итоги»;

Задание 2. Выполните вычисления в столбце «Сумма».

Методические указания.

1.    В ячейке F2 выполните вычисления;
2. Распространите формулу на ячейки F3 – F17.

Задание 3. Выполните сортировку данных таблицы по типу товаров.

Методические указания.

1.  Выделите таблицу;
2. Выполните команду ДАННЫЕ \ Сортировка (сортировать столбец Товар – по возрастанию, затем Тип – по возрастанию).
3. Скопируйте таблицу на Лист 2, 3, 4, 5

.

Microsoft Excel позволяет автоматически вычислять промежуточные итоги.

Задание 4. (На Листе 2) найти суммы, затраченные на покупку всех принтеров и всех сканеров

Методические указания.

1.  Выделите таблицу на Листе 2. В меню ДАННЫЕ выберите команду Итоги (откроется диалоговое окно Промежуточные итоги);
2. Для того, чтобы подвести итоги по каждому типу товара (отдельно принтеры и отдельно сканеры), в раскрывающемся списке При каждом изменении в выберите «Товар»;
3. В поле Операция  должна быть выбрана Сумма;
4. Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, в поле Добавить итоги по установите флажки напротив строк «Количество» и «Сумма»;
5. Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки, и нажмите ОК.

Задание 5. (На Листе 3) вычислите среднее значение цены принтеров и сканеров.

Методические указания.

1.  Выделите таблицу на Листе 3. Выполните команду ДАННЫЕ   - Итоги, в диалоговом окне Промежуточные итоги установите следующие параметры:

2. Сохраните изменения в таблице «Итоги».

Задание 6. (На Листе 4) Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров.

 Задание 7. (На Листе 5) Самостоятельно вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров) и суммарное количество каждого типа товара.

Сравните собственные результаты с приведенными на рисунке (к заданию 7)

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

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

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

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

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

Задание 8. а) скройте данные второго уровня. Сравните результат с рисунком;

                   Б) отобразите все элементы списка, структуры.

Контрольные вопросы:

1. Какие средства Excel позволяют осуществлять автоматическое подведение итогов в электронной таблице?
2. Каково назначение структуры электронной таблицы?
3. Как работать со структурой электронной таблицы?

Тема.  Анализ и обобщение данных в электронных таблицах Excel

           Составление консолидированных отчетов.

Цель:  1) освоение операции построения итоговых таблиц (консолидации данных);

             2) научиться строить сводные таблицы.

Форма отчета: 1) Запись в тетради темы работы;

                           2) краткий конспект;

                           3) сохраненный файл.

Ход работы:

1. Откройте в своей папке файл «Итоги», созданный на предыдущем занятии.
2. На Листе 1 выделите таблицу и выполните команду Правка/ Копировать;
3. Создайте новую книгу, выбрав на панели Стандартной значок  или выполнив команду Файл / Создать / чистая книга;
4. На Листе 1 выполните команду Правка / Вставить;
5. Переименуйте Лист 1 в Принтеры;
6. Выделите строки, содержащие данные о покупке сканеров и поместите их в Буфер обмена (Правка - Вырезать), перейдите на Лист 2  и разместите на нем информацию о сканерах (Правка – Вставить);
7. Скопируйте и перенесите на Лист 2 заголовок таблицы с Листа Принтеры. В случае необходимости произведите форматирование таблицы;
8. Сохраните новый файл в своей папке под именем «Консолидация данных».

ТЕОРИЯ.

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

ПРАКТИКА.

Задание 1. 

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

Методические указания.

1.    Выберите Лист 3 и переименуйте его в «Отчет»;
2. Создайте шапку новой таблицы, как показано на рисунке.

3. Выделите ячейку В7 («Товар») и в меню Данные выберите команду Консолидация. Откроется диалоговое окно Консолидация;

4. Убедитесь, что в поле Функция находится функция Сумма.

5. Выделите данные, подлежащие консолидации. Для этого:

- щелкните в поле ССЫЛКА, а затем на ярлычке листа «Принтеры»;

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

- щелкните по кнопке Добавить. Содержимое поля ССЫЛКА перенесется в окно Список диапазонов.

- щелкните на ярлычке листа «Сканеры»;

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

- щелкните по кнопке Добавить. Обе таблицы занесены в Список диапазонов;

- в группе Использовать метки активизируйте опции в Верхней строке и В левом столбце;

- сравните вид своего окна Консолидация с приведенным на рисунке и щелкните по кнопке ОК.

6. Выполните обрамление таблицы;
7. Пустые столбцы C и  D можно скрыть (выделите их и выполните команду Формат/Столбцы/Скрыть);

                  Окончательный вариант отчета должен иметь вид:

8. Сохраните изменения в файле  «Консолидация данных».

Самостоятельно.

 - вставьте в файл «Консолидация данных» еще два листа Лист 4 и Лист 5;

- Лист 4 переименуйте в «Модемы» и создайте на этом листе таблицу закупок модемов по приведенному ниже образцу.

 Для вычисления «Сумм» введите формулу.

 - -  - переименуйте Лист 5 рабочей книг в «Отчет l» и создайте на этом листе консолидированный отчет  о закупке техники (принтеров, сканеров, модемов). Используйте функцию СУММА и данные листов «Сканеры», «Принтеры», «Модемы»;

- скройте лишние столбцы, выполните обрамление таблицы;

- сохраните изменения в файле «Консолидация данных»

- предъявите результаты работы преподавателю. позволяет автоматически вычислять промежуточные итоги.

Контрольные вопросы:

1. Что такое консолидация данных?
2. Где могут находиться источники данных для выполнения консолидации?
3. Какие средства Excel позволяют осуществлять консолидацию данных?

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

Цель: 1. Выполнение вычислений в таблицах EXCEL .

            2. Закрепление приемов работы по созданию, редактированию и форматированию диаграмм.

Форма отчета:  сохраненный файл.

Задание 1.

1. Откройте папку Мои документы/Практика и загрузите файл Заготовка (диаграммы);
2. Выполните форматирование таблицы в соответствии с образцом;

3. выполните необходимые вычисления в столбцах Цена, руб. и Выручка за июль 1998 года и сегодня;
4. найдите итоговую выручку и доход в размере 20% от выручки;
5. на основе произведенных расчетов создайте новую таблицу по приведенному ниже образцу и заполните ее данными из предыдущей таблицы:

Задание 2.   По таблице №2 постройте объемную гистограмму  в соответствии с образцом

 P.S. несмежные ячейки выделяются при нажатой клавише CTRL

Задание 3.  Сохраните работу в своей папке под именем Практическая работа №2