Системы обработки табличной информации

Вопросы для изучения 1. Табличные процессоры: функциональные возможности. 2. Табличный процессора Excel. Основные понятия Excel: книга, лист, электронная таблица, ячейка, страница, адрес ячейки, виды ссылок, собственные имена ячеек. Настройка рабочей среды. Технология работы (подробно рассматривается на лабораторных занятиях). Работа с формулами. Мастер функций. 3. Типы данных в Excel. Числовые и текстовые данные, дата и время. Форматы числа. Создание пользовательского формата числа. 4. Автозаполнение данных: формул, числовых, текстовых. Создание пользовательских списков для автозаполнения. 5. Возможности деловой графики в Excel. 6. Технология создания связанных таблиц в Excel. 7. Возможности Excel по работе со списком (базой данных): работа с формой, сортировка, фильтрация, подведение итогов, создание сводных таблиц.

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

Возможности ТП: l встроенные мат. функции и алгоритмы статистической обработки Д; l средства для связи таблиц между собой, l создания и редактирования электронных баз данных. l встроенная справочная система l возможность выборки в БД по критерию l наличие средств автоматического создания отчетов с использованием многих таблиц, графиков, диаграмм, снабжать их комментариями и графическими иллюстрациями. l и др. Самые популярные ТП: Microsoft Excel и Lotus 1— 2— 3.

2. Табличный процессора Excel. Основные понятия Excel: книга, лист, электронная таблица, ячейка, страница, адрес ячейки, виды ссылок, собственные имена ячеек.

Элементы интерфейса MS Excel Cтрока основного Строка Панели Строка Кнопки меню заголовка инструментов формул управления Заголовки столбцов Текущая ячейка Заголовки строк Поле имени Строка состояния Ярлычки листов Линейки прокрутки

Основные понятия Excel: Электронная таблица — программа обработки Д, которые можно представить в виде таблицы, образованной строками и столбцами. Способы обозначения строк и столбцов ЭТ: 1) Столбцы обозначаются буквами английского алфавита - A, B, C, D. . . , а строки числами — 1, 2, 3. . ; после столбца с именем Z будут следовать столбцы с именами AA, AB, AC. . . AZ, BA, BB, BC. . . BZ, . . . 2) Столбцы и строки обозначаются числами. Переключение: Сервис-Параметры-Общие-Стиль ссылок.

Ячейка — базовый элемент ЭТ, область рабочей таблицы, находящаяся на пересечении столбца и строки, имеющая своё уникальное имя. 1. Сначала пишется буква столбца, а потом номер строки. A 4, BZ 1056, B 99. 2. Сначала пишут букву R (сокращение от слова Row — строка), номер строки, затем букву С (сокращение от слова Column — столбец) и номер столбца. Те же ячейки, что и в предыдущем примере, будут иметь при такой записи имена R 4 C 1, R 1056 C 78, R 99 C 2.

Смежная группа (блок) ячеек - связанная прямоугольная часть ЭТ. Адрес блока состоит из координат противоположных углов, разделенных двоеточием (A 1: B 3). Несмежная группа (блок) ячеек - совокупность ячеек и связанных групп ячеек. Обозначают перечисляя имена, разделенные символом “; ” (точка с запятой). Содержимое ячейки - это И, которая в нее записана (числа, текст, формулы или может быть пустой); Текущая (активная) ячейка – ячейка ЭТ, в которой в данный момент находится курсор.

l лист – основа для выполнения вычислений, разделен на строки и столбцы, состоит из 256 столбцов и 65536 строк; l книга – файл, используемый для обработки и хранения Д, состоящий из отдельных листов;

l ссылка указывает на ячейку или блок (диапазон) ячеек листа в формуле; l абсолютная ссылка не изменяется при копировании формулы в другую ячейку. (например, =$В$20 или =С$112; при копировании таких ссылок, то, перед чем стоит $, не изменяется); l относительная ссылка автоматически изменяется при копировании в соответствии с положением формулы.

Ссылки l Относительные ссылки (например: D 5). • Абсолютные ссылки – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например: $D$9). Абсолютные ссылки Относительные ссылки

Ссылки на листы и книги l Ссылка на ячейку в пределах листа =E 7 l Ссылка на ячейку на другом листе =Лист2!E 7 l Ссылка на ячейку в другой рабочей книге =[Книга 1. xls]лист2!E 7

2. 2 Настройка рабочей среды Способы настройки: l настройка меню и панелей инструментов (есть возможность создавать собственные);

Вид Панели инструментов Настройка Вкладки: l Панели инструментов позволяют отобразить, скрыть, добавить пользовательскую ПИ… l Команды - добавить на ПИ новые кнопки или меню l Параметры – управлять изображением ПИ, подсказок, размером пинктограмм, эффектом раскрывающегося меню.

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

l использование специальных утилит (надстроек), которые присоединяются к Excel и расширяют возможности. Сервис Надстройки Расширение файла . xla

2. 3 Технология работы Технология создания ЭТ: 1. Проектирование и разработка форм выходных документов (на бумаге), и алгоритмов получения расчетных Д. 2. Разработка ЭТ: создание заголовка, шапки, внесение формул в расчетные колонки. 3. Ввод Д и получение расчетных значений. 4. Сохранение ЭТ на внешнем носителе. 5. Вывод ЭТ на печать.

2. 4 Работа с формулами. Мастер функций Формула - выражение, начинающееся со знака равенства (=) и состоящее из числовых величин, адресов ячеек, функций и знаков арифметических операций. =330+25 =А 5+12 =А 1 -B 7 =A 1 -2*C 4 Мастер функций вызывается Вставка Формула

Функции: l математические l логические l статистические l текстовые l даты и времени l ссылок на массивы l работы с базами данных l проверки свойств и значений

Функции —это запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений. =СУММ(A 4: A 12) =СРЗНАЧ(D 2: D 10) =МАХ(C 2: C 10) =MIN(B 2: B 10) = СРЗНАЧ (B 3: B 9) § Знак равенства § Имя функции § Аргументы: число, текст, логические значения

Принципы построения функций Аргументами функций могут быть : l числовые или текстовые значения СУММ(А 1: А 10) суммирует значения диапазона ячеек А 1: А 10 ЧИСТРАБДНИ("10. 05. 1999"; "20. 05. 1999") - вычисляет количество рабочих дней между двумя датами l логические значения l массивы l пустой аргумент СЕГОДНЯ() – вычисляет текущую дату.

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

Типы данных Константы Формулы Числа Текст Значения даты и времени

Типы данных l Текст – строка не более 32000 символов, содержащая буквы, цифры и специальные символы Информатика 21 -37 -20 ‘ 703 2002 -й год

l Число – числовая константа -123 (целое число) 3856, 5657 (дробное число) 1, 2 E+09 (число в экспоненциальной форме) 5, 9 Е-05

l Дата – может быть представлена в различных форматах 20. 03. 02 20 марта 2002 г. 20. 03. 02 14: 42

l Формула – выражение, начинающееся со знака равенства (=) и состоящее из числовых величин, адресов ячеек, функций и знаков арифметических операций =330+25 =А 5+12 =А 1 -B 7 =A 1 -2*C 4

l Функции – это запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений =СУММ(A 4: A 12) =СРЗНАЧ(D 2: D 10) =МАХ(C 2: C 10) =MIN(B 2: B 10)

Формат числа Формат Ячейки… - для форматирования Д. Число – выбирается числовой формат, указывается число знаков после «, » Выравнивание – определить ориентацию содержимого ячейки; Шрифт – выбрать шрифт, цвет; Граница – установить границы вокруг ячеек, тип линии и цвет. Вид – выбрать фон ячейки выбранным цветом Используется по умолчанию Общий формат (текстовые и числовые значения)

Числовые форматы l Числовой - наиболее общий способ представления чисел 0. 1234; -1234 l Денежный - для отображения денежных величин 1 234 р. l Финансовый - для выравнивания денежных величин по разделителю целой и дробной части l Дробный - простые дроби 1/4; 3/7 l Экспоненциальный - для представления числа в экспоненциальном виде 1. 3 Е-05

Ошибки при использовании формул #### - если столбец недостаточно широк для вывода результата формулы или не верный формат ячейки; #ЗНАЧ! – недопустимый тип значений (складываем текст); #ДЕЛ/0 – деление на ноль #ИМЯ – не распознаёт имя или введенную ссылку (введено по-русски) #ЧИСЛО – недопустимый аргумент функции.

4. Автозаполнение данных Используется, если в столбец (строку) таблицы вводятся данные одинаковой структуры (названия месяцев, дней недели, последовательный ряд чисел. . . ). Excel даёт возможность вводить такие данные в виде списка. Варианты: l Правка►Заполнить►Вправо. (влево, вниз, вверх). l Правка►Заполнить►Прогрессия (для заполнения выделенного диапазона ячеек последовательностями чисел или дат). Данные в первых ячейках каждой строки или столбца диапазона будут использованы в качестве начальных значений последовательностей.

5. Возможности деловой графики в Excel ДГ включает диаграммы (графики) различных видов: l на отдельном листе диаграмм; l как графический объект на рабочем листе. Диаграмма l имеет определенный тип; l состоит из серий (рядов) данных и оформительских элементов (заголовка, осей, меток, легенды, произвольного текста).

Типы диаграмм l Гистограмма (в т. ч. объемная): отдельные значения представлены вертикальными столбиками различной высоты. l Линейчатая (в т. ч. Объемная) - полосами разной длины, расположенными горизонтально вдоль оси ОХ. l Круговая (в т. ч. объемная): сумма всех значений принимается за 100%, а процентное соотношение величины изображается в виде круга, разбитого на несколько секторов разного цвета. Допускается только один ряд данных. l Кольцевая: форма круговой диаграммы. Сумма всех значений принимается за 100%, а ряды данных представляют собой вложенные кольца, разделенные на сегменты в процентном соотношении.

l Диаграмма с областями: отдельные ряды данных представлены в виде закрашенных разными цветами областей. l График (в т. ч. объемный): все отдельные значения будут соединены между собой линиями. l XY (Точечная) : отдельные значения таблицы представлены в декартовой системе координат. l Объёмная поверхностная: совокупность всех значений отображается на диаграмме в виде некоторой поверхности, области которой представляют собой ряды данных.

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

Типы диаграмм l Гистограммы (обычная, с накоплением, нормированная, объемная, трехмерная)

Типы диаграмм l Круговая (обычная, объемная, вторичная, разрезная, трехмерная)

Типы диаграмм l График (обычный, с накоплением, нормированный, с маркерами, объемный)

Типы диаграмм l Лепестковая l С областями l Кольцевая l Точечная l Пузырьковая l Линейчатая l Биржевая l Поверхность

Построение диаграмм 1. Выделить блок ячеек данных (область данных диаграммы). 2. Вставка► Диаграмма или [Мастер диаграмм] на панели инструментов. 3. тип диаграммы [Далее]. Изменения в исходной таблице автоматически приводаят к изменениям в диаграмме.

Защита данных Сервис►Защита►Защитить лист Формат ►Ячейка►Защита

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

Внешняя ссылка на ячейку другого листа этой же рабочей: Имя_листа!Адрес_ячейки Ссылка на ячейку в другой книге: [Имя_книги]Имя_листа!Адрес_ячейки

7. Возможности Excel по работе со списком (базой данных): l работа с формой; l ввод и просмотр Д; l поиск Д по заданному критерию; l сортировка Д; l фильтрация Д; l подведение итогов.

Особенности: l список должен содержать Д одного типа; l не должно быть объединенных ячеек; l признак конца списка – первая пустая строка.

Работа с формой Форма - созданный на экране шаблон для ввода, просмотра (в том числе по критерию) и редактирования записей БД. 1. Выделить область Д; 2. Данные ►Форма

Для поиска по критерию: 1. Сделать текущей первую запись БД; 2. Данные ►Форма ►Критерии При записи критериев: * –произвольное количество символов; ? – один символ; Используют: =, <, >, <=, >=. Для поиска по нескольким критериям: И.

Сортировка данных Данные ►Сортировка

Фильтрация данных- выбор Д в пределах блока или таблицы, удовлетворяющих условию. Способы фильтрации Д: l Автофильтр (Данные►Фильтр► Автофильтр) 1. Выбор записей с заданным значением поля 2. Выбор записей по условию (Условие), 3. Выбор первых наибольших или наименьших n значений (Первые 10). 4. Все - восстанавливает на экране все скрытые фильтром строки таблицы. l Расширенный фильтр.

l Расширенный фильтр Данные►Фильтр►Расширенный фильтр Позволяет: l применять операции И, ИЛИ; l составлять вычисляемые критерии; l отфильтрованные Д копировать в заданный диапазон рабочего листа.

Исходный диапазон: А 2: F 7 Диапазон условий: А 10: В 11 Поместить результат: А 13: А 20

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

Работа с макросами Макрос - набор макрокоманд, каждая из которых выполняет определенное действие. Служит для автоматизации работы пользователя. Макросы создают с использованием: l VBA-кода; l макрорекордера (записывает все действия пользователя и преобразует их команды VBA).

Создание макроса Сервис► Макрос► Начать запись Указать имя макроса ; ввести команды; Вид►Панели инструментов►Остановить запись Запустить на выполнение Сервис►Макросы ► Имя макроса

Подготовка к печати. Печать электронной таблицы Файл►Параметры страницы Вид►Колонтитулы Файл►Параметры страницы► Лист ► Печать на каждой странице ► Сквозные строки ввести адрес сквозной строки. Файл Предварительный просмотр Вставка►Разрыв страницы Файл►Печать