Методические указания по выполнению практических работ по теме «Табличный процессор Microsoft Excel 2013», Часть 2

-1019175-424815ДЕПАРТАМЕНТ ОБРАЗОВАНИЯ ВОЛОГОДСКОЙ ОБЛАСТИ
БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ СРЕДНЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ Вологодской области
«ВОЛОГОДСКИЙ СТРОИТЕЛЬНЫЙ КОЛЛЕДЖ»
00ДЕПАРТАМЕНТ ОБРАЗОВАНИЯ ВОЛОГОДСКОЙ ОБЛАСТИ
БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ СРЕДНЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ Вологодской области
«ВОЛОГОДСКИЙ СТРОИТЕЛЬНЫЙ КОЛЛЕДЖ»
-1082040-71056500
4794251743075МЕТОДИЧЕСКИЕ УКАЗАНИЯ
по выполнению практических работ по теме
«Табличный процессор Microsoft Excel 2013»
Часть 2
00МЕТОДИЧЕСКИЕ УКАЗАНИЯ
по выполнению практических работ по теме
«Табличный процессор Microsoft Excel 2013»
Часть 2
-10680706663055Вологда
2014
00Вологда
2014

Рассмотрено на заседании предметно–цикловой комиссии математических, естественно–научных и общепрофессиональных дисциплин.
Протокол №2 от 8.10.2014 г.
Данные методические указания предназначены для студентов всех специальностей БОУ СПО ВО «Вологодский строительный колледж» при выполнении практических работ по теме «Табличный процессор Microsoft Excel 2013» при изучении дисциплин: «Информатика», «Информационные технологии в профессиональной деятельности», «Компьютерное сопровождение профессиональной деятельности».
Методические указания содержат: введение, теоретические сведения, задания для закрепления, включающие в себя подробный порядок выполнения, задания для отработки, приобретенных знаний и практических навыков работы с электронными таблицами, задания профессиональной направленности.
Методические указания могут быть рекомендованы к использованию студентами и преподавателями БОУ СПО ВО «Вологодский строительный колледж».
0670496500Авторы:
преподаватель информатики и ИКТ БОУ СПО ВО "Вологодский строительный колледжТ. А. Габриэлян
Рецензент: заместитель директора по учебно-методической работе и качеству образования Санкт-Петербургского государственного экономического университета, филиал в г. Вологде, преподаватель информатики высшей категории И. А. ЕвграшинаОглавление
Введение 3
Диаграммы и графики 4
Обработка данных 21
Практические задания 36
Список литературы и информационных источников 48
Приложения 50
ВведениеТребования работодателей к современному специалисту, а также федеральный государственный образовательный стандарт СПО ориентированы, прежде всего, на умения самостоятельной деятельности и творческий подход к специальности. Профессиональный рост специалиста, его социальная востребованность, как никогда зависят от умений проявить инициативу, решить нестандартную задачу, от способности к планированию и прогнозированию самостоятельных действий. Стратегическим направлением повышения качества образования в этих условиях является оптимизация системы управления учебной работой обучаемых, в том числе и их самостоятельной работой.
В современный период востребованы высокий уровень знаний, академическая и социальная мобильность, профессионализм специалистов, готовность к самообразованию и самосовершенствованию.
Табличный процессор Microsoft Office Excel — удобное и мощное средство для создания таблиц и графиков, анализа данных, проведения расчетов. В виде файлов формата Excel создаются финансовые отчеты, распространяются прайс-листы, оформляются расчеты в различных сферах деятельности, в том числе и в строительстве. Excel 2013 — на данный момент самая последняя версия популярного табличного процессора.
В методических указаниях рассмотрен интерфейс последней версии Microsoft Excel 2013, освещены вопросы организации рабочих книг Excel, форматирования листов, использования разнообразных формул для вычислений, поиска необходимой информации. Теоретический материал сопровождается практическими примерами и заданиями для самостоятельного освоения материала.
Целью методических указаний является подробное освещение последней версии Microsoft Excel, решение профессиональных задач средствами Microsoft Excel.

Начало работы с Microsoft Excel 2013
Диаграммы и графики
Построение диаграмм
Диаграммы создаются по данным, введенным в таблицы на листах.
Инструменты для работы с диаграммами собраны в группе Диаграммы, расположенной на вкладке Вставка (щелкните мышью на названии вкладки Вставка, чтобы перейти на нее).
В этой группе приведены различные виды диаграмм. Если щелкать мышью на кнопках с названиями типов диаграмм, будут раскрываться меню, где можно выбрать конкретную модификацию диаграммы выбранного типа.
Например, создадим объемную гистограмму для данных таблицы, представленной на рис. 1.

Рис. 1 Исходные данные для построения диаграммы
Предварительно нужно выделить данные, которые будут участвовать в построении гистограммы, вместе с их заголовками. На рис. 1 выделены восемь ячеек с числовыми данными и заголовки.
Как показано на рис. 1, в группе Диаграммы щелкаем мышью на стрелке в правой части кнопки Гистограмма и в открывшемся при этом подменю выбираем один из вариантов раздела Объемная гистограмма.
В результате на листе появится прямоугольная область с помещенной в нее диаграммой. Перетаскивая ее мышью, установите прямоугольную область там, где по вашей задумке должна располагаться диаграмма на листе (рис. 2).

Рис. 2 Размещение диаграммы
Размеры области с диаграммой можно менять. По сторонам и углам рамки вокруг области диаграммы есть маркеры изменения размера. При наведении на них указателя мыши он принимает вид двунаправленной стрелки. Если в этот момент нажать левую кнопку мыши и, не отпуская ее, перемещать указатель мыши, то размеры области с диаграммой изменятся.
В области диаграммы, кроме самой диаграммы, присутствуют надписи, поясняющие, какие именно данные обозначены тем или иным цветом или узором. Эти надписи называют легендой. Для отображения легенды тоже можно задавать различные параметры. Для этого щелкните правой кнопкой мыши на легенде в области диаграммы и в появившемся контекстном меню выберите пункт Формат легенды (рис. 3).
В результате в правой части окна Excel 2013 становятся доступными параметры легенды. Под словами Формат легенды находится раскрывающийся список категорий. Все варианты настройки выбранной категории доступны в правой части окна. Например, на рис. 4 показаны варианты настройки для категории Параметры легенды. С их помощью можно задать положение легенды относительно диаграммы.
Если вы щелкнете мышью на стрелке вниз справа от слов Параметры легенды, вы увидите весь список категорий.
Для завершения работы с окном настройки легенды щелкните мышью на кнопке закрытия (в виде крестика) справа от слов Формат легенды.

Рис. 3 Настройка формата легенды
Для некоторых видов диаграмм, например для гистограмм, можно настроить особенности отображения и размерность координатных осей. Для этого щелкните правой кнопкой мыши на изображении любой оси диаграммы и в появившемся контекстном меню выберите пункт Формат оси. В правой части окна Excel 2013 появится панель Формат оси, где собраны различные элементы управления для настройки по категориям.

Рис. 4 Параметры легенды
Если вы решили, что выбрали неподходящий тип диаграммы, всегда можно изменить его. Для этого, если область диаграммы не выделена, щелкните мышью на диаграмме. После этого нужно щелкнуть мышью на кнопке Изменить тип диаграммы на вкладке Конструктор. При этом откроется окно, где представлены на ваш выбор различные типы диаграмм.
Графики
В Excel 2013 есть еще одна группа инструментов для наглядного представления изменений в данных, которые называются спарклайны (в предыдущей версии Excel они назывались инфокривые). Они очень похожи на гистограммы, однако рассчитаны на размещение внутри одной ячейки. Это очень удобно, чтобы показать, например, изменение какого-то одного показателя во времени.
Все эти инструменты собраны в группе Спарклайны на вкладке Вставка.
Для вставки, например, спарклайн-графика нужно выделить ячейку для вставки и щелкнуть мышью на кнопке График в группе Спарклайны на вкладке Вставка. Появится окно, где нужно указать исходные данные для построения графика, то есть диапазон ячеек (рис. 5). Можно ввести адреса ячеек в поле Диапазон данных вручную или выделить нужные ячейки мышью (с нажатой левой кнопкой мыши выделить те ячейки, по значениям которых нужно строить график).

Рис. 5 Вставка графика
Щелкните мышью в этом окне на кнопке OK. График будет помещен в выбранную вами ячейку (рис. 6).

Рис. 6 Построенный график
При выделении ячейки с графиком становится доступной вкладка Конструктор (в верхней части окна Excel 2013). С помощью инструментов этой панели можно менять внешний вид графика. На рис. 7 показан режим вывода на графике максимального и минимального значений.

Рис. 7 Изменение параметров парклайнаПри изменении высоты или ширины ячейки, где расположен график, меняется и его масштаб.
Для удаления графика из ячейки щелкните правой кнопкой мыши на этой ячейке, в появившемся контекстном меню раскройте подменю Спарклайны и выберите пункт Очистить выбранные спарклайны, как показано на рис. 8.

Рис. 8 Удаление графика
Обработка данных
Сортировка данных
Для анализа данных удобно сортировать их по различным полям, причем как по возрастанию, так и по убыванию. Для сортировки данных внутри одного столбца надо выделить группу ячеек, значения которых требуется сортировать, и щелкнуть мышью на кнопке в группе Редактирование на вкладке Главная (рис. 9).

Рис. 9 Сортировка выделенного диапазона ячеек
В результате откроется меню, где нужно выбрать направление сортировки.
Если вы выделили для сортировки значения ячеек сразу в нескольких столбцах, есть возможность сортировать строки сначала по одному столбцу, затем внутри этой сортировки — по другому столбцу и т. д. В этом случае в меню надо выбрать пункт Настраиваемая сортировка. Откроется диалоговое окно, представленное на рис. 10

Рис. 10 Настройка порядка сортировки
Изначально настраивается только один столбец для сортировки. По нему строки будут отсортированы в первую очередь.
Для сортировки по еще одному столбцу щелкните мышью на кнопке Добавить уровень. Появится вторая запись с возможностью указать направление сортировки по второму столбцу. Если надо добавить сортировку еще по нескольким столбцам, поступайте аналогичным образом (рис. 11).

Рис. 11 Сортировка по нескольким столбцам
Фильтры
Иногда таблицы в Excel содержат довольно большое количество данных, это может быть, например, перечень закупленных за год расходных материалов. А вам нужно найти среди них только данные, относящиеся к вашему подразделению. Как это сделать?
Для того чтобы из общей массы записей выбрать только часть, удовлетворяющую вашему условию, можно воспользоваться инструментом под названием фильтр.
Чтобы установить фильтр, выделите мышью заголовки в шапке таблицы либо всю таблицу, затем на вкладке Главная щелкните на кнопке Сортировка и фильтр и в появившемся меню выберите пункт Фильтр. В правом нижнем углу ячеек с заголовками столбцов таблицы появятся кнопки с направленной вниз стрелкой , как показано на рис. 12

Рис. 12 Установка фильтра
Если вы щелкнете мышью на такой кнопке, появится меню, в котором можно указать направление сортировки по этому столбцу, а также условия отбора записей (рис. 13).

Рис. 13 Выбор текстового фильтра
Если вам, например, нужно выбрать в списке только имена, начинающиеся на букву С, щелкните на кнопке в столбце с именами, в появившемся меню раскройте подменю Текстовые фильтры и выберите пункт начинается с. Откроется окно, показанное на рис. 14.

Рис. 14. Параметры текстового фильтра
В поле справа от слов начинается с введите букву С. Можно ввести не одну букву, а несколько. В результате будут выбраны только те имена, которые начинаются с указанного сочетания букв, а остальные записи таблицы скроются с экрана (рис. 15).
Кнопка в ячейке с заголовком столбца, по которому установлен фильтр, меняется и принимает вид .
Рис. 15 Столбец с установленным фильтром
Если щелкнуть мышью на кнопке в столбце с числовыми данными, вместо пункта Текстовые фильтры появится пункт меню Числовые фильтры. При выборе такого пункта вы можете указать диапазон значений чисел для выбираемых записей (рис. 16). Можно выбрать все записи больше указанного вами значения или меньше.

Рис. 16 Выбор числового фильтра
Для рассматриваемого нами примера, выберем список людей, родившихся после 1973 года. Для этого щелкнем мышью на кнопке в ячейке Е1 (Год рождения), в появившемся меню раскроем подменю Числовые фильтры, как показано на рис. 16, и выберем пункт больше. Откроется окно, показанное на рис. 17. В поле справа от слова больше введем значение 1973 и щелкнем мышью на кнопке OK. В результате в списке останутся только люди, родившиеся после 1973 года (рис. 18).

Рис. 17 Параметры числового фильтра

Рис. 18 Выбраны числовые значения больше 1973
Фильтр можно в любой момент поменять или вовсе убрать. Для этого щелкните мышью на кнопке и в появившемся меню выберите пункт Удалить фильтр из столбца.
Импорт внешних данных
Excel 2013 предлагает разнообразные инструменты импорта данных, хранящихся в форматах других приложений и в различных базах данных.
Основные инструменты для работы с внешними данными собраны на вкладке Данные. При необходимости получения данных из файлов в формате Access или вебстраниц щелкните мышью на соответствующей кнопке, при появлении окна открытия файла найдите папку, где содержится нужный вам файл, и щелкните на его имени мышью. Если в выбранном файле содержится несколько таблиц, вам будет предложено выбрать в списке нужную. Сделайте это щелчком на ее имени мышью, затем щелкните на кнопке OK. На следующем шаге вам будет предложено выбрать, вставлять ли полученные данные в текущий лист или открыть для них новый. Сделав выбор, щелкните на кнопке OK, и данные появятся на листе.
Получать информацию непосредственно из систем управления базами данных (СУБД), таких как MS SQL Server, несколько сложнее. Для этого требуется установить связь с СУБД, для чего вы должны знать имя пользователя и пароль.

Практические задания
Каждое задание выполняется на отдельном листе
Задание1 (Лист 1)
Теоретические сведения:
Диаграммы
Диаграмма – это способ графического представления числовых данных, находящихся в таблице.
Основное достоинство визуального представления данных – наглядность. На диаграммах легко просматриваются тенденции к изменению, различные приросты, соотношения. При создании диаграммы необходимо использовать только те элементы, которые наилучшим образом проиллюстрируют отображаемые на ней данные.
Элементы диаграммы:

Область построения диаграммы. Область на диаграмме, ограниченная осями, в которой находятся ряды данных.
Область диаграммы. В этой области находятся все элементы диаграммы.
Легенда. Набор ключей и текста, описывающих ряды данных диаграммы.
Точка данных. Значение, связанное с меткой категории (графически: 1 из столбцов на диаграмме).
Линии сетки. Эти линии наносятся на диаграмму и облегчают чтение значение точек данных.
Ряд данных. Группа значений (связанных с категорией), отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы.
Вывод рядов данных на диаграмме (строка/столбец). Изменяет вывод рядов данных в диаграмме либо из строк, либо из столбцов [Конструктор →Строка/столбец]. Взаимная замена данных по осям. Одна и та же диаграмма может принимать разный вид, в зависимости замены данных по осям.

В диаграммах обновление данных происходит автоматически при изменении данных в исходной таблице.
Для редактирования уже имеющейся диаграммы, необходимо активизировать её, щелкнув мышью по области диаграммы.
При активизации диаграммы в MS Word 2007, на ленте появляются 3 новые вкладки для работы с диаграммами.
Конструктор: Тип, данные, макеты диаграмм, стили диаграмм, перемещение диаграммы, название диаграммы, название осей, легенда, подписи данных, таблица данных, настройки области построения диаграммы.
Формат: форматирование элементов диаграммы
Построение диаграммы:
Проанализировать: какие данные таблицы должны отражаться на диаграмме;
Выделить таблицу или области (диапазоны) ячеек по которым будет строиться диаграмма. Несмежные ячейки выделяются с нажатой клавишей Ctrl.
Вставка – Выбрать тип диаграммы.
Отобразить на диаграмме подписи данных, название, легенду.

Создайте таблицу, постройте диаграмму на этом же листе.
знактехникаприродахуд.образчеловек18 9 7 3 22
а) Объемная гистограмма

б) Тип: Объемная разрезанная круговая

в) Тип: Линейчатая, Горизонтальная цилиндр.

Порядок выполнения:
Выделить диапазон A1:E2;
Вставка→Гистограмма→Объемная гистограмма (а);
Конструктор→Строка/столбец (поменять ряды местами);
Конструктор→Название диаграммы → Над диаграммой;

Конструктор → Подписи данных → Показать (Дополнительные параметры подписей данных);

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

Выделить все ячейки, включая заголовки;
Вставка →Диаграммы →График→ График с маркерами
По шкале Х будет отложена дата, по шкале Y — денежные единицы.
У получившегося графика есть один существенный недостаток — слишком большой диапазон значений по вертикальной оси, вследствие чего изгиб кривой виден нечетко, а графики оказались прижаты друг к другу. Чтобы улучшить вид графика, необходимо изменить промежуток значений, отображаемых на вертикальной шкале.
Поскольку даже самая низкая цена в начале полугодового интервала превышала 1000, а самая высокая не превысила отметку 2000, имеет смысл ограничить вертикальную ось этими значениями. Выполните правый щелчок на области оси Y и задействуйте команду Формат оси

Далее оформим диаграмму
Конструктор → Стили диаграмм → Стиль 4;
Легенда → Добавить легенду снизу;
Выделить область построения диаграммы →Формат → Заливка фигуры →Градиентная → Выбрать цвет;
Макет → Название диаграммы

Задание3 (Лист 3)
В электронной таблице Microsoft Excel создайте таблицу, заполните данными и вычислите с помощью автосуммирования – сумму по столбцам, с помощью функции СУММ – сумму по строкам. Постройте диаграмму по диапазонам: В1:Е1 и В9:Е9; для выделения не смежных диапазонов, используйте клавишу Ctrl


Задание4 (Лист 4)
Создайте таблицу, выполните вычисления.

При оформлении таблицы установить:
Выравнивание данных по центру столбца;
В столбце «Плотность населения» записать формулы для вычислений, задать формат числа с одним знаком после запятой;
По данным таблицы построить диаграммы:
Круговую – по столбцу «Всего земель»
Цилиндрическую – по столбцу «Плотность населения»;
При оформлении диаграмм убрать линии сетки, записать заголовок диаграммы, установить подписи данных и легенду.
Задание 5 (Лист 5)
В электронной таблице Microsoft Excel создайте таблицу, заполните данными, постройте по 2 и 3 столбцам диаграмму
годприходрасход1992 200 150
1993 350 230
1994 410 250
1995 200 180


Задание 6 (Лист 6)
Составить электронную таблицу «Справочник расценок», содержащую поля: Код работы, Наименование работы, Расценка (руб./час), Объем работы (час), Стоимость заказа. Стоимость заказа должна рассчитываться. Построить гистограмму распределения стоимости заказа по кодам работ.
Код работы Наименование Расценка Объём работы Стоимость заказа
1 Установка ОС 1000 12 12000
2 Установка ПО 300 11 3300
3 Восстановление информации с носителя 500 1 500
4 Диагностика ПК 500 3 1500
5 Удаление вредоносных программ 500 1 500

Задание 7 (Лист 7)
Постройте график функции , для построения графика заполните таблицу.

В ячейку B1 ввести начальное значение -5, в ячейку С2 ввести формулу: =B1+0,5. Скопировать формулу в остальные ячейки строки.
В ячейку B2 ввести формулу:. =СТЕПЕНЬ(СТЕПЕНЬ(B1;2)*(B1+3);1/3)
Скопировать формулу в остальные ячейки строки.
Установить границы таблицы.
Выделить всю таблицу (диапазон A1:V2);
Вставка →Диаграммы →Точечная → Точечная с гладкими кривыми;
Макет → Название диаграммы, Измените название, вставив объект формулу;

Задание 8 (Лист 8)
Создайте и заполните таблицу. Рассчитайте с помощью функций:
СУММ, столбец «Итого»;
СРЗНАЧ, среднее значение премии по месяцам;
МИН, МАКС, минимальную и максимальную премии
Постройте диаграммы.
Премиальные выплаты сотрудникам ООО "Карат"
ФИО Сентябрь Октябрь Ноябрь Декабрь Итого
Борсовкин А. Л. 2 000,00р. 2 300,00р. 1 000,00р. 3 300,00р. 8 600,00р.
Веселов П. Р. 2 500,00р. 2 875,00р. 1 200,00р. 3 500,00р. 10 075,00р.
Гордеев Н. Г. 4 000,00р. 4 600,00р. 1 500,00р. 5 000,00р. 15 100,00р.
Ершов А. Ф. 1 200,00р. 1 380,00р. 1 700,00р. 2 300,00р. 6 580,00р.
Корнеев Г. Л. 800,00р. 920,00р. 900,00р. 1 500,00р. 4 120,00р.
Малышев Ф. Д. 4 300,00р. 4 945,00р. 2 100,00р. 3 800,00р. 15 145,00р.
Порозов Е. О. 5 200,00р. 5 980,00р. 3 150,00р. 6 000,00р. 20 330,00р.
Ср.знач.премии2 857,14р. 3 285,71р. 1 650,00р. 3 628,57р. Мин.премия800,00р. 920,00р. 900,00р. 1 500,00р. Макс.премия5 200,00р. 5 980,00р. 3 150,00р. 6 000,00р.


Построение графиков
Задание9 (Лист 9)
Построить графики функций y=x2 и y=0,5x+2.
Порядок выполнения:
Создадим таблицу для первой функции.
В ячейку В3 введем число -6, в ячейку С3 формулу: =В3+0,5 и скопируем формулу в ячейки D3:Z3;
В ячейку В4 введем формулу: =СТЕПЕНЬ(B3;2), скопируем в ячейки С4:Z4;

Создадим таблицу для второй функции.

Выделить диапазон В3:Z4
Вставка → Точечная → С гладкими кривыми
График будет иметь вид:

Конструктор → Выбрать данные →Элементы легенды (ряды) →Изменить →

Изменяем параметры горизонтальной оси: Макет → Оси →Основная горизонтальная ось → Дополнительные параметры основной горизонтальной оси → Меняем переключатель с «авто» на «фиксированное», задаем значение.

Устанавливаем переключатель: Значение оси 0,0

Изменяем параметры вертикальной оси аналогично.
Добавляем вторую функцию на график
Конструктор → Выбрать данные →Элементы легенды (ряды) →Добавить →
а)б)
в)
Название: Конструктор → Название диаграммы → Над диаграммой;
Устанавливаем линии сетки: Конструктор → Добавить элемент диаграммы → Сетка → Вертикальные линии сетки → Основные линии сетки

Задание 10 (Лист 10)
Построить графики функций y1= x 2 и y2= x 3 на интервале [- 3 ; 3] с шагом 0,5.
Порядок выполнения:
Заполнить таблицу значений;
Выделить таблицу и указать тип диаграммы Точечная.
Выбрать формат точечной диаграммы с гладкими кривыми.


Задание 11
Построить графики функций QUOTE и QUOTE на интервале [- 3 ; 3] с шагом 0,5.

Задание 12
Построить графики функций QUOTE и QUOTE на интервале [- 0.5 ; 9] с шагом 0,5.
Задание 13
Построить графики функций QUOTE и QUOTE на интервале [- 5 ; -0.5] с шагом 0,5.
Задание 14
Построить графики функций QUOTE и QUOTE на интервале [- 0.5 ; 5] с шагом 0,5.
Задание 15
Построить графики функций QUOTE , QUOTE , QUOTE на интервале [-3 ;3] с шагом 0,3.

Задание 16
Теоретические сведения:
Сортировка списков. Виды сортировки. Технология сортировки
Под списками в EXCEL понимаются таблицы, содержащие однородную информацию. Чтобы превратить таблицу EXCEL в список, следует присвоить столбцам имена, каждый из которых находится в одной клетке. В списке, который иногда называют базой данных EXCEL, строки таблицы называются записями, а столбцы – полями. При создании списка на рабочем листе EXCEL необходимо соблюдать следующие основные правила:
1) на одном рабочем листе не следует помещать более одного списка;
2) следует отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой;
3) имена полей списка должны располагаться в первой строке таблицы, EXCEL использует эти имена при создании отчётов, в поиске и сортировке данных.
4) каждый столбец списка должен содержать во всех строках однотипные данные.
При работе со списками часто возникает необходимость сортировки его строк в заданном порядке. Это можно выполнить с помощью команды Сортировка, которая входит в меню Данные, предварительно выделив весь список с заголовками столбцов, кроме итоговых строк таблицы, если они присутствуют. Включение заголовков столбцов в область выделения облегчает настройку сортировки, поскольку эти имена можно использовать в качестве ключей сортировки. В противном случае ключами сортировки будут стандартные имена столбцов таблицы EXCEL. Команда Сортировка открывает одноименное диалоговое окно, где задаются параметры сортировки.
Вместо простого порядка сортировки по возрастанию или по убыванию можно задать пользовательский порядок сортировки по заданному ключу. В этом случае не применяются обычные правила сортировки в алфавитном или числовом порядке. Например, с помощью пользовательского порядка сортировки можно отсортировать список по месяцам календаря: январь, февраль, март, апрель и т.д.
Пример: Имеется таблица «Премиальные выплаты сотрудникам ООО «Карат»». Произвести сортировку в столбце B выплат по возрастанию, а в столбце А – фамилий в обратном алфавитном порядке.

Порядок выполнения:
Выделяем ячейки B2:B9
Главная
В появившемся окне выбираем

Результат:
Выполним сортировку в обратном алфавитном порядке в столбце А, выделим столбец «ФИО» (A2:A9)
Главная
Результат:
Фильтрация данных
Фильтрация – это скрытие лишних данных в таблице, при этом на экране остаются только те записи, которые удовлетворяют заданным критериям.
Суть ее в том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным условиям. Условий может быть много, они могут быть простыми и сложными, связанными друг с другом или независимыми. Существует несколько способов фильтрации списков в Excel.
Способ 1. АвтофильтрОтфильтровать список автофильтром - значит скрыть все строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в меню

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

Из исходной таблицы получим следующую таблицу:

Способ 2. Пользовательский автофильтрМожно использовать автофильтр для выборки записей по более сложным условиям. Допустим, необходимо выбрать из списка строки, где зарплата сотрудника лежит в диапазоне от 10000 до 15000 или меньше/больше определенной величины.
Выделить диапазон F2:F9



Из исходной таблицы получим следующую таблицу:

Способ 3. Расширенный фильтр
В отличие от Автофильтра и Пользовательского автофильтра - Расширенный фильтр практически не имеет ограничений на количество условий, налагаемых на список. Но требует некоторых подготовительных операций. А именно:
В любую свободную область (лучше всего вставить над списком несколько пустых строк) нужно скопировать из "шапки" списка заголовки столбцов, по которым будут вводиться условия.
Затем, ниже скопированных заголовков в пустые ячейки вводятся условия для фильтрации. Условия, введенные в ячейки одной строки Excel, будет связывать логическим "И", а в ячейки разных строк - логическим "ИЛИ".


Задания профессиональной направленности
Задание 1 (дисциплина «Строительные конструкции», специальность 270802 (СиЭЗиС))
Подобрать сечение стропильной ноги из бревна, если пролет l=4,2 м, шаг стропил a=1,2 м, расчетная нагрузка на 1 м2-qм2=1991 Н/м, нормальная нагрузка 1 м2-qnм2=1436, уклон кровли -=25, обрешетка из досок сечением b×h=30×150 мм, коэффициент надежности по назначению n=0,95, коэффициент f=1,1, p=500, E=107.Диаметр стропильной ноги = 16 см.
Создадим таблицу:

Для вычисления функции COS, аргумент функции необходимо перевести в радианы, т. е. угол (град.) умножить на коэффициент QUOTE
Формулы:
Полная расчетная нагрузка на 1 пог.м горизонтальной проекции стропильной ноги q;
Полная нормативная нагрузка на 1 пог.м горизонтальной проекции стропильной ноги qn QUOTE ;
С20:=ОКРУГЛ(((C7*C6+C14*(ПИ()*СТЕПЕНЬ(C16/100;2))/(4*COS(C9*ПИ()/180))*C13*10)*C12)/1000;1)
QUOTE
C21: =ОКРУГЛ((C8*C6+C14*(ПИ()*СТЕПЕНЬ(C16/100;2))/(4*COS(C9*ПИ()/180))*10)/1000;1)
Максимальный изгибающий момент QUOTE
С22: =ОКРУГЛ((C20*СТЕПЕНЬ(C5;2))/8;1)
Требуемый момент сопротивления из условий прочности на изгиб QUOTE
С23: =ОКРУГЛ(C22/C18*1000;0)
Требуемый диаметр бревна QUOTE
С24: =ОКРУГЛ(СТЕПЕНЬ(10*C23;1/3);1)
Расчётный диаметр в середине пролёта QUOTE
С25: =ОКРУГЛ(C17+0,8*(C5/(2*COS(C9*ПИ()/180)));2)
Геометрические характеристики отёсанного бревна
С26: =ОКРУГЛ(0,096*СТЕПЕНЬ(C25;3);0) QUOTE
С27: =ОКРУГЛ(0,0476*СТЕПЕНЬ(C25;4);0) QUOTE
Нормальное напряжение QUOTE
С28: =ОКРУГЛ(C22/C26*1000;1)
Сравниваем QUOTE
С29: =ЕСЛИ(C28<=C18;"Прочность по нормальному сечению обеспечена, сечение выбрано верно"; "Прочность по нормальному сечению не обеспечена, сечение выбрано неверно")
Максимальный прогиб в середине пролёта QUOTE
С30: =ОКРУГЛ((5*C21*СТЕПЕНЬ(C5;4))/(384*C15*C27/10000000*COS(C9*ПИ()/180))*1000;1)
Предельно допустимый прогиб
С31: =(C5-3)/3*50 QUOTE
С32: =ОКРУГЛ(C5/(150+C31)*100;1) QUOTE
Проверяем QUOTE
С33: =ЕСЛИ(C30<C32;"Жёсткость обеспечена" ;"Жёсткость не обеспечена, необходимо увеличить диаметр бревна (d)")
В этом случае необходимо взять больший диаметр d0.
Задание 1.2
Составить расчётную таблицу для решения задачи № 1 «Проверить сечение обрешетки под кровлю из металлочерепицы», с. 6, [5]
Задание 1.3
Составить расчётную таблицу для решения задачи № 3 «Подобрать сечение двухпролётной стропильной ноги», с. 14, [5]
Задание 1.4
Составить расчётную таблицу для решения задачи № 4 «Проверить сечение стропильной ноги на прочность и жёсткость», с. 16, [5]

Дополнительные задания (область применения: профессиональная деятельность)
Задание 2 Составить таблицы следующего вида на разных листах:
Справочник автомашин (лист 1)
Государственный номер Марка автомобиля Средний месячный пробег (K)
Справочник стоимости (лист 2)
Марка автомобиля Начальная стоимость (S)
Заполнить первые две таблицы (марка автомобиля может повторяться). С помощью справочных таблиц должна автоматически заполняться результирующая таблица.
Расчет стоимости автомобиля с учетом амортизации (лист 3)
Государственный номер Сумма амортизационных отчислений в месяц (А) Стоимость с учетом амортизации (B)
Сумма амортизационных отчислений за месяц рассчитывается по формуле:
A = S*K / 10000*10/100
Стоимость с учетом амортизации рассчитывается по формуле:
B = S – А
Построить диаграмму распределения стоимости с учетом амортизации по маркам автомобилей, автоматически корректируемую при изменении данных в исходной таблице.
Вывести государственный номер машины, имеющий максимальную стоимость, минимальную стоимость.
Порядок выполнения:
Для автоматизации заполнения необходимо при заполнении 2 таблицы делать ссылку на лист1: А2:=Лист1!В2, далее скопировать формулу в остальные ячейки столбца;
При заполнении 3 таблицы делать ссылку на лист 1 для заполнения столбца «Государственный номер»:
А2:=Лист1!А2 и ссылки на листы 1 и 2 для использования исходных данных в вычислениях;
Переименовать листы: ПЩ по ярлыку листа→Переименовать→Ввести новое название листа
Лист 1 → «Справочник автомашин»
Лист 2 → «Справочник стоимости»
Лист 3 → «Расчет стоимости автомобиля»
В ячейку В2 (Лист 3) ввести формулу: = 'Справочник стоимости'!B2*'Справочник автомашин'!D2/10000*10/100. Скопировать формулу в остальные ячейки (В3:В6);
В ячейку С2 (Лист 3) ввести формулу: = 'Справочник стоимости'!B2-'Расчет стоимости автомобиля'!B2. Скопировать формулу в остальные ячейки (С3:С6);
В ячейку С8 ввести формулу для определения максимальной стоимости а/м;
В ячейку С9 ввести формулу для определения номера а/м с максимальной стоимостью:
=ИНДЕКС(A2:C6;ПОИСКПОЗ(МАКС(C2:C6);C2:C6;0);1);
Диаграмма:
Выделить диапазон С1:С6 (столбец);
Вставка →Диаграмма → Все типы диаграмм → Линейчатая → Горизонтальная цилиндрическая с группировкой;
Удалить легенду (выделить → Delete);
Редактировать название: выделить название, редактировать;
Изменить подписи оси: Конструктор → Выбрать данные → Подписи горизонтальной оси (категории) → Изменить → Выделить данные столбца без заголовка «Марка а/м» на листе2 (А2:А6);
Изменить стиль диаграммы: Конструктор → Стиль 35
Подписать точки данных: Макет → Подписи данных → Показать

Образец выполнения:
Лист 1 «Справочник автомашин»

Лист 2 «Справочник стоимости»

Лист 3 «Расчет стоимости автомобиля»

Диаграмма

Задание 3
Составить таблицы следующего вида для 5 а/м:
Справочник грузоперевозки
Код марки автомобиля Марка автомобиля Масса груза M Расстояние L
Справочник автомашин
Марка автомобиля Стоимость 1т.км (N)
Заполнить первые две таблицы (марка автомобиля может повторяться). С помощью справочных таблиц должна автоматически заполняться результирующая таблица.
Стоимость перевозок
Код марки автомобиля Стоимость перевозки S
Стоимость перевозки рассчитывается по формуле: S= N* M* L
Вывести марку автомобиля, имеющую максимальную стоимость перевозки, минимальную стоимость перевозки.
Задание 4
Составить таблицы следующего вида для 5 наименований:
Сроки проведения работ
Наименование работы Дата начала Дата окончания
Справочник характеристик работ
Наименование работы Группа Бригада Нормативный срок
Заполнить первые две таблицы. При заполнении второй таблицы следует учесть, что группа товара и бригада могут повторяться. С помощью справочных таблиц должна автоматически заполняться результирующая таблица.

Расчетные данные по опережению и отставанию в проведении работ
Наименование
работыФактическая продолжительность, дн. Отставание, дн. Опережение, дн.
Построить объемную столбиковую диаграмму отставания или опережения по бригадам, которая должна автоматически корректироваться при изменении данных в исходной таблице. Вывести наименования групп работ (столярные, сантехнические и т.п.) с максимальным отставанием.
Задание 5
Составить таблицы следующего вида:
Справочник расценок
Код работы Наименование работы Расценка (руб/ед)
Справочник бригад
Табельный номер Фамилия Код бригады
Справочник, выполнения работ
Табельный номер Код бригады Код работы Выполненный объем КТУ
Заполнить первые три таблицы (код бригады и код работ могут повторяться). КТУ (коэффициент трудового участия) может иметь значения от 0 до 1. С помощью справочных таблиц должна автоматически заполняться результирующая таблица.
Ведомость начислений
Табельный номер Начислено сдельно Начислено КТУ Всего
Расчет «Начислено КТУ» выполнить по формуле:
Начислено КТУ= Премия на бригаду * КТУ рабочего/ КТУ бригады
Вывести фамилии рабочих, имеющих минимальный КТУ.
Список литературы и информационных источников:
ГОСТ 7.32-2001 «Отчет о научно-исследовательской работе. Структура и правила оформления»
ГОСТ 7.1-2003 «Библиографическая запись. Библиографическое описание. Общие требования и правила составления»
ГОСТ 7.80-2000 «Библиографическая запись. Заголовок. Общие требования и правила составления»
ГОСТ 7.82—2001 «Библиографическая запись. Библиографическое описание электронных ресурсов»
Лазарев Д. Презентация: Лучше один раз увидеть!– М.: Альпина Бизнес Букс, 2009
Единая коллекция цифровых образовательных ресурсов – http://schoolcollection.edu.ruЕдиное окно доступа к образовательным ресурсам – http://window.edu.ruИнформационная система «Единое окно доступа к образовательным ресурсам» (Информационно-методическое пособие для учреждений общего образования) – http://catalog.iot.ru/pdf/window_edu_ru.pdfКаталог образовательных ресурсов «Школьный мир» – http://www.myschools.ruКаталог электронных образовательных ресурсов – http://fcior.edu.ru
Видеоуроки «ИнтернетУрок» – http://interneturok.ruИнформатика и информационные технологии в образовании – http://www.rusedu.infoИнформатика и информационные технологии: cайт лаборатории информатики МИОО – http://iit.metodist.ruИнформатика и информация: сайт для учителей информатики и учеников – http://www.phis.org.ru/informatikaМатериалы к урокам информатики (О.А. Тузова, С.-Петербург, школа № 550) – http://school.ort.spb.ru/library.htmlЭлектронные учебники по HTML, Word, Excel, VBA – http://www.on-lineteaching.comОфициальный сайт профессионального IT-тренера, разработчика и эксперта по программам пакета Microsoft Office Николая Павлова http://www.planetaexcel.ru/