МИНОБРНАУКИ РОССИИ
Санкт-Петербургский государственный
Электротехнический университет
«ЛЭТИ» им. В.И. Ульянова (Ленина)
КАФЕДРА МО ЭВМ
отчет
По лабораторной работе №5
по дисциплине «Информационные технологии и базы данных в ПК»
Тема: Электронная таблица MS Excel
| Студентка гр. 0704 | Янченко А.В. | |
| Преподаватель | Бушмакин А.Л. |
Санкт-Петербург
Цель работы:
Практическое освоение способов создания, редактирования, оформления и эксплуатации электронных таблиц в программе MS Excel.
Основные теоретические положения:
1. Общие правила работы
2. Настройка таблицы
3. Построение диаграмм
4. Эксплуатация электронной таблицы
5. Работа с электронной таблицей как с базой данных
6. Индивидуальное задание
Экспериментальные результаты:
1. Общие правила работы
Запускаем приложение Microsoft Excel при помощи ярлыка, расположенного на Рабочем столе или любым другим удобным нам способом. Осваиваем систему помощи программы Excel. Для этого щелкаем по знаку «?» и таким образом переходим в справочное меню:
Затем переходим в раздел «Создать», выбираем шаблон «Пустая книга» и открываем его.
Чтобы переименовать его, кликаем по иконке «Лист 1» и выбираем переименовать:
Согласно заданию, задаем новое название – «Годовой доход»:
После этого начинаем вводить данные в ячейки согласно образцу. В соответствующих ячейках вводим свою фамилию и имя, а также дату составления отчета. В ячейке с датой указываем нужный формат - для этого правой кнопкой мыши кликаем по ячейке, далее нажимаем «Формат ячеек», в разделе «Число» выбираем «Дата».
Для ячеек с большими числами применяем экспоненциальный формат. Для этого выделяем необходимые ячейки, затем нажимаем на выделенную область правой кнопкой мыши и переходим в раздел «Формат ячеек» и выбираем «Экспоненциальный», число десятичных знаков - 0
Вводим значение и обновляем ячейку:
Для ячеек «Итого» и «Получено за год» применяем автосумму. Нажимаем на ячейку, на панели управления выбираем «Автосумма»:
Далее применяем автосумму к другим соответствующим ячейкам. Для этого выбираем ячейку, где автосумма уже применена, наводим курсом на её правый нижний угол и растягиваем на необходимые ячейки. Итог:
То же самое делаем с ячейками «Получено за год»:
Добавляем в ячейку I6 заголовок «Налог»:
Вставляем дополнительную строку перед строкой 5 - нажимая правой кнопкой мыши по 5 строке и переходя в раздел Вставить:
Вводим в ячейку А5 нужный текст - доход, облагаемый налогом по минимальной ставке:
В ячейку Н5 вводим значение 1000000:
В разделе Формула находим категорию Логические (формулы, которые вычисляют размер подоходного налога по ставке 12 %) Вводим в соответствующие ячейки следующие данные:
Итог:
Применяем это ко всем остальным соответствующим ячейкам:
Для Итога применяем автосумму уже описанным выше и знакомым нам способом:
Присваиваем ячейке H5 имя ПорогЗнач, кликая по ней правой кнопкой мыши и переходя в раздел «Присвоить имя»:
Исправляем формулы так, чтобы в них содержался не адрес ячейки H5, а её имя.
Исправляем подобным образом значение других ячеек и получаем следующее:
Сохраняем файл под именем test1.
2. Настройки таблицы
Запускаем Excel и открыаем файл test1.
Применяем нужный формат, для этого выделяем диапазон ячеек, кликаем по выделенной области правой кнопкой мыши и переходим в раздел Формат ячеек. Выбираем «все форматы» и вводим нужное значение.
Для нулевых значений применяем красный цвет в разделе Формат ячеек. Для отрицательных значений – ошибку.
Далее форматируем таблицу. Для этого в разделе «Границы» выбираем нужную толщину и также применяем цвет согласно образцу (в данном случае зеленый):
Итог:
Выделяем ячейки, где данные необходимо выделить полужирным, и выделяем их полужирным в разделе Шрифт. Данные в нужных ячейках помещаем в центре. Ячейки Получено за год/Налог объединяем и надпись помещаем в центре:
Затем в разделе формат ячеек меняем толщину внутренних границ:
Выравниваем также ФИО по центру и к фамилиям и ИТОГО применяем курсив:
В разделе Шрифт выбираем цвет заливки верхней зоны. В разделе Формат ячеек выбираем цвет заливки и узор нижней строки таблицы.
Итог всех описанных ранее действий и операций:
Затем выделяем все ячейки, нажимаем правой кнопкой мыши на эту область и переходим в раздел Формат ячеек и снимаем галочку с пункта Защищаемая ячейка:
Далее в разделе Рецензирование (на панели инструментов) нажимаем «Защитить лист», применяем защиту и проверяем её.
После проверки снимаем защиту:
Построение диаграмм
Выделяем в таблице только общий доход 5 людей. Переходим в раздел Вставка и выбираем круговую диаграмму.
Далее переходим в раздел «Конструктор диаграмм», выбираем «Выбрать данные», далее «изменить».
Выделяем нужные ячейки для подписи, определяя диапазон.
Затем удаляем название диаграммы:
Подбираем необходимый стиль и выносим данные диаграммы за ее пределы:
Затем выделяем каждый сектор диаграммы и применяем инструмент «Заливка» (цвета подбирается примерно тем, что представлены в образце):
Цвет шрифта – черный, контур также черный. В итоге:
Следующим шагом самостоятельно строим объемную гистограмму со всеми элементами оформления, содержащую только зарплаты Иванова, Петрова и Косарева и только за 2 и 4 кварталы.
Для этого выделяем необходимые ячейки, в разделе Вставка выбираем объёмную гистограмму.
В разделе Конструктор диаграммам нажимаем выбрать данные и убираем ненужные элементы
Далее вносим все необходимые изменения во внешний вид гистограммы согласно образцу задания и получаем следующий вид:
4.Эксплуатация электронной таблицы:
Копируем данные листа «Годовой доход» и вставляем их в новый лист (Лист 2) при помощи специальной вставки. Выделяем диапазон ячеек С7:E14 и копируем их. Присваиваем новому листу название «Первое полугодие».
Переходим на лист «Годовой доход». Переходим в раздел Формулы, далее Параметры вычисления. Переключаем автоматический режим на ручной.
Вводим новые данные в электронную таблицу так, чтобы проверить работоспособность формул.
У Косарева за 2 квартал меняем сумму с 2000000руб. на 5000000руб
После этого также изменилась гистограмма и приобрела следующий вид:
Совершаем пересчет данных, выделяя область данных и нажимая клавишу F9.
Данные изменились как на таблице, так и на диаграмме с гистограммой. (что касается данных на листе «Первое полугодие», там изменений у меня не произошло).
В принципе автоматическое изменение диаграмм мы наблюдали при выполнении предыдущих операций. Но пронаблюдаем еще раз:
Теперь меняем данные в таблице (Получено за год). Диаграмма меняется соответсвенно внесенным изменениям и приобретает следующий вид:
Изменения гистограммы было представлено выше, когда меняли данные относительно Косарева и делали пересчет данных.
РАБОТА С ЭЛЕКТРОННОЙ ТАБЛИЦЕЙ КАК С БАЗОЙ ДАННЫХ.
Запускаем Excel. Создаем заданную таблицу на 9 столбцов, согласно рисунку, и заполняем её.
Заполняем данные дальше. В столбце Дата поставки меняем формат ячеек (сперва автоматически распознаваемый, затем ДД/ММ):
При автоматически распознаваемом выставляется автоматически текущий год
Формат ДД/ММ:
Добавляем столбец «Дата окончания срока хранения товаров» и заполняем его соответствующей формулой. Для этого кликаем по G2, выбираем вставить – Столбец. Задаем название:
Вводим формулу суммы и применяем ее для всех соответсвующих ячеек:
Формат ячеек с суммами меняем на Денежный с 2-мя десятичными знаками.
Вносим необходимые данные согласно образцу.
Добавляем столбец «Сумма» уже известным способом со вставкой и применяем к нему формат «Денежный»:
В столбце Сумма применяем формулу умножения Стоимости на Количество:
Меняем цветовую гамму таблицы, редактируем границы, выравниваем текст:
В режиме заполнения формы заполняем еще несколько строк. Итог:
В ячейку «Цена продажи» вставляем примечание. (кликаем пр.кн.мыши – выбираем «создать примечание»)
Создаём новый лист и копируем в него все данные.
Применяем команду Сортировка.
А) по возрастанию стоимости
- по алфавиту названий товара:
- по убыванию количества товара:
Создаем список «самые дефицитные товары»:
- начинается на определенную букву:
Выбираю буква Х, для этого перехожу в текстовые фильтры, после чего использу «пользовательский автофильтр»:
- По цене товара (для этого «числовые фильтры» - «Равно», вводим нужные данные и применяем):
Далее задаём нужные критерии при помощи автофильтра
А) (< заданное наименование >) И (ЦП1 < Цена продажи) И (К1 < Количество< К2)
- (Дата поставки < Д1) И (Ср1< Срок хранения) И (Стоимость <= С1)
Cортируем производителей и потом добавляем Итоги по каждой стране:
Сохраняем таблицу в файле test2 и выходим из EXCEL.
Индивидуальное задание
Для индивидуального задания была выбрана таблица расходов и доходов семейного бюджета
Открываем Excel, создаем новый файл. Задаем заголовок, затем создаем 1 таблицу «Доходы бюджета», состоящую из 3 столбцов. Форматируем ее (выбираем цвет – зеленый, шрифт, выравниваем текст), задаем данные:
Применяем ко всем ячейкам «Сумма» денежный формат (уже изученный нами способ – «формат ячеек» - «Денежный»):
К ячейке «Итог» применяем автосумму (формулы – автосумма):
Создаем 2 таблицу «Расходы бюджета». Подобно первой форматируем ее и вводим данные:
Применяем ко всем ячейкам «Расход за месяц» и «План расхода» денежный формат (уже изученный нами способ – «формат ячеек» - «Денежный»):
К ячейке «Отклонение» применяем необходимую формулу для его вычисления:
Распространяем это на все остальные ячейки этого столбца:
Далее применяем к этим ячейкам Денежный формат (где отрицательные значения – отражаются красным цветом):
Далее форматируем таблицы еще раз, применяем заливку (чтобы четко разграничить столбцы):
Проверяем защиту ячеек с формулами. Для этого переходим в Формат ячеек. Защита подтверждена.
Создаем объемную гистограмму для «Отклонение»:
Редактируем ее. Столбцы с одинаковым показателем – обозначаем одним цветом
Другой способ, чтобы посмотреть отклонение в расходе, - использование фильтра. Для этого кликаем на ячейку «Отклонение» - выбираем «Числовые фильтры»
Вывод: в ходе данной лабораторной работы мною были освоены и практически применены навыки создания, редактирования, оформления и эксплуатации электронных таблиц в MS Excel.