Лабораторно практические занятия в программе Excel

ГОСУДАРСТВЕННОЕ АВТОНОМНОЕ ПРОФЕССИОНАЛЬНОЕ

ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ СВЕРДЛОВСКОЙ ОБЛАСТИ

«ВЕРХНЕПЫШМИНСКИЙ МЕХАНИКО-ТЕХНОЛОГ7ИЧЕСКИЙ

ТЕХНИКУМ «ЮНОСТЬ»

Microsoft Office Excel 2013

Практикум

Отрак Е.С

Практикумпо Microsoft Office Excel 2013

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

Описание Microsoft Excel 2013

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

Новые инновационные возможности, реализованные в Microsoft Excel 2013

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

• новые средства, связанные с бизнес-аналитикой. Так, текущее издание продукта вмещает в себя надстройки PowerPivot и Power View, что превращает его в персонализированный, многоцелевой инструмент для бизнес-анализа искомых данных

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

• WEBSERVICE - позволяет извлечь XML-содержимое из веб-сервиса

• ENCODEURL - позволяет преобразовать текст в URL

• FILTERXML - помогает осуществить синтаксический разбор XML и извлечь из него фрагменты данных.

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

Практическая занятие № 1

Основные понятияMSExcel

Запустите редактор электронных таблицMS Excel 2007:

ПускПрограммы Microsoft Office  Microsoft Excel

Рассмотрите вид экрана MSExcel и найдите все, о чем говорится в пояснениях.

Пояснения:

Лист Excel состоит из 1 048 576 строк с номерами 1, 2, 3, 4 – 1 048 576 и 16 384 столбцов c именами А, В, С, D, Е ..., на пересечении которых находятся ячейки. т.е. из более17 миллиардов ячеек. Совокупность листов составляет рабочую книгу, которая сохраняется как целостный объект в одном файле с расширением .xlsx

Ячейка — основная единица хранения данных. Адрес ячейки (ссылка на ячейку) образуется из имени столбца и номера строки: А1, Bl, D3, Е5 и т. п.

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

Активная ячейка

Строка формул

Типы данных

В ячейку можно поместить данные следующих типов:

Задание 1. Создание и форматирование таблиц

Удерживая нажатой левую кнопку мыши, выделите таблицу из 3 столбцов и 6 строк.

Для обозначения границ выделенной области нажмите вкладку Главная Шрифт  Границы  Все границы:

Получится так:

Заполните таблицу по образцу (выберите шрифт Times NewRoman).
выделите ячейкиА1, В1, С1 и объедините их:

Для того, чтобы в км² «2» сделать надстрочным индексом, выделите «2», откройте диалоговую панель Шрифт и выберите Видоизменение надстрочный:

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

Выровняйте текст в таблице:

Выделите ячейки А2, В2,С2. Нажмите правую кнопку мыши и в контекстном меню выберите Формат ячеек… Перейдите на вкладку Выравнивание, выберите: Выравнивание по горизонтали: по центру, по вертикали:по центру, поставьте галочку переносить по словам, нажмите ОК:

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

Таблица 1

Задание 2. Наберите и отформатируйте таблицы самостоятельно:

Таблица 2

Таблица 3

Сохраните работу в папке Excel под названием Практическая работа 1.

Практическое занятие № 2

Функция автозаполнения

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

Задание 1. Автоматическое заполнение

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

1. Установите указатель мыши на точку в правом нижнем углу выделения, он примет вид тонкого черного крестика +.

2. Нажмите левую кнопку мыши и, не отпуская ее, перетащите указатель + по строке или столбцу.

3. Отпустите кнопку, и ячейки будут заполнены.

Для автозаполнения названиями месяцев или дней недели достаточно ввести название в одну ячейку и выполнить пункты 3—5.

Используйте Автозаполнение для ввода следующих данных:

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

Сделайте то же самое для последовательностей: 10, 20, …; 100, 200, …

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

Задание 2. Создание таблицы умножения

НаЛисте 2 с помощью функции Автозаполнение создайте таблицу умножения:

В ячейку

А3 введите «1»,

в ячейку В3 – знак «*»,

в ячейку С3 – «1»,

в D3 – «=».

Нажмите значок , чтобы после знака «=» отменить ввод формулы.

В ячейке Е3 поставьте знак «=» и введите формулу: щелкните по ячейке А3, введите знак «*» и щелкните по ячейке С3.

В ячейке Е3 и в строке формул появится формула «=А3*С3»:

Строка формул

Нажмите клавишу [Enter]. В ячейке Е3 появится результат умножения:

Выделите ячейку А3 и с помощью автозаполнения заполните единицами ячейки от А4 до А12:

Уменьшим ширину столбцов. Выделите столбцы от А до Z, выберите Формат, Ширина столбца и установите ширину3, нажмите ОК.

Отформатируйте столбцы от А до Z по центру.

Выделите ячейку В3 и с помощью автозаполнения заполните знаком умножения «*» ячейки от В4 до В12.

В ячейку С4 введите число 2. Выделите ячейки С3 и С4 и с помощью автозаполнения заполните ячейки до С12.

Заполните ячейки D3:D12 знаком «=».

Выделите ячейку Е1 и скопируйте формулу до ячейки Е12.

Выделите диапазон ячеек А3:Е12, скопируйте его и вставьте в ячейки G3,M3, S3, A14,G14, M14, S14,A25, G25. В ячейках G3:G12 с помощью автозаполнения исправьте «1» на «2», в ячейках М3:М12 исправьте «1» на «3» и т.д. В ячейке К34 «100» не видно, потому что не умещается, – уменьшите размер шрифта (9).

Выделите ячейки А1:W1, объедините их и напишите заголовок «Таблица умножения». Таблица умножения готова:

Задание 3. Создание календаря

На Листе 3 с помощью функции Автозаполнение самостоятельно создайте календарь на 2018 год.

Задание 4. Создание таблицы «Системы счисления»

На Листе 4 создайте таблицу, объедините ячейки А1:Е1 и напишите заголовок. Десятичные числа (столбец А) с помощью функции Автозаполнение заполните до 20 (до ячейки А23).

Выделите ячейку В3. Поставьте знак «=» и щелкните пиктограмму «Вставить функцию»

Появится окноВставка функции. Выберите категорию: Инженерные:

Выберите функциюДЕС. В ДВ., которая преобразует десятичное число в двоичное:

Нажмите ОК. Появится окно Аргументы функции. Для ввода числа щелкните по ячейке А3, разрядность введите 8.

Нажмите ОК. В ячейке В3 появится число 0 в двоичной системе.

Выделите ячейку В3 и с помощью автозаполнения скопируйте ее до ячейки В23.

Аналогично переведите десятичные числа с 1 до 20 в восьмеричную, шестнадцатеричную и римскую систему счисления.

Для перевода десятичных чисел в восьмеричную систему счисления выберите функцию ДЕС.В.ВОСЬМ, разрядность 2.

Для перевода десятичных чисел в шестнадцатеричную систему счисления выберите функциюДЕС.В.ШЕСТН, разрядность 2.

Для перевода десятичных чисел в римскую систему счисления выберите в математических функциях функцию РИМСКОЕ(категория функции – математические). В римской системе счисления нет цифры 0, поэтому начинайте с 1 (ячейка А4). Оформите таблицу.

Сохраните файл Практическая работа 2 в Мои документы, в Вашей папке, в папкеExcel.

Практическое занятие № 3

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

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

Задание 1. Построение линейчатой и круговой диаграмм

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

Откройте Практическую работу №1, перейдите на Лист 2.

1. Заполните таблицу:

10 самых больших стран мира по населению 2017

1. Выделите таблицу вместе с заголовком. Выберите вкладку

Вставка  Диаграммы  Вставить гистограмму Объемная гистограмма с группировкой:

Появившуюся диаграмму увеличьте и расположите так:

Аналогично создайте круговую и кольцевую диаграммы:

Задание 2. Самостоятельное построение диаграмм

Перейдите на Лист 1.

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

Можно выбрать стиль диаграммы, поменять цвет:

Для построения диаграммы Глубина океанов выделите первый столбец таблицы (Название), удерживая нажатой клавишу [Ctrl], выделите последний столбец (Наибольшая глубина, м).

Вы работали с файлом Практическая работа 1.Сохраните файл.

Практическое занятие № 4

Ввод формул. Вычисления по формулам

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

1. Введите числа от 1 до 10 в ячейки А1:А10 (воспользуйтесь функцией автозаполнения). Выделите ячейку А11, в которую поместим результат суммирования.

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

3. Нажмите Enter для принятия области, которую выбрала программа Excel. В ячейке А11 появится результат автосуммирования.

4. Результат автоматически изменится, если вы измените какое-нибудь число в ячейках А1:А10, измените область суммирования. удалите какую-нибудь строку в области суммирования. Если в процессе работы добавляются новые строки в области суммирования, то результат также изменяется автоматически. Попробуйте!

5. Введите числа от 1 до 10 в ячейки С1:С10 (воспользуйтесь функцией автозаполнения). Для суммирования нескольких диапазонов, например, С1:С3 и С6:С8, необходимо:

• выделить ту ячейку, в которой вы хотите получить сумму (С11), щелкнуть кнопку Автосумма -  – Excel примет решение, какую область включить в диапазон суммирования и выделит ее пунктирной движущейся рамкой (границей);

• выбрать мышкой свой диапазон (С1:С3) и удерживая нажатой клавишу [Ctrl], выделить второй диапазон (С6:С8), нажмите Enter. В ячейке С11 появится сумма диапазонов С1:С3 и С6:С8.

Основные правила создания формул

• Формула всегда начинается со знака «=» (равно). Формула отражается в строке формул. Аргументами формул обычно являются ссылки на ячейки (адреса ячеек).

• Адреса ячеек могут быть относительные или абсолютные.

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

• Абсолютная ссылка на ячейку или область ячеек будет всегда ссылаться на один и тот жеадрес строки и столбца. Формат записи абсолютной ссылки – $А$1. Адрес $А$1 вводится также, как относительный. Чтобы он стал абсолютным, после указания ячейки необходимо нажать клавишу F4 – перед именем столбца и строки появится знак «$». Попробуйте несколько раз нажать F4 и посмотрите, как будет меняться адрес ссылки.

Задание 2. Создание простых формул

Перейдите на Лист 2

1. Введите в ячейку А1 – число 25, в ячейку В1 – число 5, в ячейку С1 – число 8.

2. 

   Строка формул

   Выделите ячейку D1 и введите формулу =A14+B14-C14. За вводом формулы следите в строке формул.

Будьте внимательны, для ввода формулы выполните следующие шаги:

• выделите ячейку D1, начните ввод формулы со знака =;

• щелкните на ячейке А1, затем введите знак плюс +;

• щелкните на ячейке В14, введите знак минус -;

• щелкните на ячейке С14, нажмите Enter, в ячейке D14 получится результат.

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

Задание 3. Создание таблицы и расчет по формулам

1. Введите числовые данные в ячейки:

1. Оформите таблицу как показано на рисунке.

2. В ячейку Е2 введите формулу для расчета суммы на палатки

=С2*D2

и нажмите клавишу Enter – в ячейке Е2 отобразится результат.

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

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

1. Вычислите общий расход (ВСЕГО) в ячейкеЕ7.

Задание 4. Вычисление процентов

1. Введите числовые данные в ячейки, оформите таблицу, выполните все расчеты:

1. В ячейку F2 введите подзаголовок: «%». В этом столбце вычислим процент сотрудников с каждым видом образования.

Процент сотрудников с высшим образованием вычисляется по формуле:

количество сотрудников с высшим образованием / общее количество
сотрудников

1. В ячейку F3 введите формулу =Е3/Е$7.

Знак $ означает абсолютную ссылку, т.е. при копировании формулы номер строки в адресе этой ячейки не меняется. Нажмите Enter.

1. Выделите ячейку F3. Установите для нее числовой формат – процентный. Для этого на вкладке Главная выберите вкладку Число, формат Процентный .

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

Задание 5. Относительные и абсолютные ссылки

Заполните и оформите таблицу:

1. Пусть выплата за месяц вычисляется по формуле:

выплата за месяц (к выдаче) = базовая зарплата + премия - налог

Налог определен государством в виде фиксированного процента от суммы (базовая зарплата + премия). Число 0,13 в ячейке А11 означает, что начисления на денежную выплату (налог) составляют 13%.

В ячейку Е3 введите формулу =$A$11*(C3+D3). Нажмите Enter. В ячейке появится результат. Знак $ означает абсолютную ссылку, т.е. при копировании формулы адрес этой ячейки не меняется.

1. Выделите ячейку Е3 и, перетащив маркер заполнения, скопируйте формулу в ячейки Е4:Е8. В ячейках появятся результаты вычислений налога.

2. В ячейку F3 введите формулу =C3+D3-E3. Нажмите Enter. Выделите ячейку F3 и, при помощи маркера заполнения, скопируйте формулу в ячейки F4:F8. Вы увидите результаты вычислений.

3. Самостоятельно получите результат вычислений в ячейке F9 (ИТОГО). Сохраните файл.

Практическое занятие № 5

Стандартные функции. Фильтрация. Сортировка

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

Microsoft Excel содержит 400 встроенных функций:

математические и тригонометрические;

• статистические;

• финансовые;

• логические;

• инженерные; и др.

Задание 1. Использование стандартных функций. Скопируйте таблицу из практической работы №1 «Крупнейшие реки мира», добавьте три строки: «Наименьшее значение», «Наибольшее значение» и «Среднее значение»:

Выделите ячейку С14 и выполните команды Формулы  Вставить функцию

В диалоговом окне выберите категориюСтатистические, функцию МИН и нажмите ОК:

В ячейке С14 появится формула:

В появившемся окне нажмите ОК. Скопируйте содержимое ячейки С14 в ячейку D14. Получится так:

Аналогично найдите наибольшее значение (МАКС) и среднее значение (СРЗНАЧ). Не забудьте исправить аргументы функции: диапазон С3:С13 и В3:В13.

Проверьте результат работы:

Сохраните работу.

Задание 2. Фильтрация (выборка) данных

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

Скопируйте таблицу «Крупнейшие реки мира» на Лист 2. Выберите реки, с длиной более 5000 км:

Установите курсор внутри таблицы, исполните команды:

Данные  Фильтр

Щелкните левой кнопкой мыши по кнопкев столбце Длина. В появившемся окне щелкните Числовые фильтры  больше…

В диалоговом окне Пользовательский автофильтр введите значение 5000:

Нажмите ОК. Проверьте результат работы:

Задание 3. Фильтрация (выборка) данных. Выберите реки, площадь бассейна которых составляетот 1 500 до 3 500 км².

Скопируйте таблицу «Крупнейшие реки мира» на Лист 3. Установите курсор внутри таблицы, исполните команды: Данные Фильтр

Щелкните левой кнопкой мыши по кнопкев столбце Площадь бассейна. В появившемся окне щелкнитеЧисловые фильтры  между…

В диалоговом окне Пользовательский автофильтр введите значения:

Нажмите ОК. Проверьте результат работы:

Задание 4. Фильтрация (выборка) данных.(самостоятельно). Выберите реки, длина которых соответствует условию

5 000 < Длина реки < 6 500

Задание выполняйте на Листе 4.

Задание 5.Абсолютная адресация. Сортировка данных. На Листе 5 постройте таблицу, содержащую сведения о стоимости туристических путевок в разные страны мира.

1. Найдите стоимость путевок в рублях.

В ячейку С3 введите формулу для расчета стоимости путевки в рублях (используйте абсолютный адрес ячейкиВ1):

Скопируйте содержимое ячейки С3 в блок ячеек С4:С18 с помощью маркера заполнения.

Выделите блок ячеек С3:С18, установите формат Денежный:

Главная  Число (вкладкаЧисло):

Число десятичных знаков: 00000

Обозначение: нет

2. Произведите сортировку данных по Цене в долларах по убыванию. Выделите блок ячеек А3:С18, выполните команды:

Данные  Сортировка

В диалоговом окне Сортировка установите:

Сортировать по Цена в долларах

Порядок По убыванию

нажмите ОК.

Задание 6. Фильтрация (выборка), сортировка данных.(самостоятельно).

На Листе 6 постройте таблицу

Вычислите % россиян от общего числа туристов по формуле:

= Туристов из России / Всего туристов

Выделите блок ячеек D2:D16 и установите процентный формат (2 десятичных знака):

Произведите сортировку стран по алфавиту (выделяйте блок ячеек А2:D16), постройте диаграмму по % россиян от общего числа туристов. Сохраните работу. Позовите преподавателя.

Практическое занятие № 6

Построение графиков

Задание 1. Вычислите функцию y=2-х² при х[-3;3], шаг=0,5. Результат работы представьте в виде таблицы. Постройте график функции.

1. Создадим таблицу в Excel. В ячейке А1 напечатайте х, в ячейке В1 – y=2-x².

Задайте шаг изменения аргумента х. Используйте режим автозаполнения. Введите в ячейку А2 значение -3,0, в ячейку А3 -2,5. Выделите эти две ячейки и потяните за правый нижний угол «+» вниз по столбцу, пока не появится значение 3,0.

1. Во втором столбце в ячейке В2 наберите формулу, начиная со знака равно «=»=2-А2^2.

Нажмите Enter. В ячейке появится результат -7,0. Заполните остальные ячейки при помощи автозаполнения.

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

Вставка  Диаграммы  Точечная  Точечная с гладкими кривыми.

Расположите график рядом с таблицей.

Д обавьте подписи осей с помощью

Измените цвет и толщи-ну контура графика, выберите цвет и толщину осей. Можете сделать заливку.

Должно получиться так:

Задание 2. Перейдите на Лист 2.Самостоятельно постройте таблицу и график зависимости перемещения от времени при равномерном равноускоренном движении по формуле:

где начальная скорость v₀₌1,25 м/c, ускорение а= 0,3 м/с². Время меняется от 0 до 20 с с шагом 2 с.

Используйте функцию автозаполнения. Внимательно вводите формулу:

Постройте график. Введите название «Равноускоренное движение», добавьте названия осей.

Должно получиться так:

Сохраните файл.

Задание 3. Перейдите на Лист 3. Постройте график функции sinx.

1. Создадим таблицу в Excel.

В ячейке А1 напечатайте: х (град), в ячейке В1: х (рад) (в радианах), в ячейке С1 –y.

Задайте шаг изменения аргумента х. Используйте режим автозаполнения. Введите в ячейку А2 значение 0, в ячейку А3 –30. Выделите эти две ячейки и потяните за правый нижний угол «+» вниз по столбцу, пока не появится значение360.

1. Во втором столбце в ячейке В2 наберите формулу перевода углов из градусов в радианы, начиная со знака равно=A2*3,14/180.

Нажмите Enter. В ячейке появится результат – 0,523333.

Заполните остальные ячейки при помощи автозаполнения.

1. В третьем столбце в ячейке С2 поставьте знак равно «=» и щелкните f_x – Вставить функцию. Появится окно Вставка функции.

Выберите категорию: Математические. Выберите функцию SIN.

Нажмите ОК. Появится окно Аргументы функции. Щелкните мышкой по ячейкеB2. Нажмите ОК. В ячейке С2 появится 0. (sin0 = 0).

Установите числовой формат ячейки С2, число десятичных знаков 2:

Заполните остальные ячейки при помощи автозаполнения.

1. Выделите ячейки В1:С14, и выберите менюВставка  Диаграммы  Точечная  Точечная с гладкими кривыми. Появится график функции sinx.

Задание 4. Самостоятельно постройте график функции cosx.

Сохраните файл.

Практическое занятие № 7

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

(самостоятельно)

Создайте таблицу успеваемости:

1. Вычислите итоговую оценку (Среднее значение), округлите итоговые оценки до целого числа.

2. Подсчитайте общее количество «5», «4», «3» и «2» по зачету.

Для этого добавьте поясняющую информацию и используйте функцию СЧЕТЕСЛИ:

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

Сохраните работу. Позовите преподавателя.

Адрес публикации: https://www.prodlenka.org/metodicheskie-razrabotki/471926-laboratorno-prakticheskie-zanjatija-v-program