МВД РОССИИ САНКТ-ПЕТЕРБУРГСКИЙ УНИВЕРСИТЕТ Кафедра математики и информатики Тема 5 «Обработка табличных электронных документов» для специальности 030901. 65 – Правовое обеспечение национальной безопасности Профессор Домбровская Л. А. Материал презентации рассмотрен на заседании ПМС кафедры математики и информатики (протокол № 7 от 02. 13 г. )
Лекция 5/1. Обработка табличных электронных документов
Учебные вопросы: 1. Электронные таблицы как электронный документ. 2. Табличные процессоры – основные понятия. Элементы интерфейса табличного процессора Microsoft Excel. 3. Организация вычислительного процесса. 3
1. Электронные таблицы как электронный документ В любой сфере деятельности существует множество задач, в которых исходные и результирующие данные должны быть представлены в табличной форме. Для автоматизации расчетов в подобных задачах имеется класс программных продуктов, называемых табличными процессорами или электронными таблицами. Технология работы с электронными таблицами, создаваемыми в среде табличного процессора, в настоящее время столь же популярна, как и технология создания текстовых документов. 4
При работе с табличными процессорами создаются документы электронные таблицы (ЭТ). Электронная таблица (документ) создается в памяти компьютера. В дальнейшем ее можно просматривать, изменять, записывать на магнитный диск для хранения, печатать на принтере. Итак, электронная таблица – это: 1) компьютерная программа по созданию таблиц; 2) собственно электронный документ в памяти ПК. 5
История электронных таблиц Идею электронных таблиц впервые сформулировал американский учёный австрийского происхождения Рихард Маттезих (нем. Richard Mattesich), опубликовав в 1961 г. исследование под названием «Budgeting Models and System Simulation» . Концепцию дополнили в 1970 г. Пардо (англ. Rene Pardo) и Ландау (англ. Remy Landau), подавшие заявку на соответствующий патент. Патентное ведомство отклонило заявку, но авторы через суд добились отмены этого решения. 6
История электронных таблиц Общепризнанным родоначальником электронных таблиц как отдельного класса ПО является Дэн Бриклин, который совместно с Бобом Фрэнкстоном разработал программу Visi. Calc в 1979 г. Эта электронная таблица для компьютера Apple II стала очень популярной, превратив персональный компьютер из игрушки для технофилов в массовый инструмент для бизнеса. 7
История электронных таблиц Впоследствии на рынке появились многочисленные продукты этого класса — Super. Calc, Microsoft Multi. Plan, Quattro. Pro, Lotus 1 -2 -3, Microsoft Excel, Open. Office. org Calc, таблицы Apple Works и gnumeric, минималистический Spread 32. Существуют электронные таблицы для мобильных телефонов и КПК, в частности Spread. CE. 8
Перечень программных продуктов Операционные системы Название Примечание UNIX Mac OS X Microsoft Windows Gnumeric Да Да Да KSpread Да Да Да Входит в состав KOffice. Lotus 1 -2 -3 Нет Да Самая известная программа, впервые выпущенная 26 января 1983 под DOS. Microsoft Excel Нет Да Да Впервые выпущена в 1985 под Mac OS. Numbers Нет Да Нет Входит в состав i. Work. Libre. Office Calc Да Да Да Входит в состав Libre. Office. Open. Office. org Calc Да Да Да Входит в состав Open. Office. org. 9
Понятие и структура таблицы как документа Таблица — это организованный в вертикальные колонки (графы или столбцы) и горизонтальные строки словесно цифровой материал, образующий своеобразную сетку, каждый элемент которой — составная часть и столбца, и графы. Таблица (польск. tablica < лат. tabula — доска, таблица) — 1) перечень сведений, цифровых данных, запись их в известном порядке, по графам; 2) печатный материал, сгруппированный в виде нескольких столбцов (граф), имеющих самостоятельные заголовки и отделенных друг от друга линейками. На пересечении столбца и строки образуется ячейка и устанавливается графическая смысловая связь между понятием, объединяющим материал в строку, и понятием, объединяющим материал в столбец, что позволяет выявить ее без мысленного перевода в словесную форму и существенно облегчить усвоение и анализ организованных в таблицу данных. 10
Понятие и структура таблицы Существуют определенные условия, при которых организация материала в таблицу не имеет смысла: в таблице нет надобности, если включаемые в нее данные не носят справочного характера, образуют лишь одну строку, из которой используется далеко не вся информация; таблица уступает организации данных в виде текста, когда ради нескольких числовых данных приходится строить сложную по структуре заголовочную часть, занимающую много места и требующую значительных усилий при ее восприятии. Таблицу рекомендуется заменить графиком или диаграммой, если необходимо наглядно продемонстрировать характер протека ния процесса, выявить структуру, показать соотношение частей. 11
«Шапка» таблицы № пп Районы Санкт. Петербурга Количество ДТП в праздничные Всего дни 8. 03. 13 Б О К О В И Н А 9. 03. 13 10. 03. 13 1 Красносельский 5 4 3 12 2 Кировский 2 6 4 12 3 Московский 5 3 2 10 ИТОГО 12 13 9 34 12
Преимущества использования табличной формы представления информации: потребность предварительного проектирования таблицы (анализ и систематизация всех данных); табличная или матричная форма записи данных является наиболее плотной, а значит и более обозримой. 13
Преимущества использования табличной формы представления информации: Данные, содержащиеся в ячейках такой таблицы, связаны между собой отношениями, которые описываются формулами. Изменение содержимого одной ячейки приводит к автоматическому пересчету данных, находящихся в связанных с ней ячейках. Поэтому электронные таблицы избавляют от проведения расчетов вручную, позволяют сэкономить массу времени и широко применяются в самых различных сферах человеческой деятельности. 14
В таблице различают исходную или первичную информацию, а также – производную. Производная информация является результатом различных арифметических и иных операций, совершаемых над первичными данными. 15
Основные функции (возможности) табличного процессора Excel: 1. Формирование (проектирование) таблицы с "шапкой" и боковиной; 2. Ввод данных и формул; 3. Форматирование всех элементов таблицы, редактирование данных; 4. Построение, редактирование и форматирование диаграмм и других видов графиков; 5. Создание баз данных – списков; построение запросов, сортировка данных; 16
Основные функции (возможности) табличного процессора Excel: 6. Создание сводных таблиц, консолидация данных; 7. Решение задач оптимизации (Подбор параметра, Поиск решения, Сценарии "что если" и другие задачи); 8. Статистическая обработка данных, анализ и прогнозирование (инструменты анализа из надстройки "Пакет анализа"). 17
Область применения Excel: планово – финансовые и бухгалтерские расчеты, учет материальных ценностей, системы поддержки принятия решений (СППР) и другие области применения. 18
Одно из ОСНОВНЫХ НАПРАВЛЕНИЙ ИСПОЛЬЗОВАНИЯ КОМПЬЮТЕРНЫХ ТЕХНОЛОГИЙ В БОРЬБЕ С ПРЕСТУПНОСТЬЮ 1. Оформление материалов оперативных, уголовных, надзорных и судебных дел, а также служебной документации с помощью стандартных программ для ЭВМ: ● текстовых и гипертекстовых редакторов (например, MS WORD); ● электронных таблиц (например, MS Excel); ● программ обработки фото и видеоизображений (например, MS Power Point для оформления фототаблиц); ● графических редакторов (для оформления приложений к протоколам следственных действий – планов, схем и чертежей). 19
2. Табличные процессоры – основные понятия. Элементы интерфейса табличного процессора Microsoft Excel. 20
Программа Excel входит в офисный пакет программ Microsoft Office и предназначена для подготовки и обработки электронных таблиц под управлением операционной оболочки Windows. Версии программы Excel 4. 0 и Excel 5. 0 предназначены для работы в Windows 3. 1, а Excel 7. 0 и 97 для Windows 95/98. Есть Excel 2000, входящий в пакет программ Office 2000, работающий в Windows 2000. Чем старше № версии Excel, тем она совершеннее. В настоящее время наиболее популярны Excel-2003, Excel 2007, Excel-2010, Excel-2013. Программа Excel относится к основным офисным компьютерным технологиям обработки числовых данных. 21
Вид окна ЭТ EXCEL 2003 22
Вид окна Excel 2007 23
Вид окна ЭТ EXCEL 2010 24
Рабочим полем табличного процессора является экран дисплея, на котором электронная таблица представляется в виде матрицы. ЭТ, подобно шахматной доске, разделена на клетки, которые принято называть ячейками таблицы. Строки и столбцы таблицы имеют обозначения. Чаще всего строки имеют числовую нумерацию, а столбцы буквенные (буквы латинского алфавита) обозначения. Как и на шахматной доске, каждая клетка имеет свое имя (адрес), состоящее из имени столбца и номера строки, например: А 1, С 13, F 24 и т. п. 25
Но если на шахматной доске всего 8 х8=64 клетки, то в электронной таблице ячеек значительно больше. Например, у табличного процессора Excel таблица максимального размера содержит 256 столбцов и 16384 строки (EXCEL 97). Поскольку в латинском алфавите всего 26 букв, то начиная с 27 го столбца используются двухбуквенные обозначения, также в алфавитном порядке: АА, АВ, AC, . . . , AZ, ВА, ВВ, ВС, . . . , BZ, СА. . . Последний, 256 й столбец имеет имя IY. Значит, существуют ячейки с такими, например, именами: DL 67, HZ 10234 и т. п. 26
При загрузке табличного процессора обычно открывается рабочее окно, отображающее инструментальные панели, информационные поля и собственно электронную таблицу. Отображаемая электронная таблица, как правило, является одной из многих, объединенных в книгу или блокнот, являющихся объектом хранения в отдельном файле. В связи с этим отдельная таблица именуется рабочим листом. 27
Документом (т. е. объектом обработки) Excel является файл с произвольным именем и расширением . xls или. xlsx Такой файл называется рабочей книгой, ее окно занимает большую часть окна программы. Новой книге, которая создается после запуска, автоматически присваивается имя "Книга 1" (Bookl). Книга Excel состоит из набора рабочих листов, каждый из которых имеет собственное название и может содержать одну или несколько таблиц. Подобная организация документа очень удобна, поскольку позволяет собрать вместе сведения, относящиеся к одному проекту. 28
Адрес ячейки Объекты электронной таблицы Номер столбца Номер строки Ячейка Столбец Строка формул Блок ячеек
Технические характеристики и ограничения листа и книги EXCEL 2007 (2010) Параметр Максимальное значение Количество открытых книг Ограничено объемом доступной оперативной памяти и ресурсами системы Размер листа 1 048 576 строк и 16 384 столбца Ширина столбца 255 знаков Высота строки 409 пунктов Общее количество знаков в ячейке 32 767 знаков Число знаков в верхнем и нижнем колонтитуле 255 Количество листов в книге Ограничено объемом доступной оперативной памяти (по умолчанию 3 листа) Количество цветов в книге 16 миллионов цветов (32 битовый спектр с полным доступом к 24 битовому спектру) Количество уникальных форматов/стилей ячеек 64 000 Количество стилей заливки 32 30
Характеристики Excel 2003 Excel 2007 Количество строк 65536 1 048 576 Количество столбцов 256 16 384 Итак, теперь рабочий лист имеет 1 048 576 строк и 16 384 столбца, т. е. содержит больше 17 миллиардов ячеек — почти по 3 ячейки на каждого жителя нашей планеты. Другими словами, рабочий лист Excel 2007 (2010)имеет в тысячу раз больше ячеек, чем рабочий лист Excel 2003 31
Основные параметры электронных таблиц Ячейка – элементарный объект электронной таблицы, расположенный на пересечении столбца и строки. Строка – все ячейки, расположенные на одном горизонтальном уровне. Столбец – все ячейки, расположенные в одном вертикальном ряду таблицы. Диапазон (блок) ячеек – группа смежных ячеек, которая может состоять из одной ячейки, строки (или ее части), столбца (или его части), а также из совокупности ячеек, охватывающих прямоугольную область таблицы. При работе на компьютере электронная таблица существует в форме рабочего листа, который имеет имя(например, Лист1). Рабочие листы объединяются в книги.
Адресация ячеек и блоков Блок (диапазон ячеек) – это прямоугольная группа ячеек. Его координаты указываются по диагонали через разделитель "двоеточие": адрес лев. верхней ячейки : адрес прав. нижней ячейки. А 1: А 1 – блок в виде отдельной ячейки; А 1: Е 1 часть строки; В 1: В 12 – часть столбца; A 1: F 27 прямоугольная группа ячеек. 33
Примеры фрагментов электронной таблицы A B C D E F G H I J 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Диапазон строк 6: 8 Диапазон столбцов C: F Блок ячеек H 12: J 14 34
Информация, вводимая в ячейку ЭТ ЧИСЛОВЫЕ ДАННЫЕ Выравниваются по правому краю. Наборы цифр от 0 до 9, могут включать спец. символы: +, -, %, Е и др. В десятичных дробях исп-ся и ". " и ", " -147 – отриц. число. 008 – будет целое 8 5/4 – надо набрать: 0 <пробел> 5/4. В ячейке и на принтере будет 1 ¼, а в строке формул – 1, 25. Число в ячейке не > 15 символов; далее в экспоненциальную форму Выравнивается по левому краю. Если набранная последовательность символов не является ни числом ни формулой, то является текстом. Если набрать '5/4, то введется текстом, а не числом, или "=5/4" ФОРМУЛЫ ТЕКСТ Начинаются со знака "=" =А 4+А 5*А 5 =СУММ (В 2: В 10) 35
Формат представления значений задает вид отображения данных в зависимости от требований оформления табличного документа и интерпретации его содержимого. Обычно табличный процессор предлагает следующие форматы: общий; числовой; денежный; финансовый; календарный; временной; процентный; дробный; экспоненциальный; текстовый; задаваемый пользователем. Общий формат предназначен для отображения как числовой, так и текстовой информации произвольного вида. 36
Текстовый тип данных Текстовые данные представляют собой некоторый набор символов. Если первый из них является буквой, кавычкой, апострофом или пробелом, либо цифры чередуются с буквами, то такая запись воспринимается как текст. Действия над текстовыми данными производятся аналогично действиям над объектами в текстовом процессоре. Пример текстовых данных: Расписание занятий 311 учебный взвод ‘’ 236 001 счет
Числовой тип данных Числовые данные представляют собой последовательность цифр, которые могут быть разделены десятичной запятой и начинаться с цифры, знака числа (+ или -), или десятичной запятой. Над числовыми данными в электронной таблице могут производиться различные математические операции. Пример числовых данных: 232, 5 13, 7 +100 , 345
Тип данных – даты Этот тип данных используется при выполнении таких функций, как добавление к дате числа, получение разности двух дат, при пересчете даты. Например вперед или назад. Пересчет чисел в даты производится автоматически в зависимости от заданного формата. Табличный процессор позволяет представлять вводимые числа как даты несколькими способами. Пример. Представление дат в разных форматах: 4 июня 1989 06. 98 Июнь 2001 4 июня 04. 06. Июнь
Тип данных Процентный формат данных Процентный формат обеспечивает представление числовых данных в форме процентов со знаком %. Например, если установлена точность в один десятичный знак, то при вводе числа 0. 257 на экране появится 25. 7%, а при вводе числа 257 на экране появится 25700. 0%. Денежный формат обеспечивает такое представление чисел, при котором каждые три разряда разделены пробелом, а следом за последним десятичным знаком указывается денежная единица размерности – «р» (рубли). Например, число 12345 будет записано в ячейке
Итак, каждая ячейка электронной таблицы характеризуется следующими параметрами: адрес ячейки; содержание ячейки; значение ячейки; формат ячейки. Обычно при выполнении операций копирования фрагменту копии передаются все свойства соответствующих ячеек фрагмента оригинала, но возможна передача только содержания, значения или формата. Понятия адреса и формата ячеек рассмотрены выше. 41
В качестве содержания ячейки выступают числовые и текстовые константы, а также выражения (формулы). В качестве значения ячейки рассматриваются выводимые на экран представления числовых и текстовых констант, а также результатов вычисления выражений (формул). Под выражением понимается совокупность операндов, соединенных знаками операций. В качестве операндов используются числовые и текстовые константы, адреса ячеек и встроенные функции. При этом числовые и текстовые константы используются не посредственно, вместо адресов ячеек используются значения соответствующих клеток таблицы, а вместо встроенных функций ис пользуются возвращаемые ими значения. 42
Форматирование ячеек. Под форматированием ячеек понимают: установку формата данных выравнивание текста в ячейке изменение шрифта задание границ ячеек изменения вида ячеек защиту ячеек 43
Для доступа ко всем вышеперечисленным опциям необходимо выделить ячейку (блок ячеек), по выделенному щелкнуть ПКМ и из контекстного меню выбрать команду Формат ячеек. Горячие клавиши: Ctrl 1 44
3. Организация вычислительного процесса 45
Понятие формулы Ввод формулы начинается со знака равенства. Если его пропустить, то вводимая формула будет воспринята как текст. В формулы могут включаться числовые данные, адреса объектов таблицы, а также различные функции. Различают арифметические (алгебраические) и логические формулы. Если ячейка содержит формулу, то в рабочем листе отображается текущий результат вычисления этой формулы. Если сделать ячейку текущей, то сама формула отображается в строке формул.
Правило использования формул Если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко можно выполнить в «уме» . 47
Ссылки на ячейки Ячейка, содержащая формулу, таким образом, является зависимой. Значение, отображаемое в ячейке с формулой, пересчитывается при изменении значения ячейки, на которую указывает ссылка. 48
Способы задания ссылки на ячейку Адрес ячейки можно ввести вручную. Щелчок на нужной ячейке или выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон при этом выделяются пунктирной рамкой. 49
Редактирование формулы Двойной щелчок на соответствующей ячейке. При этом ячейки (диапазоны), от которых зависит значение формулы, выделяются на рабочем листе цветными рамками, а сами ссылки отображаются в ячейке и в строке формул тем же цветом. Двойной щелчок ЛКМ в ячейке с любыми данными – возможность их редактирования. Или клавиша F 2 50
Арифметические формулы аналогичны математическим соотношениям. В них используются арифметические операции (сложение «+» , вычитание «-» , умножение «*» , деление «/» , возведение в степень «^» . При вычислении по формулам соблюдается принятый в математике порядок выполнения арифметических операций.
Пример вычисления по арифметическим формулам Пусть в С 3 введена формула =А 1+7*В 2, а в ячейках А 1 и В 2 введены числовые значения 3 и 5 соответственно. Тогда при вычислении по заданной формуле сначала будет выполнена операция умножения числа 7 на содержимое ячейки В 2 (число 5) и к произведению (35) будет прибавлено содержимое ячейки А 1 (число 3). Полученный результат, равный 38, появится в ячейке С 3, куда была введена эта формула.
Итоговые вычисления предполагают получение числовых характеристик, описывающих определенный набор данных в целом. Проведение итоговых вычислений в Excel выполняется при помощи встроенных функций. Особенность использования таких функций состоит в том, что при их задании программа пытается «угадать» , в каких ячейках заключен обрабатываемый набор данных, и задать параметры функции автоматически. 53
Суммирование Диапазон суммирования, выбираемый автоматически, включает ячейки с данными, расположенные над текущей ячейкой или слева от нее и образующие непрерывный блок. При неоднозначности выбора используется диапазон, непосредственно примыкающий к текущей ячейке. Наиболее часто используется кнопка меню ∑ автосумма. Кроме того, можно использовать встроенную функцию. Например, =СУММ(А 5: А 10). 54
Функции для итоговых вычислений Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке формул или с использованием мастера функций. Пиктограмма для вызова встроенных функций fx 55
Группы встроенных функций в EXCEL Встроенные функции имеют тот же смысл, что и в языках про граммирования высокого уровня, но в табличных процессорах их набор существенно больше. Существуют следующие группы встроенных функций: функции для работы с базами данных и списками; функции для работы с датами и временными значениями; функции для инженерных расчетов; функции проверки свойств и значений; логические функции; функции для работы со ссылками и массивами; математические функции; функции для статистических расчетов; текстовые функции; финансовые функции. 56
Встроенная функция как операнд выражения записывается в виде FUNCTION (список аргументов). Здесь FUNCTION представляет собой имя встроенной функции (зарезервированное слово табличного процессора), а список аргу ментов задается в виде перечня объектов (числовых и текстовых констант, адресов ячеек, диапазонов строк и столбцов, блоков яче ек, имен встроенных функций), разделенных принятым в конкрет ной операционной среде символом разделителем. 57
=СРЗНАЧ(А 2: А 10) 58
Относительные, абсолютные и смешанные ссылки
Относительная ссылка адрес ячейки, автоматически изменяющийся при копировании формулы
Относительные ссылки Относительная ссылка – автоматически изменяющаяся при копировании формулы ссылка. Пример: Относительная ссылка записывается в обычной форме, например F 3 или E 7. Во всех ячейках, куда она будет помещена после ее копирования, изменятся и буква столбца и номер строки. Относительная ссылка используется в формуле в том случае, когда она должна измениться после копирования. В ячейку С 1 введена формула, в которой используются относительные ссылки. Копировать формулу можно «растаскивая» ячейку с формулой за правый нижний угол на те ячейки, в которые надо произвести копирование. Посмотрите, Как изменилась Формула при Копировании.
Абсолютная ссылка Записывается с символом $ перед буквенной и числовой частью. Адрес ячейки при копировании формулы не изменяется.
Абсолютные ссылки Абсолютная ссылка – не изменяющаяся при копировании формулы ссылка. Абсолютная ссылка записывается в формуле в том случае, если при ее копировании не должны изменяться обе части: буква столбца и номер строки. Это указывается с помощью символа $, который ставится и перед буквой столбца и перед номером строки. Пример: Абсолютная ссылка: $А$6. При копировании формулы =4+$A$6 во всех ячейках, куда она будет скопирована, появятся точно такие же формулы. В формуле используются абсолютные ссылки Обратите внимание, что при копировании формулы на другие ячейки, сама формула не изменятся.
Записывается с символом $ только перед буквенной частью Смешанные ссылки Записывается с символом $ только перед числовой частью
Смешанные ссылки Смешанная ссылка используется, когда при копировании формулы может изменяться только какая то одна часть ссылки – либо буква столбца, либо номер строки. При этом символ $ ставится перед той частью ссылки, которая должна остаться неизменной. Пример: Смешанные ссылки с неизменяемой буквой столбца: $C 8, $F 12; смешанные ссылки с неизменяемым номером строки: A$5, F$9.
Операции редактирования объектов ЭТ Для объектов электронной таблицы определены следующие операции редактирования, объединенные в одну группу: удаление, очистка, вставка, копирование. Операция перемещения фрагмента сводится к последовательному выполнению операций удаления и вставки. Перед выполнением конкретной операции редактирования необходимо определить объект, над которым выполняется дей ствие. По умолчанию таким объектом является текущая ячейка. Остальные объекты должны быть выбраны (выделены). Это обычно выполняется с помощью мыши или клавиатуры. 66
Операции редактирования объектов ЭТ Операция очистки содержимого фрагмента электронной табли цы удаляет данные из его ячеек, оставляя на месте сами ячейки. В табл. 1 иллюстрируется выполнение очистки блока ячеек В 2: СЗ. В отличие от очистки операция удаления приводит не только к очистке содержимого ячеек фрагмента, но и к удалению из электронной таблицы самих ячеек. Однако при этом надо указать направление сдвига соседних с удаляемым фрагментом ячеек для заполнения освободившегося места. Обычно указываются направления влево и вверх 67
Операция очистки содержимого фрагмента электронной таблицы Таблица 1 До выполнения очистки После выполнения очистки А В С D 1 11 12 13 14 2 21 22 23 24 2 21 24 3 31 32 33 34 3 31 34 4 41 42 43 44 68
Результаты удаления фрагмента электронной таблицы Со сдвигом влево Со сдвигом вверх А В С D 1 11 12 13 14 2 21 24 2 21 42 43 24 3 31 34 4 41 42 4 41 44 43 44 69
Удаление строк и столбцов (равно как и их диапазонов) приводит к смыканию соответствующих соседних строк и столбцов. При удалении фрагментов электронной таблицы происходит присвоение новых адресов ячейкам по результатам сдвига на место удаленных фрагментов. 70
Скачать презентацию МВД РОССИИ САНКТ-ПЕТЕРБУРГСКИЙ УНИВЕРСИТЕТ Кафедра математики и информатики
ПРЕЗ_РЛ 5.1 ИИТПД ПОНБ.ppt