Методическая разработка по теме Электронные таблицы 10 класс

Этапы решения задач средствами табличного процессора Microsoft Excel

Арзамас, 2016

Содержание:

Введение

3 стр.

2. Примерный тематический план

3 стр.

3. Этапы решения задач средствами табличного процессора Excel

4 стр.

4. Примеры практических задач

5 стр.

5. Задачи для самостоятельного решения

11 стр.

6. Контрольные вопросы

16 стр.

7. Библиографический список

18 стр.

Введение

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

В настоящее время ведущее положение на мировом рынке программных продуктов такого класса занимает электронная таблица Excel корпорации Microsoft , входящая в состав знаменитого пакета MS Office.

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

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

Примерный тематический план: раздел «Табличный процессор Microsoft Excel»

1. Знакомство с Excel .

   1. Функциональные возможности табличного процессора Excel .

   2. Интерфейс среды Excel .

   3. Основные понятия Excel.

2. Элементарные операции с данными.

   1. Адресация в электронной таблице.

   2. Типы данных.

   3. Ввод и редактирование данных в ячейке таблицы.

   4. Форматирование данных.

   5. Защита информации в Excel.

3. Функции рабочего листа.

   1. Функция суммирования.

   2. Арифметические функции.

   3. Текстовые функции.

   4. Логические функции.

   5. Функции выбора и поиска.

   6. Сводные функции.

   7. Функции обработки дат.

   8. Финансовые функции.

   9. Информационные функции.

3.10.Матричные функции

4. Обобщение данных.

   1. Сортировка.

   2. Фильтрация.

   3. Итоги.

   4. Консолидация.

   5. Сводные таблицы.

   6. Создание серийных документов.

5.Этапы решения задач с помощью табличного процессора.

6.Примеры практических задач.

Этапы решения задач средствами табличного процессора Excel

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

• какие исходные данные известны;

• что требуется определить;

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

• какие данные допустимы?

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

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

Четвертый этап - анализ полученных результатов. Данный этап требует значительных интеллектуальных усилий и навыков по обработке, представлению и осмыслению получаемых решений. Анализируя получаемые результаты такого контрольного расчета, в случае их правильности можно сделать вывод о правильности всех (I-III) предшествующих этапов. Как же определить, что результаты получены правильные? Для этого в зависимости от класса решаемой задачи применяют различные подходы.

• сравнивают полученные результаты с результатом, рассчитанным вручную в соответствии с тем же методом или с помощью калькулятора;

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

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

Шестой этап - оформление таблицы.

Седьмой этап - построение диаграмм.

Восьмой этап - защита таблицы.

Девятый этап - сохранение таблицы и использование ее для расчетов.

Десятый этап - печать таблицы.

При решении конкретной задачи следует учитывать тот факт, что не все перечисленные этапы являются обязательными, как правило к числу таковых относятся: 7, 8,10.

Примеры практических задач.

Пример 1.

Составить таблицу расчета сметы оборудования кабинета информатики на 15 посадочных мест, в которой подсчитать затраты на приобретение электронно-вычислительной техники.

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

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

Рис.1 Окно Microsoft Excel с введенными данными задачи

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

Для ввода формулы нужно выделить ячейку D4, затем щелкнуть кнопку = в строке ввода формул, щелкнув ячейку В4, ввести первый операнд,

щелкнув клавишу «*», задать операцию умножения, щелкнув ячейку С4,

ввести второй операнд, щелкнув клавишу «*», задать операцию умножения, щелкнув ячейку В2, ввести третий операнд.

Так как адрес ячейки В2, в которой размещен курс ин.валюты, должен оставаться неизменным при последующем копировании формулы вычисления суммы в рублях, то зададим абсолютную адресацию ячейки В2, для чего щелкнем клавишу F4. После этого в изображении ссылки на ячейку В2 появится знак доллара ($): $В$2. Завершим создание формулы вычисления, щелкнув кнопку ОК. После этого в ячейке D4 немедленно появится результат вычислений, а в строке формул будет изображена формула, по которой выполняется вычисление: =В4*С4*$В$2.

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

Для копирования формулы из ячейки D4 в D5:D12 выделим ячейку D4, затем установим курсор мыши на рамку выделения и нажмем левую кнопку мыши. Прижав клавишу Ctrl, перетащим мышью выделение с D4 на D5, удерживая нажатой левую кнопку мыши. Для копирования формулы в примыкающий к данной ячейке диапазон внутри столбца или строки удобно использовать маркер заполнения. Для этого выделим ячейку D4 , содержащую формулу и перетащим маркер заполнения в диапазон в D5:D12.

Для вычисления итоговой суммы затрат просуммируем суммы в рублях всех комплектующих, для этого в ячейку D13 введем формулу вычисления суммы ячеек D4:D12, для чего выделим диапазон ячеек D4:D12 и щелкнем кнопку (Автосумма).

Четвертый этап - анализ полученных результатов.

Результаты выполнения действий приведены в таблице на рис.2. Оцените полученные результаты. Обратите внимание, что фактически составлена небольшая программа, которую можно использовать для многократных пересчетов. Например, если изменить одно или несколько чисел в исходных данных, все суммы будут пересчитаны автоматически. Более того, можно модифицировать структуру таблицы, например, удалить строку с записью о принторе или вставить новую строку и формулы в итогах будут изменены автоматически. При оценке результатов часто возникает необходимость просмотреть формулы в ячейках таблицы. Для просмотра формулы нужно выделить ячейку и в строке формул будет выведена формула в данной ячейке. Если требуется просмотреть формулы во всех ячейках таблицы на данном листе, то для переключения режимов просмотра формул и просмотра значений формул

следует нажать Ctrl+« (левая кавычка).

Смета оборудования офиса

(рис.2)

Изменение режима отображения формул и результатов вычислений на листе можно выполнить, выбрав команду Параметры в меню Сервис. На вкладке Вид для отображения формул в ячейках включите флажок формулы. Если вы хотите отображать в ячейках результаты вычислений, то снимите данный флажок.

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

Если в ячейках таблицы появляются ошибки, то можно воспользоваться справкой Excel для уточнения характера ошибки. Вызовите справку, выбрав команду Вызов справки в меню «?». На вкладке Указатель задайте слово «ошибка», затем установите курсор на разделе ошибка ##### и щелкните кнопку Показать. В окне справки Excel Разрешение вопросов, возникающих при появлении ошибок, выбирая тип ошибки, вы можете ознакомиться с причинами возникновения данной ошибки и ме­рами по ее устранению.

Для изменения содержимого ячейки следует дважды щелкнуть ячейку, затем отредактировать содержимое ячейки. После изменения содержимого ячейки нажать клавишу Enter для сохранения изменений или нажать клавишу Esc, если вы хотите отменить внесенные изменения. Если вы уже нажали Enter, то для отказа от внесенных изменений нужно воспользоваться командой Отменить из меню Правка.

Если нужно, вы можете вставить новые столбцы или строки. Например, для вставки в нашу таблицу строки с наименованием Модем в количестве 1 шт., цена которых 60,5$ , выделим строку 13 и в меню Вставка выберем команду Строки. После этого все строки, расположенные ниже, сместятся на одну строку вниз, и строка вставится в таблицу. Введем в соответствующие столбцы этой строки данные и скопируем в ячейку D13 формулу расчета из ячейки D12. Обратите внимание, что сумма затрат в ячейке D14 автоматически пересчитана с учетом добавленного оборудования.

Шестой этап - оформление таблицы. Когда таблица проверена, найденные ошибки исправлены, наступает очередь этапа оформления таблицы.

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

Один из самых быстрых способов оформления таблицы заключается в использовании команды Автоформат меню Формат. Для его применения выделите все ячейки таблицы и выберите в меню Формат команду Автоформат. В диалоговом окне Автоформат выберите нужный тип формата в поле Список форматов, а в поле Образец просматривайте вариант оформления таблицы с избранным типом формата. Если нужно сделать дополнительный выбор, то для частичного применения автоформата нажмите кнопку Параметры и снимите флажки для форматов, которые не нужно применять. По окончании выбора нужного типа формата щелкните кнопку ОК и просмотрите результат избранного вами варианта оформления таблицы. Если этот вариант вас не устраивает, то можно воспользоваться отменой операции, выбрав в меню Правка команду Отменить Автоформат. Пример оформления таблицы приведен на рис. 3

Рис.3

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

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

Щелкнув кнопку Мастер диаграмм, следуя инструкциям мастера, зададим параметры диаграммы:

• на первом шаге выбрать тип Диаграммы, например круговая;

• на втором шаге определить источник данных диаграммы: строки или столбцы и уточнить диапазон ячеек, данные из которых отображаются на диаграмме, на вкладке Ряд можно уточнить состав рядов с данными, участвующих в формировании диаграммы;

• на третьем шаге задать параметры диаграммы: заголовки, подписи осей и данных, отображение линий сетки, состав и место размещения легенды на диаграмме;

• на четвертом шаге выбрать место размещения диаграммы и щелкнуть кнопку Готово.

Диаграмма будет выведена на экран как показано на рис.4

Рис.4

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

Рис.5. Панель инструментов редактирования диаграммы

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

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

Затем в меню Формат выберите команду Ячейки. На вкладке Защита установите флажки Скрыть формулы и Защищаемая ячейка, после чего нажмите кнопку ОК. После этого в меню Сервис выберите команду Защита, а затем - команду Защитить лист. Проверьте, чтобы в открывшемся диалоговом окне был установлен флажок Содержимое.

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

Десятый этап - печать таблицы.

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

Пример2.

Создать таблицу распределения фонда заработной платы сотрудников подразделения в соответствии с устанавливаемым руководителем коэффициентом (индивидуальной оценкой качества и ответственности труда).

Первый этап - анализ исходных данных и постановка задачи. Проанализируем текст задачи и определим исходные данные задачи: фонд з/п (число), ФИО сотрудников (текст), индивидуальный коэффициент работника (число). Установим, что мы должны рассчитать в задаче следующие величины: «вес» единицы коэффициента в денежном исчислении (т.е. <фонд з/п>/<сумма коэффициентов>), а затем умножить его на индивидуальный коэффициент работника, <з.п работника>=<фонд з.п>/<сумма коэффициентов>*><коэффициент работника>.

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

(рис.6)

Третий этап - выбор метода решения (описание расчетов).

Установим, что мы должны рассчитать в задаче следующие величины: «вес» единицы коэффициента в денежном исчислении (т.е. <фонд з/п>/<сумма коэффициентов>), а затем умножить его на индивидуальный коэффициент работника, <з.п работника>=<фонд з.п>/<сумма коэффициентов>*><коэффициент работника>.

Отсюда следует, что прежде нужно найти сумму всех коэффициентов В8=СУММ(В4:В7) . Тогда зарплата Петрова будет вычисляться по формуле С4=В$1/В$8*В4, в итоге подсчитывается сумма всех установленных зарплат С8=СУММ(С4:С7) Результаты выполнения действий приведены в таблице на рис.7

Рис.7

Рассмотрение этапов 4-10 не считаю целесообразным т.к. они достаточно подробно рассмотрены в примере 1, т.о. залог успешного решения задач прикладного характера - это правильное преодоление этапов решения задач 1 - 3 (по сути построение математической модели задачи).

Задачи для самостоятельного решения

1.Составить таблицу расчета оплаты за аренду помещения в зависимости от площади помещения, если арендуется меньше 100м² площади, то арендная плата составляет 200 руб. за 1 м^2, если арендуемая площадь больше, чем 100м² , но не превышает 200м² , то арендная плата составляет 180 руб. за 1 м².

2. Составить таблицу - шаблон счета оплаты за электроэнергию с учетом льгот для некоторых категорий потребителей(например, 50% от величины тарифа оплачивают потребители в сельской местности)

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

4. Имеются сведения о пассажирах, количестве мест и общем весе багажа:

5.Определите какая сумма окажется на счете, если вклад размером 1200тыс.руб. положен под 11% годовых сроком на 17 лет, а проценты начисляются ежеквартально.

6.Какая сумма должна быть выплачена, если 8 лет назад была выдана ссуда 50000 руб. под 10% годовых с ежемесячным начислением процентов.

7.Определите ежемесячные выплаты по займу в 125000тыс.руб., взятому на 7месяцев, под 9% годовых.

8. Создать таблицу с использованием математических функций, которая рассчитывает значения функции y=sin(2x/3)*cos(x/2) на интервале значений х от -Пи до +2Пи с шагом 0.1, вычисляет максимальное и минимальное значения функции на данном интервале области определения, а также строит график данной функции.

9.Создать таблицу с использованием математических функций, которая рассчитывает значения функции y=sin(2x/3)*cos(x/2) на интервале значений х от -Пи до +2Пи с шагом 0.1, вычисляет максимальное и минимальное значения функции на данном интервале области определения, а также строит график данной функции.

10.Создать таблицу с использованием математических функций, которая рассчитывает значения функции y=sin(2x/3)*cos(x/2) на интервале значений х от -Пи до +2Пи с шагом 0.1, вычисляет максимальное и минимальное значения функции на данном интервале области определения, а также строит график данной функции.

11.Рассчитайте будущую стоимость облигации номиналом 400тыс. руб., выпущенной на 5 лет, если предусмотрен следующий порядок начисления процентов: в первые два года - 13,5%годовых, в следующие два года - 15%, а в последний год - 18% годовых.

12. Сформируйте таблицу «Штатное расписание магазина», в которой пре­дусмотрите следующие реквизиты: № п/п, Должность, Оклад, Количество, Сумма. Данные таблицы можно ввести произвольно, но не менее 5 должно­стей. В графу Сумма введите формулу. Рассчитайте Итого по количеству и по сумме. Предусмотрите подпись экономиста по труду. Отсортируйте таблицу по колонке Оклад. График: Должность-Оклад (круговая диаграмма).Сохраните таблицу.

13.Сформируйте таблицу «Расчетно-платежная ведомость», в которой пре­дусмотрите следующие реквизиты: № п/п, Табельный номер, Фамилия И.О., Начислено, Удержано, К оплате. Данные таблицы введите произвольно (но не менее 4-5 фамилий). Графу К оплате вычислите по формуле. Рассчитайте итоговые показатели по графам Начислено, Удержано, К оплате. Предусмот­рите подпись главного бухгалтера. График: Фамилия-Начислено (гистограмма). Сохраните таблицу.

14.Сформируйте таблицу «Реализация товаров за 1 квартал» со следующи­ми реквизитами: № п/п, Наименование товара, Продано за: Январь, Февраль, Март, Всего за квартал, Среднемесячная реализация. Заполните произвольно данные таблицы (5-6 наименований товаров). В графы Всего за квартал и Среднемесячная реализация введите расчетные формулы. Предусмотрите под­пись экономиста. График: Продажа товаров по месяцам (круговая диаграмма).Отсортируйте таблицу по среднемесячной реализации. Сохраните таблицу.

15.Сформируйте таблицу «Инвентаризационная ведомость по складу», в которой имеются следующие реквизиты: № п/п, Наименование товара, Еди­ницы измерения, Цена, Количество, Сумма. Данные в таблицу вводите произ­вольно, 5-6 наименований. Графа Сумма рассчитывается по введенной фор­муле. Рассчитайте итоговые показатели по графе Сумма. Предусмотрите под­писи членов инвентаризационной комиссии и зав. складом. Выполните сорти­ровку таблицы по убыванию цены. С График: Наименование товара - Количество. Сохраните таблицу.

16.Сформируйте таблицу «Реализация компьютеров по кварталам». Изде­лий - не менее 5, кварталов - 4. Рассчитайте среднеквартальную реализацию в рублях каждого компьютера. Цену компьютеров укажите в рублях и в услов­ных единицах. Пересчет в рубли - автоматический по приведенному в табли­це курсу (ввести формулу). Вычислите итоговые показатели по каждому квар­талу. Предусмотрите подпись экономиста. П График: Среднеквартальная продажа компьютеров (диаграмма). Сохраните таблицу.

17.Сформируйте таблицу «Успеваемость за I четверть». Введите произволь­ные данные по нескольким учащимся вашего класса (4-5) и по предметам, которые вы изу­чаете. Рассчитайте средний балл успеваемости каждого ученика по всем предметам. Отсортируйте таблицу по среднему баллу. Предусмотрите под­пись классного руководителя. Сформируйте гистограмму успеваемости всех учащихся с полной разметкой. Сохраните таблицу.

18. Сформируйте таблицу «Расчет заработной платы» со следующими рек­визитами: № п/п, Фамилия И.О., Оклад, Подоходный налог (13%), Пенсионный фонд (2%), Итого удержано, Сумма к выдаче. В произвольной форме введите 4-5 строк, в графах Подоходный налог, Пенсионный фонд, Итого удержано и Сумма к выдаче значения вычислите по формулам. Рассчитайте итоговый показатель по графе Сумма к выдаче, предусмотрите подпись бухгалтера. Отсортируйте таблицу по убыванию значений графы Сумма к выдаче. Сформируйте гисто­грамму, показывающую величину выданной зарплаты каждому работнику. Сохраните таблицу.

19. Вычислить заработанную рабочим сумму в зависимо­сти от количества отработанных им в неделю часов и их вида. <3арплата> определяется как число отработанных <Нормальных> часов, умноженных на <Стоимость нормального часа> плюс стоимость сверхурочных часов и часов, отработанных в выходные дни. Стои­мость таких часов увеличивается на 150% и 200% относительно "нормального" часа. Кроме того, если общее число отработанных ча­сов превышает 52, работник получает <Доплату> в 1000 руб., если больше 60 часов - 2000 руб., если больше 66 - 2500 руб. и еще 5% от зар­платы. Сумма, выдаваемая <На руки>, это <Зарплата>+<Доплата> с учетом <Налога>. Постройте диаграмму, включающую <Фамилию> сотрудника и размер полученной им з/п(<На руки>).

20. Вычислить <Стоимость всего> товара, хранящегося на складе магазина. Она определяется стоимостью первого сорта товара (<Число единиц 1 сорта>, умноженной на <Цену 1 сорта>) плюс стоимость 2 сорта (<Число единиц 2 сорта>, умноженная на <Цену 1 сорта>, уменьшенную на <Процент скидки 2 сорта>), плюс стоимость то­вара 3 сорта, полученную аналогичным образом, плюс стоимость просроченного товара по цене 10% от цены 1 сорта.

Кроме того, следует определить факт затоваривания или нехватки товара. Если совокупная стоимость любого товара всех сортов состав­ляет величину большую 100000руб, в столбце <Состояние запасов> формируется слово "Избыток". Если стоимость менее 20000 руб. -"Нехватка". Если равна нулю - слово "Нет". В остальных случаях не выдается никакого сообщения - пустые кавычки ("").

График: Название товара - Стоимость всего.

21. Вычислить величину квартплаты. Она определяется количеством квадратных метров <Площади>, умноженных на <Цену 1 квадратного метра>. Кроме того, если в квартире имеется излишек площади относительно санитарной нормы, он оплачивается в двойном размере. Излишек определяется как <Площадь> квартиры минус число проживающих в ней <Человек>, умноженное на <Санитарную норму>. Если в квартире проживает один человек, ему положена удвоенная са­нитарная норма. Некоторым категориям жильцов положены льготы при оплате коммунальных услуг. Инвалиды платят на 25%, а участни­ки войны - на 50% меньше. Эти лица отмечены в колонке <Льготы> буквами "и", "у" или "иу" соответственно. Кроме того, для жильцов пер­вого этажа квартплата снижается на 20% в виду отсутствия необходи­мости платить за лифт, а жильцам второго - на 10% по тем же причи­нам.

График: Номер квартиры - Квартплата фактическая.

22. Вычислить зарплату рабочего, которая определяется числом <изготовленных им деталей>, умножен­ным на «Стоимость одной детали>. Заработок также зависит от <Разряда> рабочего. Он увеличивается на соответствующий <Разрядный коэффициент>. Кроме того, если рабочий произвел более 30 деталей, ему начисляется премия в размере 50% от стоимости каждой детали, начиная с 31-й. Зарплата рабочего может быть и уменьшена в случае, если им было изготовлено свыше трех бракованных деталей - из зара­ботанных сумм вычитается штраф в размере 500 руб. В колонке <Брак> выводится восклицательный знак, если бракованных деталей до пяти, вырабатывается сообщение "Брак", если больше пяти, и "Аврал", если больше семи. В ячейке F13 подсчитывается число рабочих, допустив­ших брак в количестве от пяти деталей. В области F2:F5 подсчитать число рабочих, имеющих соответствующий разряд. Указание: ниже предлагается макет таблицы, где исходные данные помечены символом «ххх», а графы, содержащие формулы «???», т.е. практически реализованы этапы решения задачи 1-2.

23. Определить <Сумму> на счету клиента банка по ис­течении установленного <Срока> хранения. <Процент банковской премии> определяется тарифной сеткой (А1:E4), зависящей от суммы <Вклада> (от 5000$,20000$ и т.п.) и <Срока> его хранения (от 3,6 и т.д. месяцев). Кроме того, если клиент открыл счет более, чем на 100000$, сроком не менее, чем на 6 месяцев, ему вручается подарок в размере 1% от исходного значения вклада. Здесь также по известной продолжительности депозита нужно вычислить дату его закрытия (число месяцев плюс один день). В клетке Н12 подсчитать число человек, получивших подарки. График: Вкладчик-Сумма.

Указание:

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

- 13% от суммарной зарплаты, если она меньше 20000;

- 30% от суммарной зарплаты, если она больше 50000;

- 21% от суммарной зарплаты во всех остальных случаях.

Контрольный пример

Исходные данные:

Фамилия Место работы Кол-во дней

Петров

Антонов

Сидоров

Петров

Сидоров

Антонов

Предприятие

ННГУ

ННГУ

ННГУ

НИРО

НИРО

МОУ СШ №16

Ставка

100

180

50

150

100

ННГУ

НИРО

МОУ СШ №16

150

100

120

Контрольные вопросы.

1. Для решения каких задач предназначены табличные процессоры?

2. Какие преимущества может дать обработка информации с помощью электронных таблиц по сравнению с обработкой вручную?

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

4. Опишите способы запуска и способы завершения работы Microsoft Excel.

5. Перечислите все элементы окна документа Excel, совмещенного с окном приложения и опишите их назначение.

6. Чем отличается производная информация от первичной или исходной?

7. Что такое ячейка и как определяется ее положение в таблице?

8. Какая ячейка называется активной и как она выделяется?

9. Что называется рабочей книгой в Excel? Каково отличие рабочей книги и листа?

10. Сколько листов может быть в одной книге Excel?

11. Каково стандартное расширение имени файла-книги?

12. Сколько строк и столбцов в листе?

13. Для чего нужно выделение данных?

14. Что такое маркер заполнения?

15. Как выделить целиком строку/столбец, весь лист?

16. .Как выделить блок клеток с помощью клавиатуры и мыши?

17. Как выделить несмежные элементы данных?

18. Какие и при каких обстоятельствах формы принимает маркер мыши?

19. Перечислите известные вам способы копирования и перемещения данных на листе.

20. Что такое автозаполнение?

21. Как получить арифметическую и геометрическую прогрессии?

22. Как построить последовательность, состоящую из рабочих дат?

23. Как удалить/вставить блоки, столбцы и строки листа?

24. Зачем нужно скрытие строк/столбцов?

25. Как переименовывать, добавлять, удалять рабочие листы?

26. Зачем нужна кнопка отката?

27. Назовите и охарактеризуйте основные типы данных в ячейках электронной таблицы.

28. Каково назначение строки формул, поля имени текущей ячейки?

29. Где расположена пустая кнопка для выделения всей таблицы?

30. Сравните и опишите общие и отличительные черты меню Microsoft Word и Microsoft Excel.

31. Сравните и опишите общие и отличительные черты панелей инструментов Microsoft Word и Microsoft Excel.

32. Чем отличаются относительная и абсолютная адресации? Как уста­новить абсолютную адресацию?

33. Какая дата является началом отсчета времени в Excel?

34. Что в числовом представлении даты является носителем собственно даты, а что носителем времени? Чему в числовом представлении рав­ны: одна минута, 10 минут, один час, 6 часов, одни сутки, одна неделя?

35. Зачем даются имена клеткам (группам клеток)? Как они присваи­ваются?

36. Для чего нужно форматирование данных?

37. Перечислите средства выравнивания данных.

38. Как объединить ячейки?

39. Перечислите параметры шрифтов.

40. Какие следствия возникают в результате применения к числу знака %?

41. Что такое условное форматирование? Сколько и каких секций может быть в условном формате?

42. Как сделать, чтобы в дате отображалось имя месяца и дня недели?

43. Зачем нужен Мастер условного форматирования?

44. Для чего нужна проверка ввода и где находятся эти средства?

45. Как скопировать в таблице только форматы?

46. Как защитить информацию в Excel.

47. Какую информацию выдает Excel в строке состояния?

48. Как включить и выключить изображение сетки в окне документа Excel?

49. Опишите процесс перемещения по рабочим листам файла .XLS. Как активизировать конкретный рабочий лист? Исследуйте и опишите два способа разбиения окна рабочего листа на подокна.

50. Как вывести на экран панель инструментов Рисование?

51. Перечислите все способы ссылки на ячейку и на диапазон ячеек.

52. Что такое относительный адрес ячейки? Можно ли изменить формат относительного адреса ячейки? Если да, то как это можно сделать?

53. Опишите способы изменения высоты строки и ширины столбца таблицы.

54. Назовите основные виды информации, используемые в электронных таблицах. По каким признакам Excel отличает число от текста, текст от функции?

55. Как будет воспринято программой число, в котором разделителем дробной части вместо точки поставлена запятая: как ошибка, как алфавитная информация или как формула?

56. Как ввести данные в ячейку таблицы? Какими способами фиксируется их значение в ячейке? Как отредактировать данные в ячейке?

57. Проведите сравнительный анализ возможностей форматирования текста в Word и Excel.

58. Что такое функция? Что такое Мастер функций и какие способы его запуска вы знаете?

59. В какой последовательности выполняются операции в арифметическом выражении?

60. Как следует записывать аргумент тригонометрической функции, если он записан в градусах?

61. В каких случаях применяются логические функции? Чем отличается функция ЕСЛИ от остальных функций.

62. Составьте примеры случаев, в которых необходимо использовать функцию ЕСЛИ и логические функции И, ИЛИ? Чем отличаются функции И и ИЛИ от функции ЕСЛИ? В каком формате записываются функции И, ИЛИ, ЕСЛИ?

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

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

65. Как с помощью мыши упростить ручной набор формулы? Как увидеть формулу, записанную в ячейку? Как сделать так, чтобы в ячейке отображался не результат вычислений по формуле, а сама формула?

66. Как установить (изменить) точность отображения числа и результата вычислений?

67. Зачем копируются формулы? Опишите способы копирования формул.

68. Каково назначение диаграмм? Опишите отличительные черты диаграмм различного типа.

69. Как выполнить обмен данными между Excel и другими приложениями Microsoft (например, Word)?

70. Что такое списки? Приведите примеры данных, собранных в списки. Какие операции обработки списков имеются в Excel?

71. Зачем применяется фильтр при обработке списков? Как задать фильтр? Как вернуться к исходному полному списку данных?

72. Опишите назначение и порядок выполнения сортировки списков.

73. С какой целью выполняются изменения конфигурации Excel? Каковы возможности изменения конфигурации Excel?

74. Что такое шаблон? Опишите порядок создания и использования шаблонов в Excel.

75. Что такое макрокоманда? Какими способами можно создать макрос?

Список литературы:

1. Попов В.Б. Основы компьютерных технологий. - М: Финансы и статистика, 2002.

2. Лавренов С.М. EXCEL. Сборник примеров и задач. - М: Финансы и статистика, 2002.

3. Попов А. . EXCEL. Практическое руководство. - М:ДЕСС КОММ, 2000.

4. Овчаренко Е.К., Ильина О.П., Балыбердин Е.В. Финансово-экономические расчеты в EXCEL. - М:Информационно-издательский дом «Филин»,1999.

5. Хахутаишвили М.Ш. Информатика и информационные технологии в подготовке специалистов экономического профиля.- Мытищи, 2000.

6. Информатика образование. №7, 1999.