Краткие теоретические сведения

ОГЛАВЛЕНИЕ

введение.. 6

обработка данных С ИСПОЛЬЗОВАНИЕМ арифметических формул 8

Учебные цели изучения темы.. 8

Краткие теоретические сведения. 8

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

Задачи.. 14

Задача 1.1. Расчет учебной нагрузки. 14

Задача 1.2. Расчет платежей за воду. 20

Задача 1.3. Платежный календарь. 21

подсчет и суммирование данных.. 23

Учебные цели изучения темы.. 23

Краткие теоретические сведения. 23

Суммирование всех значений диапазона. 26

Определение среднего арифметического данных диапазона. 26

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

Подсчет непустых ячеек в диапазоне. 26

Подсчет ячеек, удовлетворяющих критерию.. 26

Условное суммирование по одному критерию.. 28

Вставка функций с помощью средства Мастер функций. 29

Проверка данных, вводимых пользователем.. 29

Задачи.. 30

Задача 2.1. Анализ результатов олимпиады.. 30

Задача 2.2. Учет товарного запаса. 35

Связывание данных посредством формул с двумерными и трехмерными ссылками 36

Учебные цели изучения темы.. 36

Краткие теоретические сведения. 36

Задачи.. 39

Задача 3.1. Потребительский кредит.. 39

Задача 3.2. Расчет зарплаты бригады.. 41

Задача 3.3. Расчет платежей за электроэнергию.. 46

Задача 3.4. Долгосрочное кредитование. 46

обработка данных с использованием условных формул 48

Учебные цели изучения темы.. 48

Краткие теоретические сведения. 48

Логические выражения. 48

Логическая функция ЕСЛИ.. 51

Задачи.. 53

Задача 4.1. Расчет комиссионных от продаж... 53

Задача 4.2. Оплата телефонных переговоров. 54

Задача 4.3. Счет за проживание в гостинице. 58

Задача 4.4. Расчет стипендии. 59

Задача 4.5. Расчет квартальной премии бригады.. 60

Задача 4.6. Поток товарно-материальных запасов. 62

Задача 4.7. Учет расходования ГСМ... 65

обработка данных ТИПА ДАТА/ВРЕМя с использованием функции выбора 67

Учебные цели изучения темы.. 67

Краткие теоретические сведения. 67

Создание ряда последовательных дат.. 68

Извлечение из даты дня, месяца или года. 68

Возвращение дня недели, соответствующего любой дате. 69

Выбор значений из списка по индексу. 69

Возвращение даты, отстоящей на заданное количество рабочих дней от заданной даты 70

Определение количества рабочих дней между двумя датами. 71

Определение разницы между двумя датами в годах. 71

Задачи.. 72

Задача 5.1. Учет издания методической литературы.. 72

Задача 5.2. Заработанный доход врачей‑стоматологов. 76

Задача 5.3. Анализ опозданий сотрудников фирмы.. 77

Задача 5.4. Начисление заработной платы при трехсменной работе 78

обработка текстовых данных с использованием функции ВЕРТИКАЛЬНОГО ПРОСМОТРА ТАБЛИЦЫ 81

Учебные цели изучения темы.. 81

Краткие теоретические сведения. 82

Извлечение символов из текстовой строки. 82

Преобразование текста, отображающего число, в число. 82

Объединение несколько текстовых элементов в один. 83

Поиск вхождения одной текстовой строки в другую.. 83

Преобразование знаков в текстовой строке из одного регистра в другой 83

Присвоение имен ячейкам и диапазонам.. 83

Вертикальный просмотр таблицы.. 84

Задачи.. 86

Задача 6.1. Расчет транспортного налога. 86

Задача 6.2. Расшифровка кода группы и номера зачетной книжки студента 88

Задача 6.3. Анализ кодов ISBN.. 92

Задача 6.4. Определение знака зодиака. 93

КРИТЕРИАЛЬНЫЕ ЗАДАЧИ.. 94

Задача «Гороскоп». 94

Задача «Расчет стоимости заказа текстильных этикеток». 95

Задача «Расчет зарплаты ППС». 97

Задача «Учет движения товаров на мебельном складе». 98

Вопросы для критериальных тестов.. 100

Литература.. 102

Введение

В сборнике представлена целостная система взаимообусловленных, последовательно усложняющихся задач, предназначенных для формирования интеллектуальных умений обработки данных в среде табличного процессора MS Excel. Ее проектирование основывалось на принципах психологической теории учебных задач (А.Г.Балл, Е.И.Машбиц, Л.М.Фридман), а стратегия отработки соотносилась с положениями теории поэтапного формирования умственных действий (П.Я.Гальперин, Н.Ф.Талызина).

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

1.Разрабатывать и реализовывать табличные модели задач обработки данных и оценивать адекватность полученных результатов требованиям предъявляемых задач.

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

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

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

5.Интерпретировать полученные результаты средствами деловой графики.

Перечисленные умения обусловили отбор учебных элементов. Задачник состоит из семи разделов. Первые шесть включают учебные задачи по следующим темам:

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

2.Подсчет и суммирование данных.

3.Связывание данных с использованием формул с двумерными и трехмерными ссылками.

4.Обработка данных с использованием условных формул.

5.Обработка данных типа ДАТА/ВРЕМЯ с использованием функции ВЫБОР.

6.Обработка текстовых данных с использованием функции ВПР.

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

В формулировках учебных задач явно прописаны составные компоненты по Л.М.Фридману [14]: предметная область, отношения, требования. Причем, задачные ситуации взяты из тех предметных областей, данные о которых должны присутствовать, по нашему мнению, в опыте студентов или же относятся к азам экономических знаний. Значения исходных данных для большинства задач студенты подбирают самостоятельно.

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

Средством проверки достижения учебных целей, сформулированных на уровне системы умений, являются критериальные задачи [1, 8] и критериальные тесты. Они приведены в седьмом разделе сборника. При их проектировании мы придерживались требований содержательной и функциональной валидности. Решение критериальных задач предполагает, что студенты осуществляют синтезированную деятельность по обработке данных в среде MS Excel на аналитико-синтетическом уровне.

Обработка данных С ИСПОЛЬЗОВАНИЕМ арифметических формул

Учебные цели изучения темы

Завершив изучение этой темы, вы должны уметь:

1.Описывать назначения объектов: рабочая книга, рабочий лист, ячейка; называть типы ссылок и их стили; перечислять категории символов, вводимых в ячейки.

2.Называть компоненты формул; объяснить преимущества использования в формулах ссылок, а не значений.

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

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

5.Составлять формулы, адекватные условиям предъявляемых задач.

6.Демонстрировать умения вводить формулы с разными типами ссылок.

7.Видоизменять данные рабочего листа с помощью средств числового и стилевого форматирования.

8.Исследовать и обосновывать полученные результаты решения задач.

Краткие теоретические сведения

Табличный процессор – это прикладное программное средство (ППС), предназначенное для манипулирования данными в электронных таблицах (рис. 1).

Наиболее популярные табличные процессоры – MS Excel, Lotus 1-2-3, Quatro Pro, Calc.

Табличный процессор MS Excel совмещает преимущества электронных таблиц и средств визуального программирования, включает свыше 100 объектов обработки данных.

Рабочая книга в MS Excel представляет собой файл (с расширением .XLS), используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов.

Рис. 1. Аспекты манипулирования данными
в табличном процессоре

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

Рис. 2. Основные объекты MS Excel

Ячейка или группа ячеек (диапазон) на листе однозначно определяется ссылкой – набором координат, который включает указатель столбца и строки. MS Excel поддерживает два типа стилей ссылок А1 и R1C1 (рис. 3).

Рис. 3. Стили ссылок в MS Excel

По умолчанию MS Excel использует стиль ссылок A1, определяющий столбцы комбинациями букв (от A до IV, максимум 256 столбцов), а строки – номерами (от 1 до 65536). Эти буквы и номера называются заголовками строк и столбцов.

В ячейку можно вводить данные трех основных типов: надписи, значения и формулы. Отличить тип данных при вводе помогает установленное по умолчанию выравнивание: по левому краю – для надписей, и по правому – для чисел.

Текстовые данные интерпретируются как надписи. Числовые – как значения, которые могут отображаться в различных форматах (числовом, процентном, денежном и пр.). Формула – это запись, начинающаяся со знака равно (=) и предписывающая табличному процессору выполнить расчет значений в ячейке или группе ячеек. Элементами формул могут быть:

· операторы (они приведены в таблице 1);

· ссылки на ячейки или группы ячеек;

· константы (значения или надписи);

· функции и их аргументы;

· скобки (задают порядок выполнения действий в формуле).

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

Таблица 1

Операторы формул MS Excel

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

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

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

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

При необходимости сохранять в формуле ссылку на точно указанную позицию применяется абсолютная адресация, которая остается неизменной при копировании или перемещении формулы. Различать относительную и абсолютную ссылки помогает знак доллара ($). Комбинация абсолютной и относительной ссылок называется смешанной ссылкой. Изменение типа ссылки при вводе формул осуществляется клавишей F4.Рис. 4 иллюстрирует изменения формул с перечисленными типами ссылок при копировании.

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

Рис. 4. Примеры изменения формул при копировании

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

Рис. 5. Структура строки формул

В MS Excel существует два типа форматирования – стилевое и числовое.

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

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