Лабораторный практикум по информатике. Практикум по дисциплине Информатика Уфа 2012 3 Лабораторный практикум по дисциплине Информатика Уфимск гос авиац техн унт Сост. В. В. Мартынов, Е. Н. Прошин, Ю. В
 Скачать 8.39 Mb.
|
|
6.2.3 Задание на построение диаграмм для самостоятельного выполнения Создайте по таблице из задания 2.3 произвольные диаграммы, отражающие следующие зависимости: - • соотношение различных соизмеримых по значениям рядов для каждой строки; - • процентное соотношение итоговых значений столбцов; - • сравнение второго и четвертого столбца. Данные для построения выбирать по смыслу, элементы оформления задавать произвольно. Использовать различные типы диаграмм, каждую располагать на отдельном листе. 7. Методика выполнения заданий. 7.1. Расчет табличных значений Понятие электронной таблицы Основные правила: 1. Ввод формул начинайте со знака «=». Внутри формулы не допускаются пробелы, а в адресах используются только латинские буквы. 2. Завершайте ввод в ячейку и ее редактирование нажатием клавиши Enter для сохранения выполненных изменений. 3. Для форматирования данных необходимо активизировать ячейку с данными или выделить блок ячеек. Основные команды форматирования вынесены на вкладку ленты Главная. 180 4. Заголовок таблицы введите в ячейку, расположенную выше строки, с которой начинается таблица. Для центрирования заголовка по ширине таблицы выделите блок ячеек в строках с заголовком от первого до последнего столбца с таблицей и выполните команду Объединить и поместить в центре. 5. Для выбора функции =СУММ() использовать вкладку Главная панель Редактирование Откроем приложение Microsoft Excel. Создадим таблицу ЗАРПЛАТА СОТРУДНИКОВ ФИРМЫ с полями №, ФИО, ОКЛАД, ПРЕМИЯ, ПОДОХОДНЫЙ НАЛОГ, К ВЫДАЧЕ. Для этого в ячейку А1 введем с клавиатуры текст ЗАРПЛАТА СОТРУДНИКОВ ФИРМЫ, данный текст вышел за границы ячейки, пока не будем обращать на это внимания. Аналогично введем соответствующие надписи в столбцы второй строки. В ячейках А2:F2 разрешим перенос по словам, для этого выделим эти ячейки и на панели Выравнивание вкладки Главная в диалоговом окне Формат ячеек (открываемой с панелей инструментов), либо из контекстного меню (вызываемого при нажатии на ячейке правой кнопки мыши) на закладке Выравнивание установим флажок в окне Перенос по словам (Рис. 3, Рис. 4) Рис. 3 181 Рис. 4 Изменим размеры столбцов, для этого установим курсор сначала на границе между столбцами В и С (для расширения столбца В) в верхней части таблицы и когда курсор примет вид черной двунаправленной стрелки и при нажатой левой клавише мыши изменить границы столбцов до нужного размера. Для ввода нумерации строк в первый столбец используем прием АВТОЗАПОЛНЕНИЯ, для этого в ячейку А3 введем 1, в ячейку А4 – 2. Выделим обе ячейки и поставим курсор мыши в правый нижний угол выделенного блока ячеек, как только курсор примет вид черного креста (МАРКЕРА ЗАПОЛНЕНИЯ) при нажатой левой клавише мыши протянем заполняемую область до ячейки А10 (включительно). Далее заполним таблицу (Рис. ), предварительно определив формат ячеек в столбцах таблицы. Для этого на панели Выравнивание вкладки Главная в диалоговом окне Формат ячеек на закладке Число выбираем категорию Числовые форматы. До этого необходимо выделить интервал ячеек, в которых должен быть определен формат. (Рис. ) 182 Рис. 5 Рис. 6 Премия считается как =Оклад*0,1, для этого следует встать на ячейку D3 и ввести формулу =C3*0.1, как показано на 7. 183 Рис. 7 Подоходный налог считается как = (Оклад+Премия)*0,13, для этого следует встать на ячейку Е3 и ввести формулу =(C3+D3)*0,13. Сумма к выдаче считается как =Оклад+Премия–Подоходный налог, для этого следует встать на ячейку F3 и ввести формулу =C3+D3- Е3. Остальные ячейки таблицы рассчитать с помощью автозаполнения. Под таблицей подсчитать итоги по столбцам с помощью значка автосуммирования Главная→Редактирование→Сумма (Рис. 8). Рис. 8 Условное форматирование Используя команду Условное форматирование выделить другим цветом суммы к выдаче, меньшие 2000 руб. Для задания условного форматирования надо выделить блок ячеек F3:F10 и выбрать команду Главная→Стили→Условное форматирование. В открывшемся меню для задания определенного правила выделения ячеек нужно выбрать пункт Правила выделения ячеек (Рис. 9). 184 Рис. 9 В результате таблица будет иметь следующий вид: Рис. 10 Сортировка. При вводе исходных данных в таблицу не учитывался алфавитный порядок, поэтому можем произвести сортировку. Для этого поставим курсор в ячейку В3(ФИО) и выполним команду Данные→Сортировка и фильтр и нажмем на пиктограмму , таблица отсортировалась. Но при этом мы видим, что в таблице у нас имеются записи с однофамильцами. Произведем дальнейшую сортировку по окладу. Команду настраиваемой сортировки можно вызвать через Данные→Сортировка и фильтр→Сортировка, либо через Главная→Редактирование→Сортировка и фильтр. В появившемся окне Сортировка нужно указать: столбец (раскрывая списки Сортировать ПО); сортировка и порядок списка данных. Для добавления еще одного критерия сортировки нужно использовать кнопку Добавить уровень (Рис. 21). Чтобы данные первой строки списка не участвовали в сортировке, нужно поставить флажок «Мои данные содержат заголовки». 185 Рис. 21 В результате таблица будет выглядеть, как представлено на Рис. 32. Рис. 32 Чтобы задать сортировку не строк, а столбцов диапазона, нужно, нажав кнопку Параметры, указать «сортировать столбцы диапазона». При нажатии клавиши ОК получаем полностью отсортированную таблицу. Мастер функций В Excel существуют математические, логические, финансовые, статистические, текстовые и другие функции. Имя функции в формуле можно вводить вручную с клавиатуры (при этом активируется средство Автозаполнение формул, позволяющее по первым введенным буквам выбрать нужную функцию, Рис. 3), а можно выбирать в окне Мастер функций, активируемом кнопкой Вставить функцию на панели Библиотека функций вкладки Формулы или из групп функций на этой же панели (Рис. 4), либо с помощью кнопки панели Редактирование вкладки Главная (Рис. 5). Полезной возможностью по работе с формулами является отображение всех формул на листе. Это можно сделать, используя 186 команду Формулы→Зависимости формул→Показать формулы. После этого в ячейках вместо вычисленных значений будут показаны записанные формулы. Для возврата в обычный режим нужно еще раз нажать кнопку Показать формулы (Рис. 46). Под таблицей с помощью мастера функций, который вызывается по команде Формулы→Библиотека функций→Вставить функцию, подсчитать средний оклад сотрудников, максимальную сумму к выдаче и минимальный подоходный налог. Рис. 13 Рис. 14 187 Рис. 15 Рис. 46 Фильтрация (выборка данных). Для просмотра не всей таблицы, а лишь данных, удовлетворяющих некоторому условию, обращаемся к помощи фильтров. Для установки фильтра выделяется вся таблица, включая заголовок, без итогов. Если требуется просмотреть всю совокупность выбранных данных по тому или иному критерию выбираем Данные→Фильтр. В каждой ячейке заголовка появится кнопка, на которой изображена направленная вниз стрелка. При нажатии этой кнопки появится подменю, в котором выберем пункт Числовые фильтры, что вызовет появление нового диалогового окна. В данном окне установим условия просмотра: Оклад>=3000 (Рис. 7). 188 Рис. 17 После чего на экране останутся только те записи из таблицы, которые соответствуют, заданному условию. Вернуться к первоначальному виду таблицы можно вновь выбрав ячейку заголовка Оклад, на которой изображен фильтр с направленной вниз стрелкой. При нажатии этой кнопки появится подменю, в котором выберем пункт Удалить фильтр с «Оклад» илипоставить галочкув окошке Выделить все (Рис. 8). Выберите людей с зарплатой от 2000 до 3000 рублей. Выберите 3 служащих с наименьшей суммой зарплаты с помощью пункта ПЕРВЫЕ 10. Выберите людей, фамилии которых содержат букву «е». Выберите людей с окладом более 3000, фамилии которых начинаются на букву «С». 189 Рис. 18 Использование формул для принятия решений Вычислить аванс в седьмом столбце таблицы по следующему принципу: если сумма к выдаче превышает 3500 рублей, то аванс составляет 2000 рублей, иначе – 40% от суммы к выдаче. Для вычисления значения аванса необходимо использовать функции раздела Логические. Функция ЕСЛИ в зависимости от истинности параметра Лог_выражение возвращает либо Значение_если_истина, либо Значение_если_ложь. Таким образом, вызвав функцию ЕСЛИ (Рис. 9), необходимо указать следующие значения параметров: Рис. 19 190 После этого нажатие кнопки ОК завершает создание формулы (Рис. 20). После того как высчитан аванс для первого человека в списке, необходимо выполнить АВТОЗАПОЛНЕНИЕ остальных ячеек этого столбца. Рис. 20 Обработка массивов Найти количество человек с окладом более 3000 рублей. Чтобы подсчитать количество таких записей следует воспользоваться функцией СУММ, аргументом которой будет функция ЕСЛИ, проверяющая, не превышает ли очередное значение в столбце «ОКЛАД» числа 3000. При этом будет использована так называемая работа с массивами, так как одну и ту же проверку необходимо выполнить не для одной ячейки (С3>3000), а для диапазона ячеек (С3:С10>3000), который в данном случае называется массивом. Таким образом, значения параметров вложенной функции ЕСЛИ примут следующий вид (Рис. 21, рис. 22): 191 Рис. 21 Рис. 22 Однако после того как формула будет введена, в ячейке отобразится ошибка: #ЗНАЧ. Чтобы по формуле, в которой используется работа с массивами, было вычислено правильное значение, необходимо выделить ячейку с формулой, установить курсор в строку формул и нажать сочетание клавиш Ctrl+Shift+Enter. После этого формула будет заключена в фигурные скобки, которые означают работу с массивом:{=СУММ(ЕСЛИ(С3:С10>3000;1;0))} 23 192 Рис. 23 Графика Основные правила: Для создания диаграммы необходимо выделить блок данных, на основании которых строится диаграмма. В выделяемый блок данных включите не только числовые данные, но и заголовки строк (столбцов), в которых они расположены. Заголовки будут использованы в качестве подписей по осям (меток) и для формирования условных обозначений (легенды). При выделении блоков с данными для построения диаграмм необходимо соблюдать два правила: выделенный фрагмент должен состоять из равновеликих столбцов; в выделенном фрагменте не должно быть объединенных ячеек. Постройте гистограмму «ФИО→Оклад→Аванс». Постройте круговую диаграмму «ФИО→Оклад». Для построения ГИСТОГРАММЫ выделите ячейки, значения которых войдут в гистограмму – диапазон ячеек В3:В10, С3:С10 и диапазон ячеек F3:F10. Для того, чтобы выделить несмежные ячейки сначала выделите ячейки столбца В, а затем при нажатой клавише Ctrl – ячейки столбцов С, F. Вызвать диалоговое окно МАСТЕРА ДИАГРАММ можно следующим образом: вкладка Вставка→Гистограмма на панели инструментов. Все изменения удобно проводить с использованием Строка формул 193 контекстного меню. Попробуйте изменить вид диаграммы и другие параметры (Рис. 4) Для построения КРУГОВОЙ ДИАГРАММЫ (Рис. 25) необходимо перейти на вкладку Вставка, открыть список Круговаяи выбрать нужную диаграмму. В круговой диаграмме укажите долю значений ОКЛАДА. Рис. 24 Рис. 25 194 Контрольные вопросы 1. Для чего могут использоваться табличные процессоры? 2. Какие панели расположены на вкладке Главная? 3. Как переименовать лист в книге? 4. Для чего используется «специальная вставка»? 5. Как удалить одну ячейку на листе? 6. Что позволяет сделать команда скрытия ячеек? 7. Как объединить несколько ячеек? 8. В чем удобство применения средства «Формат по образцу»? 9. Как изменить параметры стилей ячеек? 10. Для чего можно использовать условное форматирование? 11. Как задать ширину столбца? 12. Как работает функция «автоподбор высоты строки»? 13. Как можно изменить формат ячейки? 14. Какие существуют правила записи формул? 15. Чем отличаются различные виды ссылок на ячейки? 16. Как вставить в формулу стандартную функцию? 17. Для чего может использоваться режим отображения зависимостей формул? 18. Как отобразить все записанные формулы на листе книги? 19. Какие виды графических изображений можно разместить на листах? 20. Каким образом можно отредактировать изображение? 21. Что включает в себя область диаграммы? 22. Как добавить в диаграмму ряд данных? 23. Как скопировать диаграмму на другой лист? 24. Как отсортировать данные по нескольким критериям? 25. Для чего используется расширенный фильтр? 26. Что такое сводная таблица? 27. Что такое ячейка? Как задается адрес ячейки? 28. Чем отличается относительный адрес от абсолютного адреса? 29. Что такое диапазон ячеек? Как обозначается диапазон ячеек! 30. Как сделать обрамление группы ячеек? 31. Что такое формат данных и как он устанавливается? 32. Какие существуют типы данных? 33. Что такое Мастер функций, какие категории функций встроены в Excel? 34. Как можно выделить несмежные ряды данных? 195 35. Что нужно сделать, чтобы внести изменения в диаграмму? 8. Требования к содержанию и оформлению отчета. Отчет по лабораторной работе должен содержать: Название университета, факультета и кафедры; Название работы; Сведения о студенте (ФИО, группа, курс) и о преподавателе (ФИО); Дата выполнения лабораторной работы; Цель работы; Постановку задачи (варианты задания); Результаты выполнения заданий работы (экранные копии); Выводы по работе. Оформлять отчет необходимо в текстовом редакторе Microsoft Word в виде отдельного документа. Вопрос о форме представления готового отчета (в распечатанном виде или в электронной форме) решается по согласованию с преподавателем. В случае представления отчета в электронной форме, отчет по каждой лабораторной работе должен находиться в отдельной папке, в которую дополнительно необходимо поместить все созданные в ходе выполнения работы файлы. 9. Критерии результативности лабораторного практикума. Лабораторная работа считается выполненной, если: - задание к лабораторной работе выполнено в полном объеме; - студент представил тексты программ и результаты их выполнения; - результаты выполнения программ соответствуют поставленным задачам; - при устном ответе на 1-2 контрольных вопроса по указанию преподавателя студент демонстрирует знание основных команд, функций Excel и определений изученной темы и умение применить их при решении соответствующих задач; - представленный отчет по лабораторной работе соответствует требованиям раздела 8. 196 Лабораторная работа №9 Консолидация данных, сводные таблицы и сводные диаграммы в Excel 2010 1. Цель работы Изучение возможностей обработки данных в электронных таблицах и изучения технологий объединения данных нескольких таблиц Excel 1. Задачи работы Изучение консолидации данных, выполнения автоматического многоуровневого структурирования данных с помощью сводных таблиц и его графического представления с помощью сводных диаграмм. 3. Теоретические сведения 3.1. Консолидация данных Под консолидацией данных понимают автоматическое объединение данных нескольких таблиц с использованием некоторых итоговых функций (сумма, количество, среднее, максимум, минимум, произведение и др.) для вычисления нарастающих итогов. Функция консолидации используется в том случае, если необходимо вычислить итоги для данных, расположенных в различных областях таблицы (или таблиц). С помощью функции консолидации над значениями, расположенными в несмежных областях, можно выполнить те же операции, что и с помощью функции автоматического определения промежуточных итогов. Например, с помощью консолидации могут быть сведены в одной таблице данные о различных бригадах одного цеха, о нескольких подразделениях одного предприятия, отчеты за несколько месяцев (кварталов) одного года и т.д. Подлежащие консолидации области могут располагаться как на одном рабочем листе, так и на разных листах, равно как и в различных рабочих книгах. При выборе команды меню Данные-Консолидация открывается окно консолидации (рис. 1), в котором можно: выбрать итоговую функцию для обработки данных; определить исходные области консолидируемых данных; 197 установить, использовать ли в качестве имен подписи верхней строки или значения левого столбца; установить, нужно ли создавать связи с исходными данными для автоматического обновления итоговой таблицы при изменении источников данных. Внимание! Связи нельзя использовать, если исходная область и область назначения находятся на одном листе. После установки связей нельзя добавлять новые исходные области и изменять исходные области, уже участвующие в консолидации. Консолидация может выполнять объединение данных таблиц одинаковой и различной структуры. В первом случае произойдет консолидация по расположению (см. рис 2), а во втором – по категориям (см. рис 3). Т.к. второй способ более универсален при выполнении лабораторной работы будет рассмотрен именно он. В диалоговом окне |