Информатика_Excel_лаб_раб. Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.

• Название: Л. Г. Чернова пакеты прикладных программ раздел iii. Microsoft Excel лабораторный практикум новотроицк 2010 2 удк 681. 31 378 ббк 32.
• Дата: 22.12.2021
• Размер: 1.91 Mb.
• Имя файла: Информатика_Excel_лаб_раб.pdf
• Тип: Практикум; #313176
• страница: 3 из 8

Число. При изменении формата числа ячейки изменяется только способ представления данных в ячейке, ноне сами данные. Если ячейка отображается в виде символов ####, это означает, что столбец недостаточно широк для отображения числа целиком в установленном формате.
Упражнение 2.3. На основе последнего столбца основной таблицы сформируйте таблицу с различными форматами исходных значений столбца Установите масштаб 75%.
 Скопируйте значения у из столбца E в столбцы КМ. При вставке воспользуйтесь контекстным меню, вызываемым правой кнопкой мыши, где нужно выбрать команду Специальная вставка Значения В столбце К задайте формат, в котором отражаются две значащие цифры после запятой 0,00.
 В столбце L задайте формат Экспоненциальный .
 В столбце М задайте формат Процентный.
 В столбце N установите собственный(Пользователь-
ский) формат - четыре знака после запятой контекстное меню, Формат ячеек Число все форматы Тип / 0,0000/ ОК.
 Оформите блок Кв стиле оформления основной и вспомогательной таблиц (заголовок, обрамление, заполнение, шрифт.
 В результате получится таблица, изображенная на рисунке. охраните файл под именем tab2.

33 Рисунок 2.2 – Установка форматов данных Защита ячеек В Excel можно защитить от изменения всю рабочую книгу, лист или некоторые ячейки. Защита делает невозможным изменение информации, до тех пор, пока она не отключена. Обычно защищают данные, которые не должны изменяться (расчетные формулы, заголовки, шапки таблиц. Установка защиты выполняется в два действия
1) отключают защиту (блокировку) с ячеек, подлежащих последующей корректировке
2) включают защиту листа или книги. Упражнение 2.4. Защитите все данные листа за исключение вспомогательной таблицы для организации возможности изменения этих данных. Шаг 1. Отключение блокировки (снятие защиты) ячеек

34
 Выделите блок H4:J4. Выполните команду Главная Ячейки Формат/Блокировать ячейку или в контекстном меню выберите команду Формат ячеек/Защита ив диалоговом окне отключите параметр Защищаемая ячейка (убратьзнак [v] в окне Защищаемая ячейка. Шаг 2. Блокировка (защита) листа или книги
 Выполните команду Главная Ячейки Формат Защитить лист. (Для отключения блокировки листа выполняются команды Главная Ячейки Формат Снять защиту листа. Таким образом, можно защитить от изменений информацию, которая не должна меняться (заголовки, основная таблица полностью, шапка вспомогательной таблицы. В результате действий заблокируется весь лист, кроме блока H4:J4.
 Попробуйте изменить значенияв ячейке Ас на 10. Это невозможно.
 Измените значение шага во вспомогательной таблице сна Это возможно. В основной таблице произошел пересчет.
 Измените текст step в ячейке I3 на текст шаг. Каков результат Почему
 Верните начальное значение шага 0,2.
 Назовите Лист – Табулирование Для этого щелкните по ярлычку листа правой кнопкой мыши и выберите Переименовать Снимите защиту с листа. Выполните команду Главная Ячейки Формат Снять защиту листа. Функции в EXCEL. Мастер функций Функции предназначены для упрощения расчетов и имеют следующую форму y=f(x), где у - результат вычисления функции, х — аргумент, f — функция. Пример содержимого ячейки с функцией = A5+ SIN(C7), где А — адрес ячейки sin() - имя функции, в круглых скобках указывается аргумент С — аргумент (число, текст и т.д.), в данном случае ссылка на ячейку, содержащую число.

35 Некоторые функции
КОРЕНЬ(число) — вычисляет положительный квадратный корень из числа. Например КОРЕНЬ(25)=5.
SIN(число) — вычисляет синус угла, измеренного в радианах. Например sin(.883)=0.772646.
МАКС(список) — возвращает максимальное число списка.
Например: МАКС, 39, 50, 28. 67, 43)=67.
СУММ(список) — возвращает сумму чисел указанного списка блока. Например СУММА) подсчитывает сумму чисел в трехстах ячейках диапазона А А. Рисунок 2.3 Окно мастера функций За часто используемой функцией суммирования закреплена кнопка Автосумма на вкладке Формулы Библиотека функций Для вставки функции в формулу можно воспользоваться Мастером функции, который вызывается на вкладке Фор-
мулы/Вставить функцию (элемент управления с изображением
f
x
). Появится диалоговое окно, рисунок 2.3, в котором необходимо выбрать Категорию, затее Функцию, а затем нажать на

36 кнопку ОК. Второе диалоговое окно (второй шаг Мастера функций) позволяет задать аргументы к выбранной функции, рисунок 2.4. Рисунок 2.4 – Второй шаг Мастера функций Упражнение 2.5. По последнему столбцу основной таблицы найдите сумму значений y, их среднее арифметическое, максимальное и минимальное значения y.
 Выберите элемент управления f
x,
, вкладки Формулы Выберите категорию Полный алфавитный перечень. Посмотрите, как работают функции СУММ, МИН, МАКС.
 Подсчитайте сумму вычисленных значений у и запишите ее в ячейку E25. Используйте кнопку

Автосумма, Вкладки Формулы.
 В ячейку D25 запишите поясняющий текст Сумма у , рисунок Оформите нахождение среднего арифметического вычисленных значений у Воспользуйтесь Мастером функций установите курсор в ячейку E26, далее выполните команды Формулы Вставить функцию Полный алфавитный перечень / Срзнач /
ОК/ E4:E24/Ок.
 Занесите в ячейку D26 поясняющий текста в E26 — среднее значение, рисунок 2.5.
 Оформите нахождение минимального и максимального значений у, занеся в ячейки D27 и D28 поясняющий текста в

37 ячейки E27 и E28 - минимальное и максимальное значения. Воспользуйтесь Мастером функций Формулы Вставить функцию Полный алфавитный перечень / Макс Е4:Е24/Ок. Оформите блок ячеек D25: E28.
 Задайте рамку для блока D25: E28.
 Заполните этот блок тем же фоном, что и у шапки таблицы.
 Поясняющие подписи в ячейках D25: D28 оформите шрифтом Arial, полужирным с выравниванием вправо, рисунок. Рисунок 2.5 – Дополнение основной таблицы Использование абсолютной и относительной адресаций при решении задач Упражнение 2.6. Имеется список персонала и окладов, рисунок, рассчитать премию в размере 20% от оклада, предусмотреть, что процент премии может измениться и тогда потребуется перерасчет. Рисунок 2.6 – Исходные данные упражнения 1.5
 Перейдите на свободный рабочий лист и дайте ему имя Премия
 Введите исходные данные, рисунок 1.5.
 В таблице нужно разместить процент премии. Вставьте перед списком 2 пустые строки. Для этого выделите мышью строку
1 и не снимая выделения выполните Главная / Ячейки Вставить Вставить строки на лист - 2 раза. Теперь список располагается в диапазоне А3:В7. В ячейку В введите «% премии, в ячейку С введите 20%.
 В ячейку С внесите заголовок «Премия»,рисунок 2.7.
 Сформируйте формулу для расчета премии. Введите в ячейку С формулу =В4*С1. В ячейке получится значение 1000.
 Скопируйте данную формулу на диапазон С5:С7. Проанализируйте полученные данные. Просмотрите формулы в ячейках диапазона С5:С7. В формуле ячейки С, которую копировали использовалась относительная ссылка на ячейку С, которая не фиксирует при копировании ссылку на конкретную ячейку.

39 Рисунок 2.7 – Дополненные таблицы
 Сделайте ссылку на ячейку С абсолютной для этого поставьте курсор в формуле рядом со ссылкой на ячейку Си нажмите F4. Формула приобретет вид =В4*$С$1.
 Скопируйте данную формулу на диапазон С5:С7, рисунок
2.8. Рисунок 2.8 – Результат упражнения 1.5
 Задайте процент премии 15 %. Проанализируйте результат. Использование логических и математических функций при решении задач Логическая функция ЕСЛИ позволяет организовать выполнение действия в зависимости от какого-либо условия.

40
ЕСЛИ(логическое_выражение; значение_если_истина;
значение_если_ложь) Логическая функция И используется тогда, когда нужно проверить, выполняются ли несколько условий ОДНОВРЕМЕННО И логич_знач1; логич_знач2; ... ; логич_знач30 ) Математическая функция ОСТАТ возвращает остаток отделения ОСТАТ(Число; Делитель) Математическая функция ЦЕЛОЕ округляет аргумент до ближайшего меньшего целого ЦЕЛОЕ(выражение) Математическая функция ABS возвращает модуль (абсолютную величину) числа выражение) Функция ДНЕЙ возвращает количество дней между двумя датами ДНЕЙ360(нач_дата, кон_дата) Упражнение 2.7. Выполнитерасчет стоимости аренды автомобиля ГАЗ 3307, используя данные с рисунка Рисунок 2.9 – Исходные данные
 Перейдите на Лист. Для нахождения числа дней аренды в D12 воспользуемся функцией ДНЕЙ из группы функций Дата и время ДНЕЙ

41
 Время аренды, значение ячейки Е, рассчитаем по формуле =АВS(Е8-Е10).
 Для ячейки D12 создайте пользовательский формат Глав-
ная/Формат ячеек Число Все форматы в поле Тип наберите
[>=5]0” дней дня день
 Осталось подсчитать общую стоимость за аренду. Так как автомобиль был в прокате более недели, то найдите стоимость за дни аренды, выбрав функцию ЕСЛИ из группы функций Логические в вспомогательной ячейке Н получим формулу
=ЕСЛИ(И(D12>=1;D12<7);D12*D18;ЕСЛИ(D12>=7;ЦЕ-
ЛОЕ(D12/7)*D19+ОСТАТ(D12;7)*D18;0) Для формирования формулы используйте только мастер функции.
 Учитывая часы аренды введите в ячейку Е формулу ЕСЛИ Е H8+D17;H8 ) В результате получатся данные, изображенные нари- сунке 2.10. Рисунок 2.10 – Конечные результаты
 Назовите Лист – Аренда, сохраните файл tab2.
III Задания для самостоятельной работы

42 Задание 1. Рассчитайте таблицу значений функции
2 2
)
(
k
x
x
f


, где х меняется на интервале [–10,10] с шагом
0,5 при k=15. Решение оформить в виде таблицы 2.3 Таблица 2.3 – Шаблон таблицы ТАБЛИЦЫ основная вспомогательная х f(x)

x k Задание 2. Создать Поисковую систему, которая на базе Результатов аттестации в соответствии с фамилией и дисциплиной выводит соответствующую оценку студента, таблицы 2.3 и 2.4. Таблица 2.3 - Результаты аттестации
ФИО Математика Информатика История Ин. язык Алексеев АН.
3 3
4 4 Иванов К.С.
4 4
4 4
Кореев B.A.
4 3
5 5
Малкин С.Н.
4 5
4 4 Петров А.К.
2 3
3 3 Петухов А.В.
5 5
5 3 Сидоров В.В.
3 2
3 4
Смирнов Т.И.
3 4
4 5 Таблица 2.4 - Поисковая система Примечания
1. Фамилия студента и дисциплина для поиска выбираются из
ФИО: Петухов А.В. Дисциплина
Ин. язык Оценка
3

43 списка. Списки задаются с помощью Данные Работа сданными Проверка данных...Источником строк в поле Тип данных является Список столбец фамилий и строка соответственно с названиями дисциплин из таблицы Результаты аттестации. Для поиска Оценки используются функции ИНДЕКС(мас-
сив; номер_строки; номер_столбца) и ПОИСКПОЗ().
3. Для функции ПОИСКПОЗ() требуется установить аргумент тип _ сопоставления равным 0. Задание 3. Используя справочную таблицу Проценты по вкладам, таблица 2.5, сформировать таблицу Вкладчики, таблица, в которой на основе суммы вклада, даты открытия счета и срока на который положен вклад определяется дата закрытия счета и доход по вкладу. Таблица 2.5 - Справочная таблица Проценты по вкладам месяц от
3 6
9 12 10 р.
3%
5%
6 %
7%
5 р.
5%
7%
8%
9%
20 р.
7%
8%
10%
11%
50 р.
9%
10%
11%
12% Таблица 2.6 - Вкладчики
ФИО Сумма Дата открытия Срок, мес Дата закрытия Доход Иванов А.В. 12000 25.02.2010 8
26.10.2010 840,00 р. Алексеев АН 20000 13.05.2010 12
Смирнов ОН 14000 23.01.2010 4 Алексеев АН 2000 18.11.2010 10 Примечания. Срок вклада в справочной таблице задается в числовом формате. Дата закрытия счета вычисляется с помощью функции ДАТА-
МЕС().
3. Для определения дохода процент по вкладу вычисляется на основе справочной таблицы с помощью функций ИНДЕКС и ПОИСКПОЗ(), причем в последней необходимо установить аргумент тип сопоставленияравным 1.
IV Контрольные вопросы
1 Перечислите виды адресации ячеек. Чем они отличаются В какой ситуации используется каждый вид адресации
2 Что предполагает форматирование таблиц
3 Как выполнить защиту данных в Microsoft Excel?
4 Как устанавливается формат числа в Excel?
5 Как создать пользовательские форматы
6 Для чего предназначен мастер функций
7 Перечислите некоторые из использованных Вами функций и их назначение.

45 Лабораторная работа № 3 Тема Создание, редактирование и форматирование диаграмм в Microsoft Excel
I Цель работы Научиться на основе таблиц строить диаграммы освоить приемы форматирования и редактирования диаграмм.
II Теоретическое введение и упражнения Одним из самых впечатляющих достоинств Excel является способность превращать абстрактные ряды и столбцы чисел в привлекательные информативные графики и диаграммы. Диаграммы — это удобное средство графического представления данных. Диаграммы являются средством наглядного представления данных и облегчают выполнение сравнений, выявление закономерностей и тенденций данных. Диаграммы создают на основе данных, расположенных на рабочих листах. Как правило, используются данные одного листа. Это могут быть данные диапазонов как смежных, таки не смежных ячеек. Несмежные ячейки должны образовывать прямоугольник. При необходимости, в процессе или после создания диаграммы, в нее можно добавить данные, расположенные на других листах. Диаграмма может располагаться как графический объект на листе сданными (необязательно на том же, где находятся данные, взятые для построения диаграммы. На одном листе сданными может находиться несколько диаграмм. Диаграмма может располагаться на отдельном специальном листе. Диаграмму можно напечатать. Диаграмма, расположенная на отдельном листе, печатается как отдельная страница. Диаграмма, расположенная на листе сданными, может быть напечатана вместе сданными листа или на отдельной странице. Диаграмма постоянно связана сданными, на основе которых она создана, и обновляется автоматически при изменении исходных данных. Более того, изменение положения или размера

46 элементов данных на диаграмме может привести к изменению данных на листе. В Excel можно создавать различные диаграммы. Всего существует типов встроенных диаграмм, каждый из которых имеют еще множество разновидностей (видов. Выбор типа диаграммы определяется задачами, решаемыми при ее создании. Один из типов диаграмм является стандартным, то есть он используется по умолчанию при создании диаграмм. Обычно стандартной диаграммой является плоская гистограмма. Построение диаграмм Перед созданием диаграммы следует убедиться, что данные на листе расположены в соответствии с типом диаграммы, который планируется использовать. Данные должны быть упорядочены по столбцам или строкам. Необязательно столбцы (строки) данных должны быть смежными, но несмежные ячейки должны образовывать прямоугольник. При создании гистограммы, линейчатой диаграммы, графика, диаграммы с областями, лепестковой диаграммы, круговой диаграммы можно использовать от одного до нескольких столбцов (строк) данных. При создании диаграммы типа "Поверхность" должно быть два столбца (строки) данных, не считая столбца (строки) подписей категорий. При создании круговой диаграммы нельзя использовать более одного столбца (строки) данных, не считая столбца строки) подписей категорий. Представим графически данные, которые использовались в лабораторной работе № 1 по Microsoft Excel. Упражнение 3.1. Постройте диаграмму на основе данных о населении г. Новотроицк представленных на рисунке 1.7 (лабораторная работа 1).

47
 Вызовите файл tab1.xlsx.
 Выделите фрагмент таблицы, для которого создается диаграмма, блок G16:M17.
 На вкладке Вставка в группе Диаграммы щелкните по кнопке с нужным типом диаграмм ив галерее выберите конкретный вид диаграммы, Гистограмма Гистограмма с группировкой, рисунок 3.1. Рисунок 3.1 – Выбор вида диаграммы На листе будет создана диаграмма выбранного вида, рисунок Если в группе Диаграммы не отображается подходящий тип и вид диаграммы, щелкните по кнопке Другие диаграммы. Для создания диаграммы стандартного типа достаточно выделить фрагмент листа и нажать клавишу F11. Для удаления диаграммы достаточно выделить ее и нажать клавишу Delete.

48 Рисунок 3.2 – Созданная диаграмма Настройка и редактирование диаграмм Все действия выполняются с выделенной диаграммой или с ее выделенными элементами. Упражнение 3.2. Оформите диаграмму в соответствии с рисунком (добавьте заголовок, подписи осей, уберите легенду.
 Для выделения диаграммы щелкните мышью в любом месте области диаграммы. Признаком выделения являются рамка диаграммы. На рамке имеются маркеры, расположенные по углами сторонам рамки.
 С помощью вкладки Конструктор группы Макеты диаграмм установите вид диаграммы – Макет 1.
 Задайте название диаграммы на Население Новотроицка,

49 выделив предварительно заголовок диаграммы двойным щелчком мыши.
 Уберите легенду диаграммы. Вкладка Макет, группа Подписи, элемент управления Легенда. В раскрывающемся списке выберите Не добавлять легенду.
 Установите подпись оси с числовыми значениями. Вкладка Макет, группа Подписи, элемент управления Названия осей. В раскрывающемся списке выберите Название основной вертикальной оси. Замените появившийся текст на тыс.чел. , рисунок 3.3. Рисунок 3.3 – Результ редактирования диагаммы
 Листу 1 присвойте имя Гистограмма Изменение диаграмм Если что-то не устраивает в построенной диаграмме, то ее можно изменить.

50
1 Изменение размеров диаграммы Часто бывает весьма затруднительно определить наилучшие размеры внедренной диаграммы до того, как вы увидите представленные на ней данные. Поэтому часто приходится изменять размеры и пропорции внедренной диаграммы для того, чтобы придать ей хороший вид или облегчить ее редактирование. Упражнение 3.3. Увеличьте диаграммы.
 Выделите диаграмму, сделав одиночный щелчок по диаграмме. На рамке диаграммы появятся маркеры выделения - маленькие черные квадратики в углах и на серединах сторон рамки.
 Захватив курсором мыши рамку, переместите диаграмму.
 Установите указатель мыши на маркер справа (указатель мыши при этом изменяет свою форму на двунаправленую стрелку) и увеличьте размеры области построения диаграммы. Протаскивание маркера, расположенного на середине стороны, позволяет изменять вертикальные или горизонтальные размеры диаграммы. Протаскивание углового маркера позволяет изменять вертикальные и горизонтальные размеры диаграммы одновременно.