Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Автономная некоммерческая организация профессионального образования

«ПЕРМСКИЙ ГУММАНИТАРНО-ТЕХНОЛОГИЧЕСКИЙ КОЛЛЕДЖ»

(АНО ПО «ПГТК»)

Пояснительная записка к лабораторным работам

По дисциплине: Информатика

Выполнила: студентка 1 курса

очной формы обучения Баранова В.А.

Проверила: Зеленина Е.Г.

Старший преподаватель

СОДЕРЖАНИЕ

ВВЕДЕНИЕ

ЛАБОРОТОРНАЯ РАБОТА №1

ЛАБОРАТОРНАЯ РАБОТА №2

ЛАБОРАТОРНАЯ РАБОТА №3

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

ВВЕДЕНИЕ

Excel - это универсальная программа, которая позволяет работать с различными форматами данных. В Excel можно вести домашний бюджет, производить как простые, так и очень сложные расчеты, хранить данные, организовывать различные дневники, составлять отчеты, строить графики, диаграммы и многое-многое другое [3]. При помощи табличного редактора можно выполнять расчеты -- от самых простых до наиболее сложных. Программа подходит для составления бухгалтерских отчетов, с ее помощью строятся графики, диаграммы, создаются презентации. Приложение активно используется в сфере финансов, аудита. Кроме создания файлов с таблицами, сложных формул, за счет передового функционала приложения, в Excel можно также выполнять их редактирование, просматривать/редактировать рабочие книги, которые являются вложением в электронных сообщениях [2]. Excel - это мощная и удобная в работе система, которая широко используется студентами, инженерами, экономистами, бухгалтерами, управленческим персоналом.

ЛАБОРОТОРНАЯ РАБОТА №1

Целью данной работы являются:

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

2. Приобретение практических навыков по созданию и оформлению ЭТ, вводу данных, использованию функции Автосумма.

3. Приобретение и закрепление практических навыков по применению функций категории Математические с использованием Мастера функций.

Задание 1.1. Для того, чтобы выполнить расчет оплаты за квартиру в ТСЖ, включающую в себя плату за коммунальные услуги, исходя из тарифов (вода, газ, тепло), нужно:

Рисунок 1.1. - Расчет квартплаты

1) Заполнить таблицу «Расчет квартплаты» по мере вселения жильцов (рисунок 1.1).

2) Установить в столбец Площадь формат «кв.м», используя команду Вкладка Главная и Другие числовые форматы/Все форматы, а в тарифах - «р/чел.», «р/кв.м.»

3) D8 = B8*$В$4+C8*($В$2+$В$3) по этой формуле надо вычислить квартирную плату в рублях отдельно для каждой квартиры.

4) В строке Всего надо вычислить суммы (Вкладка Главная, сумма ?).

5) Выделить всю таблицу. Используя команду Вкладка Данные/Сортировка и фильтр/Сортировка сделать сортировку по номеру квартиры.

Задание 1.2. Построение круговой диаграммы для таблицы «Расчет квартплаты». Чтобы подготовить круговую диаграмму необходимо:

1) Выделить столбцы № кв. и Квартплату создать диаграмму с помощью вкладки Вставка/Диаграмма и Круговая (рисунок 1.2)

Рисунок 1.2. - Диаграмма

2) Чтобы проверить данные на диаграмме, надо щелкнуть правой кнопко мышки, установленной на диаграме, затем Выбрать данные и появится «Выбор источника данных», если необходимо можно внести изменения (Изменить). (Рисунок 1.3)

Рисунок 1.3. - Выбор источника данных

3) Подписать диаграмму и данные. Надо проставить проценты (%) квартплат каждого в общей стоимоти услуг. Для этого надо щелкнуть левой кнопкой мышки на диаграмму, затем на знак «+» Элементы диаграммы и выбрать.

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

1) Для начала выбрать команду Файл/Параметры Надстройки, нажать кнопку Перейти. Затем установить флажок в окне Поиск решения и нажать ОК. (Рисунок 1.4)



Рисунок 1.4. - Установка надстройки Поиск решения

После установки, во вкладке Данные/Анализ надо выбрать команду Поиск решения и в появившемся окне выполнить следующие действия:

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

? В строке Изменяя ячейки переменных указать ячейки B2:B4

? Выбрать кнопку Значения и ввести нужное значение общей суммы квартплаты. (Рисунок 1.5)



Рисунок 1.5. - Параметры Поиск решения

ЛАБОРАТОРНАЯ РАБОТА №2

Целью даной лабороторной работы является:

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

2. Преобретение и закрепление практических навыков по применению Элемента управления (Разработчика) и вкладки Формулы.

Для начала нужно подготовить основной лист:

1) Оформить лист в MS Excel (рисунок 2.1). Назвать лист Калькуляция.

2) В ячейке B2 установить дату, с помощью формулы СЕГОДНЯ ()

3) В ячейке A3 ввести текст Курс "у.е.". Чтобы в ячейке B3 установить формат «y.e.» необходимо:

Щелкнуть правой кнопкой мыши на эту ячейку. Выбрать команду Формат ячейки и открыть вкладку Число.

В списке Числовые форматы выбрать строку Все форматы.

В поле вводы ввести новый формат: # ##0,00”у.е.” и нажать ОК.

Рисунок 2.1. - Расчетный лист

2.1 Подготовить прайс-лист, на отдельном листе MS Excel под названиям «Кровельный материал», как на рисунке 2.2.

Рисунок 2.2. - Прайс-лист «Кровельного материала»

По такому принципу подготовить прайс-листы для «Утеплитель» и «Каркас» (Рисунок 2.3)

Рисунок 2.3. - Прайс-лист «Каркас»» и «Утеплитель»

2.2. Для того, чтобы установить элемент управления Поле со списком нужно установить «Разработчик», для этого необходимо:

1) Перейти во вкладку Файл, параметры Настроить ленту/Основные вкладки, поставить галочку на окне и нажать ОК (рисунок 2.4)

Рисунок 2.4. - Установка вкладки «Разработчик»

2) Чтобы выбрать значок Поле со списком, надо перейти во вкладку Разработчик/Вставка/ Выбрать нужный значок (рисунок 2.5) Затем растянуть ее в ячейке B6.

Рисунок 2.5. - Установка значка Поле со списком

4) Для того, чтобы значок работал надо щелкнуть правой кнопкой мышки на значок перейти в Формат объекта и выполнить действия, как на рисунке 2.6

Рисунок 2.6. - Окно форматирования объекта

5) Чтобы выделить ячейки столбца, в которых расположены виды Кровельного материала, нужно нажать кнопкой на стрелочку с права в поле Форматировать список по диапазону. Для того, чтобы развернуть до первоначального размера, следует снова нажать на стрелочку.

6) В поле Связь с ячейкой необходимо отметить ячейку F6.

7) В поле Количество строк списка, рекомендуется ввести число, которое должно быть больше размера списка-источника.

8) Для завершения после установки всех параметров, следует нажать кнопку ОК. Затем выбрать нужный Кровельный материал.

2.3. После выбора вида Кровельного материала, потребуется установить их цены в ячейку C6. Для этого необходимо:

1) Для начала выделить ячейку C6 и выбрать вкладку Формулы/Ссылки и массивы, затем ИНДЕКС (рисунок 2.7)

Рисунок 2.7. - Выбор вкладки ИНДЕКС

2) В окне Мастер функции, который появился надо выделить строку массив; номер строки; номер столбца, затем нажать ОК.

3) Должно появиться окно функции Индекс, где следует указать параметры этой функции.

4) В поле Массив стоит ввести диапазон ячеек с ценами листа Кровельный материал B2:B20 (установлено с запасом)

5) В поле Номер строки следует указать ячейку F6 листа Калькуляция (рисунок 2.8)

6) Для завершения действия следует нажать кнопку ОК, тогда в ячейке C6 должна появится цена выбранного вида Кровельного материала, а в ячейке F6 - порядковый номер.

Рисунок 2.8. - Окно «Аргументы функции»

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

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

Для того, чтобы установить Счетчик необходимо:

1) На листе Калькуляция вкладку Разработчик/Вставить/Элементы управления формы/Счетчик и установить его в ячейку D6.

2) Для того, чтобы задать параметры Счетчика нужно нажать правой кнопкой мышки на поле Счетчика и выбрать команду Формат объекта. В появившемся окне Форматирование объекта нужно ввести значения полей с клавиатуры.

3) Максимальное значение нужно задать больше числа строк в соответствующем листе материала.

4) Нажать ОК и в ячейке D6 листа Калькуляция появится значение счетчика.

Подобным образом, нужно установить элемент управления Счетчик для Утеплителя и каркаса.

Рисунок 2.9. - Окно «Форматирование объекта»

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

1) Вычислить Стоимость материала по формуле E6 = С6*D6, т.е. цену умножить на количество.

2) Чтобы вычислить общую стоимость, потребуется ввести формулу =СУММ(E6:E10).

Далее необходимо расчитать стоимость скидки. Для этого потребуется установить элемент управления Переключатель.

1) Установить его можно также, как и Счетчик или Поле со списком.

2) После установки нужно будет, нажать мышкой внутри рамки редактирования Переключателя и ввести текст - 0%

3) Для установки его параметров следует нажать првой кнопки мышки в поле переключателя и выбрать команду Формат объекта.

4) В окне Формат элемента управления надо выбрать вкладку элемент управления и ввести следующие значения (рисунок 2.10). Затем нажать ОК и Переключатель выделится, в ячейке F14 появится цифра 1.

Рисунок 2.10 - Формат элемента управления Переключатель

Таким же способом, потребуется установить второй Переключатель для скидки 10%(т.е. надо будет ввести текст 10%), в той же ячейке B14 (Рисунок 2.11).

Если все сделать правильно, при нажатии мышкой на Переключатель - 0%, в ячейке F14 появится 1, а если - 10% цифра 2.

Рисунок 2.11. - Переключатель

Для вычисления стоимости скидки, нужно в ячейку E14 ввести формулу: =E12*0,1*(F14-1), а при переключателе - 10% будет появляться значение равное от общей стоимости.

Для строки Доставка необходимо установить элемент управления Флажок (рисунок 2.12). Чтобы его установить нужно выполнить следующие действия:

1) Для начала установить кнопку Флажок в ячейку B16;

2) Ввести заголовок - Нужна;

3) Для определения параметров элемента управления Флажок нужно будет:

? Нажать правой кнопкой мышки в поле Флажок и выбрать Формат объекта;

? В окне Формат элемента управления/Элементы управления нужно сделать соответствующие установки, такие как у элемента управления Переключатель, но в строке Связь с ячейкой надо сделать ссылку на ячейку $F$16 и нажать кнопку ОК. Тогда в ячейке F16 должно появится значение - ИСТИНА (значение установленного Флажка), что будет значить - доставка нужна.

4) Далее надо будет определить стоимость доставки, для этого необходимо:

? В ячейку E16 ввести формулу: E16 = ЕСЛИ(F16=ИСТИНА;500;0). Функция ЕСЛИ можно задать с помощью Мастера функций, а также ввести с клавиатуры.

Если все сделать правильно, то при установке флажка в ячейке C16 значение функции ЕСЛИ будет равна 500, а при снятом флажке будет 0. А в ячейке F16 будет ИСТИНА или ЛОЖЬ.

Рисунок 2.12 - Элемент управления Флажок

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

1) Ввести в ячейку E18 формулу: E18 = E12-E14+E16

2) В ячейку E19 следует ввести формулу персчета стоимости товара в долларах E19=E18/B3.

ЛАБОРАТОРНАЯ РАБОТА №3

табличный редактор excel

Целью данной Лабораторной работы является:

1. Получения практических навыков по использованию Автофильтра и Мастера функции, Сводной таблицы, вкладки Конструктор.

2. Приобретения навыков в работе с базой данных.

3. Приобретения практических навыков по использованию Диаграмм и Гистограмм.

1. Создание рабочего листа базы данных.

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

1) Открыть новый чистый лист Excel и назвать его Продажи.

2) В ячейки A1, A2 ввести такой же текст, как на рисунке 3.1.

3) В отдельных ячейках C4:C6 нужно набрать значения курса y.e.

4) Ввести имена полей (столбцов).

Рисунок 3.1. - Рабочий лист «Продажи»

5) Далее надо будет заполнить базу данных (столбцы: Дата, Кому, Менеджер, Сумма,y.e).

2. Чтобы легче было заполнять данные столбцов, необходимо ввести значок создания Формы на панель быстрого доступа. Для этого необходимо:

? Выбрать кнопку Файл/Параметры Excel/Настроить ленту/Все команды/Форма и нажать кнопку Добавить (рисунок 3.2)

Рисунок 3.2. - Установка значка создания Форма

После установки с помощью Формы можно вводить данные, для этого надо выполнить следующие действия:

1) Нажать по любой ячейке таблицы, чтобы Excel определил, где находится данная таблица.

2) Нажать на значок Формы в строке (панели) быстрого доступа, и тогда откроется Форма с названиями столбцов и данные 1-ой строки таблицы (рисунок 3.3).

3) Для того, чтобы ввести определенную информацию надо нажать на кнопку «Добавить».

4) Далее нужно будет заполнить форму данными, после заполнения нажать «Добавить».

5) Закончив ввод всех данных, нажать на кнопку «Закрыть».

Рисунок 3.3. - Окно Формы

3. Вычисление суммы продаж в рублях

Для заполнения поля Сумма, руб. необходимо в ячейку E9 ввести формулу: Е9= ЕСЛИ (А9=”январь”;$С$4;ЕСЛИ (А9=”февраль”;$С$5;$С$6))*D9 и скопировать ее вниз до конца таблицы. Также установить «денежный формат».

4. Вычисление общей суммы продаж

Для вычисления общей суммы продаж в y.e. и руб., необходимо воспользоваться Автосуммой и в ячейках D2 и E2 установить формат «у.е.» и «денежный».

5. Создание Автофильтра. Для этого необходимо:

? Выделить ячейки A8:E8, содержащих заголовки столбцов.

? Во вкладке Данные/Сортировка и фильтр, затем нажать кнопку Фильтр.

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

Рисунок 3.4. - Автофильтр

6. Создание промежуточных итогов

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

? С помощью Мастер функций в ячейку D3 следует ввести функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ

? В появившемся окне функции надо сделать следующие установки

? В поле Номер функции надо ввести 9

? В поле Ссылка надо ввести диапазон ячеек D9:D100, затем нажать на кнопку ОК (рисунок 3.5)

? В завершении необходимо установить формат «у.е.». В ячейке D3 должна быть записана формула: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; D9:D100)

? В ячейку E3 необходимо будет скопировать введенную формулу из ячейки D3 и установить формат «денежный».



Рисунок 3.5. - Установка функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ

7. Структура выборки из базы данных

Для создания выборки из базы данных необходимо использовать функцию БДСУММ. Это функция будет выполнять операции, только с данными, соответствующими заданным критериям.

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

? Выбрать новый лист и назвать его Менеджеры. Заполнить диапазон ячеек A1:C4, как на рисунке 3.6.

? Затем в ячейку A5 надо ввести формулу для вычисления суммы продаж И.И. Иванова. Для этого надо с помощью Мастера функций выбрать функцию БДСУММ (из категории Работа с базой данных).

Рисунок 3.6. - Пример листа об объемах продаж

? В появившемся окне функции следует задать параметры, как на рисунке 3.7. Затем нажать кнопку ОК.

Рисунок 3.7. - Аргументы функции БДСУММ

Если все сделать правильно, в ячейке A5 будет записана формула:

А5= БДСУММ(Продажи! $A$8:$E$100;"Сумма,у.е.";A3:A4). Так же будет записан результат всех продаж менеджера Иванова И.И. Далее из ячейки A5 потребуется скопировать формулу в ячейки B5 и C5.

В завершении нужно будет добавить новые записи в базу данных, чтобы ввести изменения о продажах на листе «Продажи».

8. Построение круговой диаграммы

Для построения круговой диаграммы необходимо:

? Выбрать лист «Менеджеры» (рисунок 3.6), затем выделить диапазон ячеек A4:C5.

? Перейти во вкладку Вставка/Диаграммы/Круговая. В меню выбрать нужный тип диаграммы.

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

Пример круговой диаграммы на рисунке 3.8.



Рисунок 3.8. - Круговая диаграмма

9. Создание сводной таблицы

Для создания сводных таблиц необходимо использовать - Мастер сводных таблиц. Для удобства нужно вывести его на панель быстрого доступа из вкладки Вставка/Таблицы. После этого необходимо нажать на значок Сводной таблицы , и запустится окно Сводная таблица из таблицы или диапазона (рисунок 3.9). Далее нажать на кнопку ОК.

Рисунок 3.9. - Окно создание Сводной таблицы

На листе справа появится редактор списка полей сводной таблицы (рисунок 3.10) необходимо:

? Перетащить мышкой поле Кому в область Строк

? Поле Менеджер также перетащить в область Столбцов

? Поле Сумма, y.e. в область Значений.

Рисунок 3.10. - Редактор полей сводной таблицы

Если все выполнить правильно, то на рабочем листе будет размещена Сводная таблица (рисунок 3.11). Необходимо назвать этот лист Св-табл-1 и установить формат «у.е.».

Рисунок 3.11. - Сводная таблица на рабочем листе

10. Изменения в сводной таблице

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

? на рабочем листе Св-табл-1 надо активизировать любую ячейку сводной таблицы;

? выбрать вкладку Анализ сводной таблицы/Параметры/Изменить источник данных/Обновить;

? в появившемся окне в поле Таблица или диапазон нужно изменить диапазон ячеек данных, измененной базы.

10.1. Изменение структуры сводной таблицы

Для того, чтобы изменять поля, менять местами строки и столбцы в сводной таблице, необходимо:

? Выделить любую ячейку и в редакторе списка полей сводной таблицы, поменять местами поля Менеджер и Кому (рисунок 3.12).

? Затем необходимо перетащить мышкой поле Сумма, руб. в область Значений. Установить «денежный» формат в сводной таблице (рисунок 3.12)

Рисунок 3.12. - Сводная таблица после изменений

? В сводную таблицу можно добавлять и удалять какое-либо поле. Для добавления поле Дата в сводную таблицу, необходимо:

? Выделить любую ячейку и в появившемся окне справа редакторе списка полей, нужно перетащить поле Дата в область Строк Рисунок 3.13).

Рисунок 3.13. - Сводная таблица после добавления поля Дата

10.2. Форматирование сводной таблицы

Для форматирование сводной таблицы необходимо:

? Выделить сводную таблицу, затем во вкладке Главная нажать на кнопку Форматировать как таблицу

10.3. Добавление гистограмм

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

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

? Выбрать вкладку Главная/Условное форматирование.

? В открывшемся меню выбрать раздел Гистограммы и подходящий цвет. Пример на рисунке 3.14.



Рисунок 3.14. - Сводная таблица после добавления Гистограммы

11. Построение диаграммы для сводной таблицы

Перед построением диаграммы необходимо удалить из сводной таблицы данные общих итогов. Для это необходимо:

? Выделить любую ячейку таблицы

? Зайти во вкладку Конструктор

? Нажать на кнопку Макет и выбрать Общие Итоги

? В появившемся меню выбрать Отключить для строк и столбцов

После выполнения этих действий необходимо построить гистограмму, с помощью Мастера диаграмм. Пример на рисунке 3.15.

Далее добавить Общие Итоги таким же способом, как и удалять (Включить для строк и столбцов).

Также необходимо редактирование диаграммы.

Рисунок 3.15. - Гистограмма сводной таблицы

ЗАКЛЮЧЕНИЕ

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

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

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ

1. Методические указания и индивидуальные задания по дисциплине Информатика2 Численные методы в строительстве для студентов заочников (бакалавров) строительных специальностей / сост.: Т. Б. Пермякова, М. Е. Лаищева; Перм. нац. исслед. политехн. ун-т, Строит. фак, Каф. Строит. конструкции и вычисл. механика. - Пермь: [Лаб. ИСТ СТФ ПНИПУ], 2017. - 62 с. (дата обращения - 21.05.2022)

2. COPYRIGHT © 2021 - WWW.MS-OFFICE-EXCEL.RU. Сайт. - https://ms-office-excel.ru/ (дата обращения - 18.05.2022)

3. © Microsoft 2022. Сайт. URL:https://www.microsoft.com/ru-ru/microsoft-365/excel (дата обращения - 18.05.2022)

Размещено на Allbest.ru