Технологии обработки табличной информации

Технология Электронных Таблиц предназначена для проведения автоматизированных вычислений
Первые электронные таблицы были разработаны более двадцати лет назад для задач бухгалтерского учета. Они включали в себя средства автоматизации вычислений с помощью копирования формул и десяток стандартных функций. Наиболее распространенными электронным таблицами, работающими под DOS, были программы SuperCalc, Framework. К современным электронным таблицам относятся программы Excel и Lotus 1-2-3
Далее

Вид электронной таблицы
Информация в электронной таблице хранится на листах
Каждый лист содержит таблицу, состоящую из 256 колонок и 64 тыс. строк
Колонки обозначаются буквами латинского алфавита A,B,C, D..
Строки таблицы обозначаются номерами
Клетка таблицы называется ячейкой
Каждая ячейка имеет адрес, состоящий из названия колонки и строки
Выделенная ячейка имеет адрес В3
Далее

Данные и формулы
В ячейках таблицы могут храниться данные и формулы
В ячейках В3 и С3 хранятся числа 2 и 3
В ячейку D3 введена формула
Формула видна в строке формул
А результат вычисления отражается в ячейке
Формула начинается со знака равенства
Формулы включают в себя знаки арифметических операций, числа, функции и ссылки на адреса ячеек
Далее

Адресация
Существуют два основных типа ссылок: относительные и абсолютные. Различия между ними проявляется при копировании формул.
При записи адреса ячейки в виде абсолютной адресации значение адреса не меняется в процессе копирования, а при записи в виде относительной адресации меняется. Признаком абсолютной адресации является знак $.
При копировании вверх и вниз – в адресе меняются номера строк, а при копировании вправо и влево – меняются буквы латинского алфавита.
Для ввода в формулу ссылок на ячейки достаточно щелкнуть по данной ячейке и в формуле появится относительная ссылка на данную ячейку. Если необходима запись ссылок в виде абсолютной адресации, то необходимо дополнительно нажать клавишу F4.
Далее
Пример

Пересчитаем стоимость товара в Евро
Применение в формулах абсолютной и относительной адресации
Формула:
Итого(ЕВРО) = Стоимость (руб.)/ Курс Евро
Стоимость товара при копировании формулы должна меняться в зависимости от артикула товара, поэтому ссылку на стоимость необходимо задать в виде относительной адресации
Курс Евро находится в ячейке С4, и при копировании ссылка на ячейку не должна меняться, поэтому адрес ячейки должен быть записан в виде абсолютной адресации
Далее

Набираем знак равенства
Щелкаем по ячейке С7
Набираем знак деления
Щелкаем по ячейке С4 и нажимаем функциональную клавишу F4
Ввод формулы с использованием абсолютной и относительной адресации
Рассмотрим технологию ввода формулы
Нажимаем на пиктограмму завершения ввода формулы
Далее

Копирование формул
Формула введена.
В ячейке D7 виден результат расчета.
Скопируем эту формулу.
Устанавливаем курсор на крестике и делаем двойной щелчок мышью
Далее

Пример копирования
Колонка Итого (Евро) заполняется формулами
Помимо приведенного способа, существуют еще несколько методов копирования:
с использованием меню;
с помощью протаскивания маркера выделения.
Далее

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

Функции обработки данных в диапазонах
Диапазоном называется прямоугольный фрагмент электронной таблицы. Диапазон обозначается адресом левой верхней ячейки и правой нижней.
Для вычисления суммы, среднего значения, максимума и других характеристик диапазона имеются специальные функции, аргументом которых является диапазон.
Сумма - СУММ(В2:D4) ,
Среднее значение -СРЗНАЧ (В2:D4)
Минимум -МИН (В2:D4)
Диапазон В2:D4
Пример обработки данных в диапазоне B2:D4
Далее

Логические функции
Функция И принимает значение ИСТИНА, если выполняются все Условия, в противном случае принимает значение ЛОЖЬ.
Формат функции: И (Условие1, Условие2, …….Условие30).
Функция ИЛИ принимает значение ИСТИНА, если выполняется хотя бы одно из Условий, в противном случае принимает значение ЛОЖЬ.
Формат функции: ИЛИ (Условие1, Условие2, …….Услловие30).
Функция ЕСЛИ вычисляет Формулу 1, если Условие истинно, Формулу 2 - если Условие ложно, причем формула, в свою очередь может включать функцию ЕСЛИ.
Формат функции: ЕСЛИ (Условие; Формула1; Формула2).
К логическим функциям Excel относятся функции И, ИЛИ, ЕСЛИ
Далее
Пример

Функция Если
Задание:.
С целью увеличения товарооборота на оптовой базе установлены торговые скидки. Если определенный вид товара покупается на сумму более чем 10 тыс. рублей, назначается скидка.
Рассчитать сумму продаж с учетом скидки, используя функцию ЕСЛИ
Далее

Функция Если
Набираем знак равенства
Выбираем функцию
Далее

Функция Если
Для удобства работы отодвигаем диалоговое окно «ЕСЛИ», так чтобы были видны на экране ячейки таблицы.
Нажимаем «ОК»
Вводим условие, щелкая мышью по адресам ячеек
Вводим 1 –ю формулу. Величину скидки записываем в виде абсолютной адресации
Вводим 2 –ю формулу
Далее

Функция Если
Формула введена.
В ячейке D7 виден результат расчета.
Скопируем эту формулу.
Устанавливаем курсор на крестике и делаем двойной щелчок мышью
Далее

Функция Если
Задание выполнено. Формула скопирована.
Далее

Технология Обработки Списков
Сортировка
Фильтрация
Обработка данных в группах
Далее

Списки
Списками называется табличный способ представления информации, при котором:
∙ все столбцы таблицы имеют различные имена;
∙ данные в столбцах однородные, (например, числовые значения представлены в одинаковых единицах измерения);
∙ каждая клетка таблицы содержит в себе один элемент информации.
Далее

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

Сортировка по подписи
Если отмечена опция по подписи
То в раскрывающемся списке видны все названия колонок таблицы. Первая строка таблицы в сортировке не участвует.
Далее

Сортировка по обозначению
Если отмечена опция по обозначениям
То в раскрывающемся списке видны все названия столбцов (А,В,С,,) В этом случае сортируются все данные колонки таблицы, включая названия колонок.
Далее

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

Задание условий к числовым данным
Если при выборе данных необходимо выполнение двух или более условий одновременно, то между условиями устанавливается логическая связка – И, если достаточно выполнения хотя бы одного из условий, то логическая связка между условиями - ИЛИ.
Для задания условий к числовым выражениям необходимо:
на числовой оси отметить условия выбора данных;
определить логические связки между условиями.
Если данные удовлетворяющие двум условиям находятся внутри одного числового диапазона, то применяется логическая связка И.
В случае, если данные находятся в разных числовых диапазонах, то применяется логическая связка ИЛИ.
Далее

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

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

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

Автофильтр
Если выбрать из списка фамилию Петров П.П., то на экране отразятся строки, содержащие только эту фамилию.
Далее

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

Автофильтр
Можно выбрать другие условия, например, отразить всех сотрудников.
Далее

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

Автофильтр
Выбираем из списка «Условие».
Далее

Автофильтр
Выбираем из списка 2 тыс. руб.
Данные находятся внутри одного числового диапазона, поэтому выбираем логическую связку И
Нажимаем ОК
Задаем первое условие –
Цена больше или равняется
Выбираем из списка 1 тыс. руб.
Задаем второе условие –
Цена меньше или равняется
Далее

Автофильтр
Задание выполнено
На экране отразились товары с ценой от 1 до 2 тыс. руб.
Далее

Формирование условий выбора происходит в таблице, в заголовках которой находятся названия колонок исходной таблицы, а в клетках таблицы записываются условия.
Условия, связанные логической связкой И, записываются в одной строке таблицы.
Условия, связанные логической связкой ИЛИ, записываются в разных строках.
Логическое выражение записи условий выбора для приведенного ниже условного примера будет выглядеть следующим образом:
((Заголовок1=Условие1) И (Заголовок3=Условие2)) ИЛИ (Заголовок2=Условие3)
Описание условий выборки в режиме расширенного фильтра производится на рабочем листе Excel по стандартным правилам создания запросов в базах данных.
Расширенный фильтр
Далее
Пример

Расширенный фильтр
Рассмотрим действие расширенного фильтра на примере задачи.
Пусть требуется выбрать данные за сентябрь месяц по фирме Омега и все данные по фирме Бета.
Далее

Расширенный фильтр
Формируем заголовки таблицы условий - «Дата» и «Клиент»
Записываем условия отбора данных
В данном случае в таблице условий должно быть записано четыре условия.
Первое условие (Дата >=1/9/99)
Второе условие (Дата <1/10/99)
Третье условие (Клиент= «Омега»)
Четвертое условие (Клиент= «Бета»)
Первые три условия связаны логической связкой И, поэтому они записываются в одной строке.
Четвертое условие связано с первыми тремя условиями логической связкой ИЛИ, поэтому оно записывается в другой строке
Далее

Расширенный фильтр
Вызываем меню
Устанавливаем курсор на любой клетке исходной таблицы
Далее

Расширенный фильтр
Указываем диапазон таблицы условий
Нажимаем ОК
Далее

Расширенный фильтр
Задание выполнено. Данные отфильтрованы
Далее

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

Итоги
Процесс подведения итогов состоит из двух этапов:
первый этап – разбиение данных на группы,
второй этап – анализ данных.
Разбиение данных на группы проводится в процессе сортировки.
Анализ данных в таблице происходит внутри каждой группы, при этом вычисляются такие характеристики, как сумма, максимум, минимум, произведение, дисперсия и т.п
В процессе подведения итогов в исходную таблицу после строк каждой группы включаются дополнительные строки содержащие функцию ПРОМЕЖУТОЧНЫЕ ИТОГИ, а слева от таблицы добавляется структура.
Далее

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

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

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

Сводные таблицы
Вызываем меню
Далее

Сводные таблицы
Нажимаем кнопку «Далее»
Далее

Сводные таблицы
Если при вызове сводной таблицы активная ячейка находится на данных исходной таблицы, то диапазон указывается автоматически.
Нажимаем кнопку «Далее»
Далее

Сводные таблицы
Нажимаем кнопку «Макет»
Далее

Сводные таблицы
Далее
Нажимаем ОК