Учебно-методическое пособие

МИНИСТЕРСТВО ОБРАЗОВАНИЯ ПЕРМСКОГО КРАЯ

ГБОУ СПО СТРОГАНОВСКИЙ КОЛЛЕДЖ

ФИЛИАЛ Г. ОХАНСК

О.А.Пешкова

Основы работы вExcel

Лабораторные работы

и практикум по информатике

и информационным технологиям

Данное учебное пособие разработано для проведения самостоятельных лабораторных и практических работ по предмету «Информатика и ИКТ» в учреждениях НПО и СПО.

Пособие адресовано обучающимся для самостоятельного овладения навыками работы на ПК, сдачи зачетов и ликвидации пробелов.

Рецензенты:_____________________________________________________

_____________________________________________________

ВВЕДЕНИЕ

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

В настоящее время жизнь предъявляет к выпускникам НПО и СПО достаточно высокие (и часто весьма специфические) требования к знаниям, умениям и навыкам, необходимым для обучения естественнонаучным и техническим специальностям.

Основная функция данного методического пособия – научить практическим навыкам работы на персональном компьютере, освоить методы работы с программным обеспечением ПК, применять изученное на практике.

Автор выражает надежду, что пособие будет полезным и преподавателям, и студентам для успешного овладения практическими навыками.

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

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

В текст можно включать дополнительные элементы (колонтитулы, сноски, ссылки, закладки, примечания, предметные указатели), графические объекты, внешние программы.

MSExcel дает возможность «автоматизировать» документы, включая специальные элементы – поля, номера страниц, даты, формулы и т.п. Осуществляет связь между листами за счет перекрестных ссылок.

Приложение имеет стандартный для Windows интерфейс пользователя, основным элементом которого является окно приложения. В процессе работы используется меню, панели инструментов, элементы управления диалоговых окон. При работе эффективно используется мышь (выделение и активация объектов, копирование и перемещение, изменение размеров и формы, вызова контекстного меню).

Орфографический (лексический) контроль проводится автоматически с помощью «Сервис» или функциональной клавишей F7.

Электронные таблицы Excel представляют богатые возможности по представлению информации различного типа в виде таблиц, а также средства её анализа. Приведенные ниже задания позволяют ознакомиться с основными средствами программы, получить навыки работы с Excel

Электронные таблицы

Электронные таблицы (ЭТ) – инструмент для табличных расчетов. Прикладные программы, позволяющие пользователю работать с ЭТ, называются табличными процессорами. ЭТ состоит из прямоугольных клеток – ячеек. Горизонтальные ряды образуют строки, вертикальные – столбцы. Для наименования ячеек используются буквы латинского алфавита и цифры: A1,B7….После столбца с именем Z следуют столбцы с именем AA,AB….Этими параметрами определяется адрес ячейки.

Основное свойство ЭТ: изменение числового значения в ячейке приводит к мгновенному пересчету формул, содержащих имя этой ячейки.

Правила записи формул:

- формулы содержат числа, имена ячеек, знаки операций, круглые скобки, имена функций;

- арифметические операции: сложение (+); вычитание (-); умножение (*); деление (/); возведение в степень (^);

- формула пишется в строку, символы последовательно выстраиваются друг за другом, проставляются все знаки операций; используются круглые скобки;

- для правильной записи формул необходимо учитывать последовательность выполнения арифметических действий;

- операции одинакового старшинства выполняются в порядке их записи;

- для записи чисел применятся две формы: обычная (25; 4,23; 0,2) и экспоненциальная (0,5е7, что означает 0,510⁷);

- в качестве разделителя целой и дробной части используется запятая

- формула всегда начинается со знака «=»

Заполнение ЭТ начинается с занесения в неё пояснительных текстов, заголовков и расчетных формул. Числовые данные заносятся позже.

ЭТ с которыми работает пользователь в приложении называются рабочими листами, множество рабочих листов образуют книг, в каждой книге 225 рабочих листов.

Электронные таблицы позволяют обрабатывать большие массивы числовых данных (результаты экспериментов, статистические данные)

Самостоятельная работа № 1

«Запись математических выражений в электронном виде»

Во всех формулах предполагается следующее расположение переменных величин в ячейках таблицы: х – А1; у – В2; z – С3, записать математические выражения в электронном виде, а затем ввести их в ЭТ и выполнить расчет. После выполнения расчета поменять значения переменных и сделать вывод.

а) 2х + 3,5у² б) в) г) х⁴ + у³ – z²

Практическая работа № 1 «Оформление расчетов в ЭТ»

Задание 1

Составим ЭТ под названием «Накладная на покупку канцелярских товаров»

Осуществим ввод данных по схеме:

Для расчета стоимости товара, ввести формулу. В ячейке Е10 подсчитать итоговую сумму покупки. Для расчета доли покупки необходимо стоимость конкретного товара поделить на общую сумму покупки.

После вычислений отформатировать ЭТ: информацию внутри таблицы выровнять по центру, слово «Итого» - по правому краю, в столбце «Доля покупки» уменьшить разрядность до двух цифр после запятой.

Задание 2

Составить самостоятельно ЭТ под названием «Объем продаж».

Задание 3 Представить формулы в электронном виде и выполнить расчеты, придав х различные значения

а) У = - Х³ + 1; б) У = SIN Х – 1 в) У = (задайте х = 0 и сделайте вывод)

Задание 4

Выполнить начисление заработной платы по следующим данным: количество рабочих дней в месяце – 22 дня.

Относительная и абсолютная адресации

Блоком (фрагментом, диапазоном) таблицы называется любая прямоугольная часть ЭТ. Блок обозначается именами диагонально-противоположных ячеек, разделённых двоеточием В2:D3.

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

Абсолютная адресация применяется, если заданное значение необходимо сохранить без изменений – это так называемое, замораживание. Для этой цели в имени ячейки употребляется символ $. Для замораживания всего адреса значок $ ставится дважды ($B$1). Можно заморозить только столбец ($B2) или только строку (B$2). Тогда часть адреса будет изменяться при переносе формулы, а часть нет.

Практическая работа № 2 «Относительная и абсолютная адресации»

Задание: Оформить ЭТ, ввести соответствующие формулы и выполнить расчеты при помощи относительной и абсолютной адресации

Таблица «Цена путёвок в туристическом агентстве»

Самостоятельная работа № 2

«Абсолютная и относительная адресации»

Задание 1

По данным создать документ Excel «Ремонт квартиры», внести необходимые данные.

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

Оформить счет на оплату материалов и услуг по ремонту квартиры, перенести его в Word.

Данные:

Площадь пола – 120 кв.м.; Площадь стен – 459 кв.м.; количество дверей – 3; количество окон – 6.

Один рулон обоев имеет размеры – 10  0,5 м; стоимость одного рулона 42,50 рубля;

Клей: 1 коробка рассчитана на 50 кв.м, стоимость коробки клея – 38 рублей.

Краска для пола: 1 банка 3 кг; расход краски – 200 гр на 1 кв.м; стоимость банки краски 170 рублей;

Стоимость работ по оклейке стен 10% от стоимости материалов; стоимость работ по окраске полов – 5% от стоимости расходных материалов.

Смена дверей: цена 1 двери 2700 рублей, стоимость работ – 15% от стоимости всех дверей;

Смена оконных блоков: цена одного оконного блока – 1200 рублей, расходные материалы – пена 5 баллонов по цене 78 рублей за баллон; стоимость услуг за смену блоков 15% от стоимости всех материалов.

Задание 2

Составить прайс-лист «Список товаров фирмы «Мир компьютеров» в ЭТ.

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

Курс доллара – 22,60 рубля. Курс евро – 36,40 рубля.

Комплектующие:

Материнская плата 1500 рубля материнская плата 1500 рублей

Материнская плата 2000 рубля материнская плата 2000 рублей

Видеокарта 700 рублей видеокарта 1200 рубля

Звуковая плата 950 рублей звуковая плата 950 рублей

Дисковод 450 рублей CD-ROM 1200 рублей

CD-ROM 1000 рублей

Корпус 1000 рублей

Корпус 1500 рублей

Монитор (15”) 5700 рублей

Монитор (17”) 6200 рублей

Процессор 4000 рублей

Процессор 5800 рублей

Оперативная память 2000 рублей

Жесткий диск 850 рублей

Жесткий диск 1500 рублей

Клавиатура 850 рублей

Клавиатура 1200 рублей

Мышь 180 рублей

Мышь 270 рублей

Персональный компьютер 12800 рублей

Персональный компьютер 22900 рублей

На каждый Персональный компьютер произвести 10% наценку от стоимости за сборку. Цены должны быть округлены до целых.

На прайс-листе написать название фирмы, вставить логотип (можно с помощью команды «Вставка-Символ»), впечатать реквизиты (адрес, телефон, факс, e-mail и т.д.)

Стандартные функции и табулирование функции

При записи формул в ЭТ можно использовать стандартные функции (встроенные функции). Все множество встроенных функций делится на группы: математические, статистические, функции даты и времени и др.

Табулирование функции – одна из часто решаемых прикладных задач. Табулирование означает построение таблицы значений функции для значений аргумента, изменяющихся в определённом интервале с данным шагом.

Табулирование позволяет исследовать функцию: проследить характер изменения, выделить области корней, определить экстремальные значения. Применяя приём копирования, в ЭТ можно быстро построить таблицу значений большого размера. Для этого достаточно ввести две начальные строки таблицы и затем скопировать их вниз на нужное количество строк.

Например:f(x) = x³ – 2x с шагом 0,2, начиная от значения 0, имеет ввод таблицы

Самостоятельная работа № 3

Вычислить значение функции при заданном х, выписать в тетрадь полученные ответы:

а) при х = 4,2 б) при х = 0,2

в) при х = г) при х = 5,002

д) при х = 0,001

Практическая работа № 3 «Табулирование функции»

На отрезке [-3.14; 3.14] с шагом 0,2 протабулировать функцию.

На отрезке [0; 2] с шагом 0,2 протабулировать функцию

На отрезке[2; 3] с шагом 0,1 протабулировать функцию

На отрезке [1; 2] с шагом 0,1 протабулировать функцию

На отрезке [1; 3] с шагом 0,1 протабулировать функцию

2. Выполните расчеты по исчислению выручки:

Зачетная работа № 1

Задание 1 (лист 1 – математические вычисления)

Выполнить указанные вычисления:

Найти квадрат числа Х; куб числа Х.

Найти значения функций и построить их графики: у = 2*х + 5; у = 4х² – 0,2х – 0,25.

Найти периметр треугольника, если все его стороны известны.

Найти площадь прямоугольного треугольника, если известны его стороны.

Задание 2 (лист 2 – табулирование функции)

На отрезке [1,2; 2] с шагом 0,1 протабулировать функцию у = х – 2 + sin(1/х).

Представление информации в графическом виде

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

Для создания диаграммы на рабочем листе следует:

На рабочем листе выделить ячейки, содержащие исходные данные, включая описание (заголовки);

Щелкнуть по кнопкеМастер диаграмм стандартной панели инструментов;

В открывшемся диалогом окне выбрать нужный тип диаграммы – Далее – выполнить все инструкции диалоговых окон (выбрать диапазон данных, формат диаграммы, режим отображения названий категорий и текста легенды)

После ввода всех параметров – Готово

Чтобы разместить диаграмму на отдельном листе, нужно:

На рабочем листе выделить ячейки с исходными данными и выполнить команду Диаграммаменю Вставка и задать место размещения На новом листе;

Выбрать в открывшемся окне нужный тип диаграммы и продолжить определение её параметров в диалоговых окнах.

Изменить вид диаграммы можно с помощью контекстного меню через правую кнопку мыши Редактировать объект. После этого во вновь вызванном контекстном меню появятся команды редактирования диаграммы. Тоже меню можно активизировать дважды щелкнув по диаграмме.

Практическая работа № 4 «Построение диаграмм»

Постройте графики следующих функций:

А) у = sin(x)/x, поскольку при х = 0 значение этой функции не определено, замените в таблице значений аргумента 0 на 0,0001.

Б)у = arcsin(sin(x))

Построить диаграммы разных видов:

А) Посетители международной выставки парфюмерии и декоративной косметики «WORLDPARFUM»

Б) Курс доллара и евро в рублях с 22 по 29 июля 2010 года.

В) стоимость участков земли под строительство в ближайшем Подмосковье

Г) Планеты Солнечной системы

Дизайн электронных таблиц

Использование обрамления и заливки

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

Для добавления обрамления выполняется командаФормат-Ячейка, в открывшемся диалоговом окне выбрать вкладку Рамка (Граница), после этого выбрать толщину линий обрамления, цвет и указать границы обрамления.

Цвет и фон выделенных ячеек устанавливается с помощью Цвет фона (Цвет заливки) панели инструментов форматирования, аналогично меняется цвет шрифта.

Практическая работа № 5 «Оформление таблиц и диаграмм»

Задание: из данной таблица составить отдельные таблички по характеристике температуры, давления, ветра и облачности. Оформить таблицы разными стилями, обрамлением и заливкой. Каждую характеристику оформить на отдельном листе книги. На каждую характеристику погоды оформить на отдельных листах графики, диаграммы, гистограммы (плоские и объемные).

Цель работы:представить информацию в красивом, наглядном виде.

Погода в мае месяце 2006 года

Составить таблицу «Изменение температуры воздуха в мае 2006 г» и построить график относительно двух осей координат с соответствующей легендой.

Составить таблицу «Изменение влажности воздуха в мае 2006 г» и построить график относительно двух осей координат с соответствующей легендой.

Составить таблицу «Изменение атмосферного давления в мае 2006 г» и построить график относительно двух осей координат с соответствующей легендой.

Составить таблицу «Облачность в мае 2006 г», построить круговую диаграмму (плоскую и объемную) и гистограмму с соответствующей легендой.

Составить таблицу «Направление ветра в мае 2006 г» и построить Розу ветров (лепестковая диаграмма).

Визуализация многорядных данных

Дана таблица успеваемости учащихся по отдельным предметам

Построитьстолбчатую диаграмму, представить в ней данные сразу обо всех учениках. Опорными точками являются имена учеников. В каждой опорной точке строится группа из четырех столбиков. Сравнение результатов проводится внутри прямоугольников (по данному предмету) и между прямоугольниками (между учащимися).

Вторая диаграмма – многоярусная,третья – областная диаграмма илидиаграмма площадей.

Практическая работа № 6 Статистическая обработка данных»

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

Задача 1.

Один стакан лимонада содержит 15 калорий, 1 кусок торта – 150 калорий, 1 драже «Тик-так» - 2 калории. Во время праздничного обеда Буратино выпил 5 стаканов лимонада, съел 20 драже «Тик-так» и 4 куска торта.

Мальвина съела 2 драже, 1 кусок торта и выпила 1 стакан лимонада. Пьеро выпил 2 стакана лимонада и съел 2 куска торта. Дуремар съел 3 куска торта и выпил 2 стакана лимонада.

Построить электронную таблицу, из которой будет видно: сколько всего стаканов лимонада выпито; кусков торта и драже «Тик-так» съедено; сколько калорий употребил каждый участник праздничного обеда; сколько калорий содержалось во всем выпитом лимонаде и съеденном торте и драже.

Задача 2.

Компания по обслуживанию жилых домов установила тарифы за свои услуги: 1 литр воды стоит 2 монеты; 1 кВт/ч электроэнергии 0,15 монеты; 1 кубометр газа – 5 монет. Иванов израсходовал за месяц 300 литров воды, 60 кВт/ч электроэнергии и 0,5 кубометров газа. Петров – 50 литров воды, 200 кВт/ч электроэнергии. Сидоров - 150 литров воды, 160 кВт/ч электроэнергии и 0,2 кубометра газа, Смолин – 200 литров воды и 0,3 кубометра газа. Сколько всего израсходовано воды, электроэнергии и газа всеми жильцами; сколько заплатил каждый жилец за предоставленную услугу; какой доход получила компания в целом?

Задача 3.

В кондитерской решили испечь три новых торта: ореховый, яблочный и шоколадный. Для приготовления одного яблочного торта требуется 200 грамм сливочного масла, 200 грамм муки, 2 яйца, 300 грамм сахара и 0,8 кг яблок. Для приготовления одного орехового торта требуется: 200 грамм орехов, 400 грамм муки, 300 грамм сахара , 300 грамм сливочного масла и 3 яйца. На один шоколадный торт: 3 шоколадки по 100 грамм, 3 яйца, 300 грамм муки, 200 грамм масла и 100 грамм сахара. Стоимость продуктов за кг: сахар – 42 рубля; мука – 28 рублей, орехи – 150 рублей, яблоки - 75 рублей, масло сливочное – 170 рублей.

1 десяток яиц – 29 рублей, 1 шоколадка – 32 рубля

Сколько будет стоить каждый торт? Сколько продуктов необходимо на приготовление тортов и на какую сумму? Какова будет выручка кондитерской, если каждого наименования будет изготовлено по 10 штук?

Практическая работа № 7 «Статистическая обработка данных»

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

Задание 1.

За I и II кварталы 2010 года выполнение плана по трем цехам машиностроительного завода составило: 1 цех: 98% и 99,9% соответственно, во 2 цехе: 100% и 90% соответственно и в 3 цехе 100% и 105% соответственно. Совершено прогулов по инициативе работников: 1 цех – 12 и 24; 2 цех – прогулов нет, 3 цех – 12 и 10 прогулов. Простои по вине администрации составили: 1 цех – 14 и 12 дней; 10 и 7 дней, в 3 цехе простоев не было. Оформить таблицу по результатам. Вывести среднее значение выполнение плана, прогулов по вине работников и простоев по вине предприятия. Результаты отобразить в виде графиков и диаграмм. При вычислении использовать встроенные функции.

Задание 2

Используя набор данных «Территория и население по континентам» составить таблицу и выяснить минимальную и максимальную плотность населения в 1970 году и в 1989 году, суммарную площадь всех континентов.

Территория и население по континентам

Территория Австралии и Океании – 8,5 млн.км. Плотность населения в Африке в 1989 году была 21 млн. человек на кв.км. Население Европы в 1989 году составило 701 млн. человек. Территория Южной Америки – 17.8 млн.кв.км. Население Северной и Центральной Америки в 1989 году составило 422 млн.человек. Плотность населения в Северной и Центральной Америке в 1970 году была 13 млн. человек на кв.км. Территория всего мира – 135,8 млн.кв.км. Плотность населения в Австралии и Океании в 1989 году была 3 человека на кв.км. Население Южной Америки в 1989 году составило 291 млн.чел. Территория Африки – 30,3 млн.кв.км. Населением Австралии и Океании в 1989 г. составило 26 млн.чел. Плотность населения во всем мире в 1970 г. была 27 чел. на кв.км. Территория Азии – 44,4 млн. кв.км. Население всего мира в 1989 г. составило 5201 млн.чел. Территория Северной и Центральной Америки – 24,3 млн. кв.км. Население Азии в 1970 г. составило 2161 млн. чел. Плотность населения в Европе в 1989 г. была 67 чел. на кв.км. Плотность населения в Азии в 1970 году была 49 чел. на кв.км. Население в Африке в 1970 г. составило 361 млн.чел. Население Австралии и Океании в 1970 г. составило 19 млн. чел. Население Южной Америки в 1970 г. составило 190 млн.чел. Плотность населения в Африке в 1970 г. была 12 чел на кв.км. Население Северной и Центральной Америки в 1970 г. составило 320 млн.чел. Плотность населения в Южной Америке в 1970 г. 11 человек на кв.км. Население Африки в 1989 г. составило 628 млн.чел. Плотность населения в Австралии и Океании в 1970 г. была 2 чел. на кв.км. Население Европы в 1970 г. составило 642 млн.чел. Плотность населения во всем мире в 1989 г. была 38 чел на кв.км. Территория Европы – 10,5 млн.кв.км. Плотность населения в Северной и Центральной Америке в 1989 г. была 17 чел. на кв.км. Плотность населения в Европе в 1970 г. была 61 чел. на кв.км. Население Азии в 1989 г. составило 3133 млн. чел. Плотность населения в Южной Америке в 1989 г. была 16 чел. на кв.км. Население всего мира в 1970 г. составило 3693 млн. чел. Плотность населения в Азии в 1989 г. была 71 чел. на кв.км.

Задание 3

Используя набор данных «Важнейшие судоходные каналы», составить таблицу и выяснить суммарную длину каналов, среднюю глубину каналов в фарватере, минимальную и максимальную ширину, Построить столбчатую диаграмму « Сравнение длин каналов»

Построить круговую диаграмму « Фарватеры каналов с пояснением»

Важные судоходные каналы

Волго-Балтийский водный путь имеет ширину 25-120 м. Южный канал находится во Франции. Панамский канал по­строен в 1914 r. Суэцкий канал имеет длину 161 км. Кильский канал построен в 1895 г. Южный канал имеет глубину на фарватере 2 м. Великий (Даюньхэ) канал находится в Китае. Канал Рона-Рейн имеет длину 320 км. Беломоро-Балтийский канал находится в России. Панамский канал имеет ширину 150-305 м. Береговой канал имеет длину 5580 км. Южный канал имеет

длину 241 км. Волго-Балтийский водный путь имеет длину 1100 км. Канал Рона-Рейн имеет ширину 25-100 м. Суэцкий канал находится в Египте. Панамский канал имеет длину 82 км. Береговой канал построен в 1972 г. Великий (Даюньхэ) канал имеет длину 1782 км. Волго-Балтийский вод­ный путь имеет глубину на

фарваторе 4 м. Среднегерманский канал имеет ширину 30-40 м. Кильский канал имеет ширину 104-214 м. Беломоро-Балтийский канал имеет глубину на фар­ватере 5 м. Береговой канал находится в США. Суэцкий канал имеет глубину на фарватере 16 м. Панамский канал находится в Панаме. Панамский канал имеет глубину на фарватере 12 м. Кильский канал имеет длину 99 км. Беломоро-Балтийский канал построен в 1933 г. Береговой канал имеет ширину 40-60 м. Среднегерманский канал построен в 1938 г. Кильский канал имеет глубину на фарватере 11 м. Волго-Балтийский водный путь построен в 1810 г. Канал Рона-Рейн находится во Франции. Великий (Даюньхэ) канал имеет глубину на фарватере 2-3 м. Суэцкий канал имеет ширину 120-318 м. Среднегерман­ский канал находится в ФРГ. Южный кaнал построен в 1681 г. Канaл Рона-Рейн имеет глубину на фарватере 2 м. Средне­германский канал имеет длину 325 км. Беломоро-Балтийский канал имеет длину 227 км. Береговой канал имеет глубину на фарватере 3-13 м. Великий (Даюньхэ) канал построен в 13 в. Южный канал имеет ширину 20 м. Волго-Балтийский водный путь находится в России. Среднегерманский канал имеет глу­бину на фарватере 3-6 м. Суэцкий канал построен в 1869 г. Великий (Даюньхэ) канал имеет ширину 40-350 м. Кильский канал находится в ФРГ. Канал Рона-Рейн построен в 1833 г.

Сортировка и поиск данных

Практическая работа № 8 «Сортировка и поиск данных»

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

Числа сортируются от наименьшего отрицательного до наибольшего положительного;

Текст сортируется в следующем порядке: числа, знаки, латинский алфавит, русский алфавит;

Пустые ячейки всегда перемещаются в конец списка.

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

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

Сортировка данных.

Выделить одну из ячеек с данными и ввести командуДанные-Сортировка…

На диалоговой панели Сортировка диапазона в списке Сортировкапо выбрать столбец (название первого столбца) и установить переключатель в положениепо возрастанию (по убыванию)

В списке Затем по установить название второго столбца и т.д.

3. В списке В последнюю очередь по выбрать последний столбец и снова установить порядок сортировки – ОК.

Поиск данных

В ЭТ можно осуществлять поиск данных (строк) в соответствии с заданными условиями. Такое условие называется фильтром. В результате поиска будут найдены строки, удовлетворяющие заданному фильтру.

Условия задаются с помощью операций сравнения. Для числовых данных это операции: равно (знак =), меньше (знак <), больше (знак >), меньше или равно (знак ) и больше или равно (знак >=). Для задания условия необходимо выбрать операцию сравнения и задать число.

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

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

Ввести командуДанные-Фильтр-Автофильтр. В названиях столбцов появятся раскрывающиеся списки, содержащие стандартные условия поиска, выбрать пункт Условие… для ввода условий;

На диалоговой панели Пользовательский автофильтр в полях ввести оператор условия поиска«=» и текстовое условие поиска;

Аналогично вводятся все остальные условия поиска

Задание 1

По следующим данным произвести сортировку и поиск данных:

Выполнить сортировку данных столбца А по возрастанию, столбца В по убыванию, столбца С по убыванию

Найти данные о настольном компьютере с процессоромPentiumIV и памятью 128 Мб

Задание 2

Скопировать таблицу «Территория и население по континентам» и отсортировать континенты по возрастанию численности населения.

Найти данные о численности населения на американском континенте в 320 млн. чел и плотностью населения 16 человек на кв.км. Данные какого года удовлетворяют этому условию?

Практическая работа № 9 «Условная функция и логические выражения»

Условная функция имеет следующий вид: ЕСЛИ (<условие>, <выражение 1>, <выражение 2>).

Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. Выражение 1, выражение 2 – могут быть числами, формулами и текстом.

Условная функция записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной ячейки определит <выражение 1>, в противном случае - <выражение 2>.

Логические выражения строятся с помощью операций отношения (<, >, , >=, =, <> (не равно) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические величиныИСТИНА или ЛОЖЬ.

Существуют особенности записи логических операций: сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.

Задание1:

Разработать таблицу, содержащую сведения об абитуриентах: фамилия, оценки за экзамены (математика, русский язык, иностранный язык), сумма баллов за три экзамена и информация о зачислении; если сумма баллов больше или равна проходному баллу и оценка за экзамен по математике 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае нет.

В ячеке А1 – проходной; В1 – балл; С1 – 13; формула в Е3 вычисляет сумму баллов за три экзамен; формула в ячейке F3 задается с помощью условной функции: ЕСЛИ (И(Е3>=$C$1;B3>3); «ДА»; «НЕТ»). Условие записанное с помощью логической операции И, можно расшифровать так: сумма баллов (Е3) больше или равна проходному баллу (С1) И оценка за экзамен по математике (В3)> 3. Если условие выполняется, то в F3 будет отображаться текст – ДА, в противном случае – НЕТ.

Задание 2:

Решить задачу путем построения ЭТ. Сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 160 см и возрастом не старше 13 лет?

2. Десять учащихся проходили тестирование по пяти предметам. Вычислить суммарный (ко всем предметам) средний балл, полученный учениками. Выявить учащихся, которые имеют суммарный балл ниже среднего

Самостоятельная работа № 4

Создание собственных списков, примечаний. Ввод данных в ЭТ через формы

Создание собственных списков.

Набрать следующий список начиная с ячейки А2 в столбец: Абрамова, Дмитриева, Егорова, Железнов, Кулаков, Лобанова, Михалева, Неретин, Токарев, Яковлев, Иванов, Сидоров, Петров, Носков, Фурсенко, Злобин, Громов, Рязанцев, Симаков.

Выделите список

Меню «Сервис» - «Параметры» - вкладка «Списки» - диалоговое окно «Добавить»

В поле «Элементы списка» появится текстовый курсор

Убедитесь, что в поле «Импорт» списка из ячеек указан верный диапазон

Нажмите «Импорт» - «ОК»

Создание примечаний

MSExcel позволяет добавлять текстовые примечания к ячейкам:

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

- рабочий лист большой и сложный

- рабочий лист содержит сложные формулы

При создании примечания в ячейке появляется указатель (красный треугольник).

Добавление примечания: выделить ячейку для примечания; команда «Вставка» - «Примечание» (или добавить примечание контекстным меню), в появившемся окне ввести примечание; щелкнуть мышью за пределами поля.

Изменение примечания: выделить ячейку; меню «Вставка» - «Изменить примечание» (или вызвать контекстное меню)

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

Задание 1 Создать примечания к таблице «Цена путевок туристического агентства»

Для ячейки В11 и В13 создать примечания: В11 – курс евро; В13 – курс доллара. Построить графики «Стоимость путевок в туристическом агентстве в зависимости от валюты» - «Диаграмма» - «Объемный вид» - диалоговое окно «Формат трехмерной проекции»

Ввод данных при помощи формы

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

Выполнение работы:

Сделать активной любую ячейку рабочего листа.

Меню «Данные» - команда «Форма».

В строке заголовка диалогового окна проставлено название рабочего листа (Лист 1), лист заранее переименовать.

Рассмотрите кнопки с правой стороны диалогового окна:

Добавить –очищает поля для ввода новой записи, повторный клик означает добавление новой записи;

Удалить – удаляет выведенную запись, остальные записи сдвигает;

Вернуть –восстанавливает отредактированные поля в выведенной записи;

Назад – выводит предыдущую запись в списке;

Далее – выводит следующую запись;

Критерии –очищает поля перед вводом критериев сравнения;

Правка – выход из режима ввода критериев;

Очистить –удаляет существующий критерий;

Закрыть –закрывает форму данных.

Ход работы:

Ввести названия столбцов по данным задания

Введите все данные с помощью формы

После ввода данных осуществите поиск нужных данных, задавая критерии поиска.

Для этого нажмите кнопку «Критерии» - появится незаполненная форма с заголовками полей, задайте нужный критерий (например, скидка 10%), начните поиск с нажатия «Далее». Одновременно можно задать несколько критериев.

Выполните сортировку данных таблицы. Поле, по которому идет сортировка называется ключевым (ключом). Выделить название столбца и вызвать команду «Данные» - «Сортировка». Параметры сортировки задайте самостоятельно.

Произведите фильтрацию данных – способность видеть не всю информацию, а отобранную по определенному критерию. Для этого выделить диапазон данных, меню «Данные» - «Фильтр» - «Автофильтр» и создание ниспадающих списков. В ниспадающих списках содержатся условия фильтрации, выберите некоторые из них.

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

Самостоятельная работа № 5

Создайте «Телефонный справочник» через формы. Названия полей: фамилия, имя, отчество, адрес, номер телефона. Количество абонентов – не менее 20.

Практическая работа № 10 «Расчет корреляционных зависимостей»

Цель работы:получение представлений о корреляционной зависимости величини освоение способа вычисления коэффициента корреляции с помощью функции КОРРЕЛ.

В таблице содержатся данные о парных измерениях двух величин: температуры воздуха в классе x и доли простуженных учащихся y.

Зависимость носит статистический характер, т.к. нельзя достоверно сказать, что при температуре 15 градусов болеет более 5% учащихся.

Ход работы:

Ввести данные в ЭТ.

Построить с помощьюМастера диаграмм точечную диаграмму

Ответить на вопрос, можно ли на основании этой точечной диаграммы выдвинуть гипотезу о наличии линейной корреляции между величинами.

используя встроенную функцию КОРРЕЛ, найти коэффициент корреляции и подтвердить или опровергнуть гипотезу.

Практическая работа № 11 «Получение регрессионной модели»

Цель работы: освоение способов построения по экспериментальным данным регрессионной модели и тренда.

Ход работы:

Ввести табличные данные для функции у = 2х² + х – 1, где х  [-2; 4] с шагом 0,2.

Сделать необходимые расчеты (тема «Табулирование функции»)

Построить точечную диаграмму

На оси Ох подписать «Линейный тренд»

Выделить точки графика

Вызвать контекстное меню и выбрать команду «Добавить линию тренда»

Самостоятельно построить линии тренда для функции:

у = 21,845 х² – 106,97 х + 150,21, где х [-2.125; 4.025] с шагом 0,002

8. Построить линию тренда по следующей таблице

Практическая работа № 12 «Поиск решения с помощью подбора параметра»

Установка надстроек: ввести команду «Сервис» - «Надстройки». На панели «Надстройки» в списке «Доступные надстройки» выбрать нужные путем установки флажков.

Поиск решения позволяет решать задачи оптимизационного моделирования. Процедура поиска решения позволяет найти оптимальное значение формулы, которая называется целевой.

Подбор параметра является одним из инструментов анализа «что если». Этот метод используется при поиске значения аргумента функции, который обеспечивает требуемое значение функции. Точность подбора зависит от заданной точности представления чисел в ячейках таблицы.

Пример: найдем корень уравнения .

Представим функцию в табличном виде и построим её график, который позволит определить приблизительно корень уравнения (приблизительно х=1). Для поиска решения используется методПодбор параметра.

Самостоятельная работа № 6

«Решение уравнений с помощью подбора параметра»

1. х⁴ - 4х³ – 10х² + 37х – 14 = 0 х  [-4; 5]

2. 2 cos 2x – 0,5 = 0 х [0; 3,14]

Практическая работа № 13 «Создание перекрестных ссылок между листами в Excel»

Создать электронный журнал «Начисления заработной платы на предприятии» используя перекрестные ссылки.

Лист 1 – Начисление заработной платы

Лист 2 – начисление по листкам временной нетрудоспособности

Лист 3 – начисление детских пособий

Лист 4 – ведомость выдачи

Лист 5 – диаграмма сумм выдачи

Данные для начисления:

Построить самостоятельно и сохранить таблицу «Начисления» на листе 1. Она будет базовой. Заполнять можно как саму таблицу, так и через формы.

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

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

Начнем оформлять лист «Детские», на ней выделим ячейку А2 и нажмем «=», перейдем на лист «Начисление» и щелкнем на первой фамилии в списке (В3), ввод. Это переход обратно на лист «Детские», так с каждой фамилией. Аналогично поступаем со столбцом «Количество детей».

Отфильтруем тех, у кого детей нет ( команда «Данные – Фильтр – Автофильтр), выбрать условие больше 0 или не равно 0.

Скроем столбцы «Сумма» и «Число детей» (команда «Формат – Столбец – Скрыть»). Обрамим таблицу

Так же подготовить итоговую ведомость.

Самостоятельная работа № 7

«Создание электронного журнала»

Постановка задачи:

Посчитать пофамильно средний балл, используя, Мастер функции – категория «Статистические»

Округлить полученные значения

Переименовать листы: информатика, алгебра, геометрия, химия, физика, итоговые оценки

Подсчитать количество 5, 4, 3, 2 в годовых оценках

Сохранить «Электронный журнал»

Ведомости оценок по другим предметам сформировать самим, проставляя различные категории оценок этим же учащимся.

Сформировать «Сводную ведомость оценок по всем предметам», с занесением годовых оценок и количества оценок по баллам (от 5 до 2), использовать при этом перекрестные ссылки.

Практическая работа № 14

«Информационное моделирование с помощью формализации»

Формализация –этап информационного моделирования, который включает в себя описание объекта моделирования, осуществленного в рамках заданной формы. Для решения используется команда «Сервис – Поиск решения» и график соответствующей функции.

Задача: Дочери 8 лет, а матери – 38. Через сколько лет мать будет в 3 раза старше дочери?

Составим модель: х – решение задачи, 8 + х – возраст дочери; 38 + х – возраст матери; по условию задачи 3 * (8 + х) = 38 + х

Таблица имеет вид:

Ячейке F1 необходимо сообщить что, изменяя её значение, в ячейке F3 получить 3.

Для этого выполняя команду «Сервис – Поиск решения» заполняется диалоговое окно «Поиск решения». Установить целевую ячейку $F$3 равной значению 3, изменяя ячейку $F$1 – выполнить. Если результат в виде дробного числа, измените формат ячейки и представьте его в целом виде. Если отсутствует «Поиск решения» - используйте «Подбор параметра»

Самостоятельная работа № 8

«Информационное моделирование с помощью формализации»

Решить задачи:

При встрече несколько ребят попарно обменялись рукопожатиями. Всего было 30 рукопожатий. Сколько ребят встречалось?

(модель х*(х – 1)/2 = 30)

Решить уравнение 3,5х + 12,25 = 0

В олимпиаде по информатике принимало участие несколько команд. В одном из конкурсов все команды обменялись заранее подготовленными заданиями (попарно). Всего было передано 90 заданий. Сколько было команд? (модель х*(х – 1) = 90)

В хозяйстве кролики и гуси. Всего 33 головы т 90 ног. Определите количество кроликов и гусей.

Решить систему графическим способом: 4х + у = -1 и у = -2х + 2

для – 3< x < 3с шагом 0,5. Найти координаты точки пересечения.

Зачетная работа № 2

Задание 1

Построить графики зависимости средней дневной температуры за последнюю неделю марта в различных городах европейской части России в зависимости от географической широты.

Задание 2

Создайте таблицу, показывающую рост вклада в банке за пять лет при фиксированном проценте годовых (20%) для вкладчиков, фамилии которых зафиксированы в списке Excel, внести необходимые формулы и произвести вычисления:

Задание 3

Обработать данные в ЭТ: На складе компьютерной техники хранятся компьютеры (150 шт.) по цене 22000 рублей, принтеры (48 шт.) по цене 6000 рублей, сканеры (36 шт) по цене 2800 рублей, модемы (100 шт.) по цене 895 рублей, мониторы (25 шт) по цене 5960 рублей и (15 шт.) по цене 8000 рублей. Продавец Семенов продал 10 сканеров и 5 компьютеров, Павлова продала 2 монитора и 2 компьютера, 12 модемов. Иванов продал 2 сканера, 3 принтера и один принтер. Вычислить объем продаж, выручку, остаток товара и стоимость остатков товара в магазине. Построить объект деловой графики, иллюстрирующий объем продаж по виду товара.

Задание 8

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

«Каждый экземпляр газеты «Извести цветочного города» - 10 монет, «Техническая газета Винтика и Шпунтика» - 7 монет, «Медицинские новости» - 8 монет. «Домоводство» - 12 монет, «Тайны и приключения» - 25 монет, «Мода» - 45 монет. В понедельник Незнайка продал 8 экземпляров «Известий», 7 экземпляров «Технической газеты» и 5 экземпляров «Медицинских новостей». Во вторник: 13 экземпляров «Технической газеты», 4 экземпляра «Известий», 12 экземпляров «Тайны и чудеса», 25 экземпляров «Домоводство». В среду – 5 экземпляров «Мода», 23 экземпляра «Известий», 12 экземпляров «Технической газеты», 5 экземпляров «Медицинских новостей» и 2 экземпляра «Домоводство». В среду: 18 экземпляров «Известий», 17 экземпляров «Технической газеты» и 55 экземпляров «Медицинских новостей». В четверг: 33 экземпляра «Технической газеты», 14 экземпляров «Известий», 32 экземпляра «Тайны и чудеса», 15 экземпляров «Домоводство» В пятницу: всех газет по 10 экземпляров. В Субботу и воскресенье – «Известий» 12 штук, «Технической газеты» - 45 штук, «Медицинские новости» - 4 штуки».

Литература

Угринович Н.Д. Информатика и информационные технологии (учебник), - М.: БИНОМ. Лаборатория знаний, 2007.

Угринович Н.Д. Информатика и информационные технологии (практикум), - М.: БИНОМ. Лаборатория знаний, 2007.

Семакин И.Г., Залогова Л.А. Информатика (базовый курс), - М.: Лаборатория базовых знаний, 2000.

Семакин И.Г., Хеннер Е.К. Информатика (задачник-практикум), - М.: Лаборатория базовых знаний, 2000.

Замятина Е.Б., Лядова Л.Н. Введение в информатику (лабораторный практикум), - Пермь, ПРИПИТ, 2004.

Замятина Е.Б., Лядова Л.Н. Введение в информатику (теоретические основы), - Пермь, ПРИПИТ, 2004.

Замятина Е.Б., Лядова Л.Н. Информационные технологии в обучении (учебное пособие и лабораторный практикум), - Пермь, ПРИПИТ, 2004.

Горячев А.В., Шафрин Ю.А. Практикум по информационным технологиям, -М.: лаборатория базовых знаний, 1999.

Воронкова О.Б. Информатика: методическая копилка преподавателя, - Ростов-на-Дону: Феникс, 2007.

Чернов А.А., Чернов А.Ф. Информатика (элективный курс), - Волгоград, Учитель, 2006.

СОДЕРЖАНИЕ

Введение ………………………………………………………………………….……3

Самостоятельная работа № 1 «Запись формул в электронном виде» ….…………6

Практическая работа № 1 «Оформление расчетов в ЭТ»………………………….6

Практическая работа № 2 «Относительная и абсолютная адресации».…………...8

Самостоятельная работа № 2 «Относительная и абсолютная адресации»…….….9

Самостоятельная работа № 3 «Вычисление значений функций»…………………11

Практическая работа № 3 «Табулирование функции»…………………………....11

Зачетная работа № 1 « Вычисление математических функций»…………………..12

Практическая работа № 4 «Построение диаграмм»………………………..………13

Практическая работа № 5 «Оформление таблиц и диаграмм»……………………15

Практическая работа № 6 «Статистическая обработка данных»………………….17

Практическая работа № 7 «Статистическая обработка данных»…………………..18

Практическая работа № 8 «Сортировка и поиск данных»…………………………21

Практическая работа № 9 «Условная функция и логические выражения»……….23

Самостоятельная работа № 4 «Создание собственных списков. Форма для ввода данных»………………………………………………………………………………..25

Самостоятельная работа № 5 «Создание телефонного справочника» ……………28

Практическая работа № 10 «Расчет корреляционной зависимости……………….28

Практическая работа № 11 «Получение регрессионной модели»…………………29

Практическая работа № 12 «Поиск решения с помощью выбора параметра»……30

Самостоятельная работа № 6 «Решение математических уравнений»……………31

Практическая работа № 13 «Перекрестные ссылки»………………………………31

Самостоятельная работа № 7 «Создание электронного журнала»………………..33

Практическая работа № 14 «Информационное моделирование с помощью формализации»……………………………………………………………………….34

Самостоятельная работа № 8 «Информационное моделирование с помощью формализации»……………………………………………………………………….34

Зачетная работа № 2…………………………………………………………………35

Литература……………………………………………………………………………37

Пешкова Ольга Алексеевна

преподаватель математики

ГБОУ СПО «Строгановский колледж»

Филиал г. Оханск

Основы работы вExcel

Лабораторные работы и практикум по информатике

и информационным технологиям

часть 2

Адрес публикации: https://www.prodlenka.org/metodicheskie-razrabotki/53912-uchebno-metodicheskoe-posobie