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

Тема: Обработка числовой информации. Создание и форматирование таблиц в табличных процессорах. Использование формул и функций в расчетных операциях с данными таблиц по заданным условиям

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

Тема: Обработка числовой информации. Создание и форматирование таблиц в табличных процессорах. Использование формул и функций в расчетных операциях с данными таблиц по заданным условиям

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

Оборудование: РМ преподавателя – персональный компьютер, лазерный принтер, мультимедийный проектор, персональные компьютеры (РМУ – 11 шт.), локальная сеть.

Программное обеспечение: Операционная система Windows 7, пакет Micrоsoft Office, приложение MS Word 2007, MS Excel 2007.

Методическое обеспечение: методические указания по выполнению практической работы; презентация по теме: «Табличный процессор MS Excel»; контрольные вопросы по теме.

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

1.    Выполнить: Задача 1. Расчет выручки от проката машин.

2.    Выполнить: Задача 2. Определите выручку от продажи товаров на лотках и тенденцию роста доходов.

3.    Выполнить: Задача 3. Определение величины налога по заданным условиям.

4.    Выполнить: Задача 4. Произвести анализ продаж.

5.    Выполнить: Задача № 5. Заполнить таблицу по заданным условиям.

6.    Выполнить: Задача № 6. Расчёт приобретённых компанией канцелярских средств оргтехники.

7.    Выполнить: Задача № 7. Заказ  и ведение отчётности по канцтоварам.

Ход выполнения работы

Теоретическое обоснование

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

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

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

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

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

Excel допускает арифметические операции

o        "+" — сложение,

o        "-" — вычитание,

o        "*" — умножение,

o        "/" — деление,

o        "^" — возведение в степень;

операции отношений:

o        ">" — больше,

o        "<" — меньше,

o        "=" — равно,

o        "<=" — меньше или равно,

o        ">=" — больше или равно,

o        "<>" — не равно.

Арифметические операции и операции отношений выполняются над числовыми операндами. Над текстовыми операндами выполняется единственная операция "&", которая к тексту первого операнда присоединяет текст второго операнда. Текстовые константы в формуле ограничиваются двойными кавычками. При вычислении формулы сначала выполняются операции в круглых скобках, потом арифметические операции, за ними операции отношений.

Адрес ячейки включает имя колонки и номер строки. Адреса ячеек (ссылки на ячейки) можно использовать в формулах. Возможны относительные, абсолютные и смешанные ссылки. Ссылка, которая включает имя колонки и номер строки, является относительной. При копировании формулы, а также редактировании листа такая ссылка будет модифицироваться. В абсолютных ссылках перед именем колонки и номером строки стоит символ $. Такие ссылки не модифицируются. В смешанных ссылках абсолютной является название колонки и относительной — номер строки, или наоборот (например, $А1, А$1). В них модифицируется только относительная часть ссылки.

В формуле может быть ссылка на диапазон ячеек. Диапазон может быть только прямоугольным. Указывая диапазон ячеек, задают адрес верхней левой ячейки и через двоеточие — адрес нижней правой ячейки. Если в формуле есть ссылки на ячейки, которые находятся на другом листе, то ссылка должна содержать имя листа, восклицательный знак и адрес ячейки: например, лист! А1.

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

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

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

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

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

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

Относительные ссылки

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

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

При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется.

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

Абсолютные ссылки

Если возникла необходимость указать в формуле ячейку, которую нельзя менять при автозаполнении, используется знак $. Им фиксируются как столбцы, так и строки. Например: $А$10.

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

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

При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.

Смешанные ссылки

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется.

Ход выполнения

Задача № 1. Расчет выручки от проката машин

Подготовительные условия:

1.  Составьте таблицу следующего вида.

2.   Введите в таблицу заголовок.

3.  Заполните ячейку B4. Затем протащите мышь вправо до ячейки F4 включительно (указатель мыши необходимо навести на ячейку B4 таким образом, чтобы он принял форму черного крестика).  Отпустите левую кнопку мыши. Клетки автоматически заполнятся месяцами.

Выполнение:

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

В результате выполненных действий ваша таблица примет вид:

Теперь приступаем к следующему этапу выполнения задания.

Задание:

Рассчитать выручку от проката машин на 4 часа, 1 день, одну неделю.

1.  Выделите ячейки B5:G5. Выполните щелчок на кнопке Сумма S.

2.  Скопируйте формулу из ячейки G5  в ячейки G6:G7.

3.  Выделите ячейки B5:B8. Выполните щелчок на кнопке сумма S.

4.  Скопируйте формулу из ячейки B8 в ячейки C8:G8.

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

Расчет процента

5.  Установите курсор на ячейку H5. Щелкните на кнопке Процент на ленте инструментов для задания формата ячейки.

6.  Наберите формулу = G5/G8 и, не нажимая клавишу Enter, нажмите клавишу F4. Формула примет вид =G5/$G$8, то есть относительный адрес G8 заменится на абсолютный $G$8 (не меняющийся при копировании формулы в другие ячейки). Нажмите клавишу Enter. Появится величина в процентах.

7.  Скопируйте формулу из ячейки H5 в ячейки H6:H8.

Имена ячеек

8.  Присвойте ячейке G8 имя Всего: установите курсор на ячейку G8, выполните команду Формулы ® Присвоить имя® Присвоить имя. В открывшемся окне Создание имени, в поле имени введите имя ячейки Всего, щелкните на кнопке ОК.

9.  Очистите ячейки H5:H8 для расчета процентов по  другой формуле.

10. Введите имя ячейки в формулу расчета процента: установите курсор на ячейку H5 и введите формулу =G5/всего, скопируйте формулу в H6:H8.

11. Быстрый переход к ячейке по ее имени: откройте список в поле Имя в строке Формул, щелкните на имени ячейки. Для перехода к ячейке по ее имени можно также нажать клавишу F5 и выбрать имя ячейки.

Контроль взаимосвязи ячеек при расчетах

12.          Выведите панель инструментов Зависимости формул. Для этого выполните команду Формулы®Зависимости формул®Панель зависимостей.

13.          Установите курсор на ячейку G8. Щелкните на кнопке Влияющие ячейки. Сфотографируйте экран, демонстрирующий вид таблицы, после действия указанной команды. Щелкните на кнопке Зависимые ячейки. Сфотографируйте экран, демонстрирующий вид таблицы, после действия указанной команды.

Влияющие ячейки – это ячейки, на которые ссылается формула в текущей ячейке. Зависимые ячейки – это ячейки, содержащие формулы, которые ссылаются на текущую ячейку. Фото поместить в отчет.

14.          Удалите все стрелки, щелкнув на кнопке Убрать все стрелки на панели Зависимости формул. Сфотографируйте экран, демонстрирующий вид таблицы, после действия указанной команды. Фото поместить в отчет.

15.          Сохранить таблицу под именем Таблица 3.xls. Рабочему листу, на котором находится таблица, присвойте имя Задача 1.

16.          Следующему листу присвойте имя Задача 2 (если в рабочей книге только один лист, щелкните правой кнопкой мыши по ярлычку этого листа и выполните команду Вставить ® Лист).

Задача № 2. Определите выручку от продажи товаров на лотках и тенденцию роста доходов.

Подготовительные условия:

Составьте таблицу следующей формы.

Введите исходные данные, согласно приведенным в исходной форме.

Задание:

1.  Установите курсор на ячейку E6. Введите формулу: = СУММ(D4:D6).

2.  Установите курсор на ячейку E9. Введите формулу, используя мастер функций: щелкните на кнопке Вставка функции на панели инструментов, в поле Функция щелкните на имени функции СУММ, нажмите на кнопку ОК, в появившемся диалоговом окне в поле Число 1 введите диапазон суммируемых чисел D7:D9. Щелкните на кнопке ОК.

3.  Мастер функций можно вызвать также и при одновременном нажатии клавиш SHIFT и F3. Для получения пояснения по функции  щелкните на кнопке Справка (кнопка со знаком вопроса в левом нижнем углу диалогового окна).

4.  Самостоятельно подсчитайте сумму в ячейке E12  любым способом.

     Функции СРЗНАЧ, МАКС, МИН.

5.  В ячейку  Е14 введите формулу =СРЗНАЧ(E6; E9; E12).

6.  В ячейку D15 введите формулу = МАКС(D4:D12).

7.  В ячейку D16 введите формулу =МИН(D4:D12).

8.  Очистите ячейки E14,D15, D16 и введите формулы функций через Мастер функций.

Функция РАНГ

Функция РАНГ определяет ранг (номер) элемента в общей совокупности.

9.  В ячейку С4 введите формулу =РАНГ($D4;$D$4:$D$12), где D4 – содержит число, для которого определяется ранг, а D4:D12 – массив чисел, среди которого определяется ранг.

10.          Скопировать формулу в ячейки C5:C12.

Функция ТЕНДЕНЦИЯ

11.          Выполните подготовленные операции: в ячейки B20:B22 введите соответственно значения из ячеек E12, E9, E6 в ячейки C20:C24 введите годы: 2012-2015.

12.          В ячейку B23 введите формулу =ТЕНДЕНЦИЯ(B20:B22;C20:C22;C23). Скопируйте формулу из ячейки B23 в ячейку В24.

13.          Задайте в ячейках B23:B24 формат целых чисел. Для этого следует: выделить нужные ячейки; выполнить команду Формат®Ячейки®Формат ячеек..; выбрать вкладку Число; выбрать категорию Числовой; число десятичных – 0.

14.          Сохраните таблицу. Третьему листу рабочей книги присвойте имя.

Задача № 3. Определение величины налога по заданным условиям

Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определите величину налога. Составить таблицу следующей формы.

Подготовительные условия:

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

Задание:

Функция ЕСЛИ

1. В ячейку E4 введите формулу =ЕСЛИ(И (B4=10; С4>18); D4*0,1; 0). Формула означает, что если код города равен 10 и возраст старше 18 лет, то сумма налога определяется умножением дохода на величину налога. В противном случае сумма налога равна 0. Скопируйте формулу из ячейки E4 в E5:E6.

Задача № 4. Произвести анализ продаж

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

Подготовительные условия:

Заполните таблицу согласно данным, приведенным в заданной форме

Задание:

Функции ОКРУГЛ, ABS, СТЕПЕНЬ, КОРЕНЬ

1.    В ячейку  B9 введите формулу = ОКРУГЛ(СРЗНАЧ(B5:B7);0).

2.    В ячейку C5 введите формулу =ABS(B5-$B$9). Скопируйте формулу из ячейки C5 в ячейки C6:C7.

3.    В ячейку D5 введите формулу =СТЕПЕНЬ(С5;2). Скопируйте формулу из ячейки D5 в ячейки D6:D7.

4.    В ячейку D10 введите формулу = ОКРУГЛ(СРЗНАЧ(D5:D7);0).

5.    В ячейку D11 введите формулу =ОКРУГЛ(КОРЕНЬ (D10);0).

Функции ДИСПР и СТАНДОТКЛОНП (по генеральной совокупности).

6.    Очистите ячейки D10 и D11, чтобы произвести расчет дисперсии и стандартного отклонения, используя соответствующие функции.

7.  В ячейку D10 введите формулу =ДИСПР(B5:B7).

8.  В Ячейку D11 введите формулу =СТАНДОТКЛОНП(B5:B7).

9.  Сохраните результаты, переименуйте лист Задача 4.

Задача № 5. Заполнить таблицу по заданным условиям.

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

Задача № 6. Расчёт приобретённых компанией канцелярских средств оргтехники

Создайте следующую таблицу. Заполните нужные ячейки формулами, воспользуйтесь относительными, абсолютными или смешанными ссылками при автозаполнении формул. Для товаров, стоимость которых с учетом их количества превышает 500$, установите скидку в 1%, используя функцию «ЕСЛИ» (информацию о данной функции найдите в справке).

Примечание: курс $ брать на день выполнения задания.

Задание № 7. Заказ  и ведение отчётности по канцтоварам

Считается, что отчёты составляются в виде таблицы ежеквартально, то есть раз в три месяца. Перечень приобретаемых предприятием канцтоваров примерно известен, но может меняться с учётом специфики производства. По основным позициям он приведён в Табл.1. Примерный перечень канцелярских товаров для офиса. (Смотри Приложение 1 к Методическим указаниям по выполнению практической работы № 7). Цены на канцтовары можно оперативно корректировать непосредственно в электронных таблицах.

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

Технология работы.

1.  Откройте программу Excel.

2.  Увеличьте ширину столбца А, для чего:

• Наведите курсор мышки на область заголовков между А и В (курсор примет вид перекрестия со стрелочками «).
• «захватив» границу между заголовками, перетащите её вправо.

3.  Заполните шапку таблицу

Шапка таблицы

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

5.  Заполните ячейки таблицы исходными данными об используемых канцтоварах, согласно приведённым в таблице 1.

6.  Проставьте примерное количество приобретённых канцелярских принадлежностей в соответствующем столбце (в расчете на 200 работающих).

7.  Введите формулу для подсчёта стоимости конкретного наименования товара: = С6*D6 (цена*количество).

Ввод формулы в ячейку

8.  Выделите столбец Стоимость для всего списка товаров, начиная с ячейки Е6, в которую введена формула.

9.  Скопируйте введённую формулу в выделенные ячейки.

ПРИМЕЧАНИЕ Ячейки, которые не соответствуют конкретному товару (пустые строки, заголовки), примут при копировании формулы нулевое значение. Это очень важно для дальнейшего вычисления общей суммы затрат.

10.                    Введите в ячейку Е82 формулу подсчета общей суммы затрат на канцтовары за первый квартал =СУММ(Е6:Е80).

Для этого:

·       Используйте стандартную математическую функцию: Вставить →Функция→ выбрать Математические→ выбрать СУММ ();

·       Суммируемый диапазон ячеек в таблице выделите мышкой от Е6 до Е80.

11.                    На свободном поле таблиц разместите данные расчётов промежуточных сумм за мелкие канцтовары: мелочь, папки и скоросшиватели (Папки), клей и клеящие средства (Клей), бумага и бумажные изделия (Бумага), пишущие принадлежности (Ручки).

Окно выбора стандартной функции

12.                    Сохраните табличный документ под именем Канц_принадлежности.

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

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

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

3.    Назовите наиболее распространенные табличные процессоры. Чем различаются они между собой?

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

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

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

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

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

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

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

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

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

Форма отчета по работе

В отчете содержатся:

1.                    Цели  и задачи практической работы.

2.                    Ответы по контрольные вопросы.

3.                    Выводы по работе.

4.                    Приложение с результатами выполнения заданий:

Задача № 1.

Результаты выполнения пунктов: 1-4; 5-7; 13-14.

Задача № 2.

Результаты выполнения пунктов: 1-4; 5-7; 8; 9-10; 11;12;13.

Задача № 3.

Результаты выполнения пунктов: 1.

Задача № 4.

Результаты выполнения пунктов: 1-2; 3; 4-5; 7-8.

Задача № 5.

Результаты выполнения.

Задача № 6.

Результаты выполнения.

Задача № 7.

Результаты выполнения.

Примечание:

Каждое задание выполняется в соответствии с методическими указаниями к работе. Все этапы работы должны быть сохранены.