1 Тема 4. Технология обработки данных с помощью

1 Тема 4. Технология обработки данных с помощью электронных таблиц. 1

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

3 3 1. Назначение и основные функции электронных таблиц (ЭТ). ЭТ - прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме.

1. Назначение и основные функции электронных таблиц (ЭТ). создание таблиц различной структуры, в том числе сводных; средства быстрого создания формул и проведения расчетов по ним; корректировка таблиц; редактирование, защита, поиск, сортировка, фильтрация данных в таблицах; средства для оформления таблиц; связывание таблиц; встроенная деловая графика; встроенные функции; 4 4

1. Назначение и основные функции электронных таблиц (ЭТ). наличие шаблонов для часто используемых таблиц; автоматизация обработки таблиц; обмен данными с другими аналогичными приложениями; поддержка OLE-технологии и технологии «drag-and-drog»; публикация данных в Internet; развитая файловая и справочная система; средства анализа данных; совместная работа в сети. 5 5

6 6 2. Интерфейс ЭТ. Ввод данных в таблицу. Форматирование ячеек. Рабочая книга — это файл, предназначенный для хранения ЭТ, имеет расширение .xls (.ods). Книга делится на листы, а листы, в свою очередь, — на ячейки.

8 8 Число столбцов - 256 (от A до IV) Число строк - 65 536 (216) Число ячеек - 16 777 216 (28 x 216 = 224) Каждая ячейка имеет адрес, который образуется: <имя столбца><имя строки>, например А10. Номер строки и буква столбца, обозначающие определенную ячейку, называются ссылкой на ячейку. Диапазон ячеек — это прямоугольная область в таблице, содержащая несколько выделенных ячеек. Адрес диапазона образуется как: <адрес 1-й ячейки> : <адрес последней ячейки>, например А1:А10, A10:D20.

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

10 10 Режимы просмотра листа Для изменения режима просмотра листа используют команды меню Вид. По умолчанию установлен режим просмотра Обычный.

11 11 В режиме Разметка страницы в окне отображается только сама таблица и границы страниц.

13 13 Ввод данных Данные можно вводить в ячейку или в строку формул. Для ввода данных с использованием клавиатуры следует выделить ячейку, ввести данные с клавиатуры непосредственно в ячейку или в строку формул и подтвердить ввод, одним из трех способов: нажать клавишу клавиатуры Enter, Tab или клавиши перехода: ←, ↑, →, ↓; нажать кнопку Ввод (зеленая галочка) в строке формул; выделить любую другую ячейку на листе (нельзя использовать при вводе формул). В ячейке может находиться до 32767 символа, но отображаются только 1024. Исключение составляют формулы -- длина записи до - 1024 символа.

14 14 Ввод текста Если весь текст ячейки не помещается по ширине столбца, а ячейки справа не содержат данных, то текст отображается на листе на соседних ячейках справа (ячейка А1). Если же ячейки справа содержат какие-либо данные, то весь текст на листе не отображается (ячейка А2). При этом текст в строке формул отображается полностью.

15 15 Ввод чисел При первоначальном вводе числа в ячейке может отобразиться число из 11 цифр. При вводе большего числа происходит автоматическое форматирование ячейки, и число будет отображено в экспоненциальном формате (ячейка В3). Если же ширина столбца была уменьшена и число не может быть отображено в ячейке, то вместо числа в ячейке отображаются символы # .

16 16 Существуют следующие правила ввода чисел: Ввод чисел осуществляется в общем числовом формате. Числовые значения, заключенные в круглые скобки, интерпретируются как отрицательные. Например, (5) интерпретируется, как -5. Если ввод числа начать со знака денежной единицы, к ячейке будет применен денежный формат. Если ввод числа закончить знаком %, к ячейке будет применен процентный формат.

17 Способы упрощения и ускорения ввода данных копирование; автозаполнение; ввод прогрессий.

Ввод прогрессий: Правка/Заполнить/Ряды

19 19 Типы данных. В ЭТ ячейки могут содержать следующие типы данных: Числовые данные Текстовые Логические данные Даты Формулы Функции Массивы Объекты OLE Тип данных определяется форматом ячеек.

20 20 Формат ячеек Для изменения формата необходимо выбрать: меню Формат / Формат ячеек, либо использовать контекстное меню.

21

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

24 24 Автоформатирование Для применения к таблице одного из типовых вариантов оформления необходимо выполнить команду Формат/Автоформат. В диалоговом окне Автоформат следует выбрать один из предлагаемых вариантов оформления таблицы.

25 25 Условное форматирование - оформление данных изменяется в зависимости от их значения. Первая строка со списком позволяет выбрать в качестве аргумента условия либо содержимое ячейки, либо ее формулу. Вторая строка со списком в диалоговом окне позволяет вам выбрать нужный тип сравнения. Затем установить формат, который будет применяться к данным, отвечающим данному условию. Формат создается в диалоговом окне Формат ячеек.

26 26

28 28 3. Вычисления в ЭТ. Относительные и абсолютные ссылки. Формулы представляют собой выражения, по которым выполняются вычисления. Формула может включать функции, ссылки, операторы и константы. Функция - стандартная формула, которая обеспечивает выполнение определенных действий над значениями, выступающими в качестве аргументов. Ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле. Оператором называют знак или символ, задающий тип вычисления в формуле. Константой называют постоянное значение.

29 29 Ввод формул с клавиатуры С использованием клавиатуры вводят операторы, константы и функции. Операторы вводятся с использованием следующих клавиш: сложение - + (плюс); вычитание - - (минус или дефис); умножение - * (звездочка); деление - / (дробь); возведение в степень - ^ (крышка). =F20/G20*$B$11 =СУММ(L14:L28)‏

30 30 Создание формул с использованием мастера функций Для создания формул с функциями (в MS Excel) следует выделить ячейку и нажать кнопку Вставка функции в строке формул или комбинацию клавиш клавиатуры Shift + F3.

Для создания формул с функциями (в Calc) следует выделить ячейку и нажать кнопку Мастер функций в строке формул или комбинацию клавиш клавиатуры Ctrl + F2.

32 32 Виды функций: MS Excel Calc

33 Относительные и абсолютные ссылки По умолчанию ссылки на ячейки в формулах относительные. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Например, при копировании ячейки D2 на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D4 будет формула =В4*С4 и т. Д.

34 Для того чтобы ссылка на ячейку при копировании не изменялась, необходимо использовать абсолютные ссылки. Абсолютная ссылка на ячейку имеет формат $A$1. Чтобы ссылка на ячейку была абсолютной при создании формулы, после указания ссылки на ячейку следует нажать клавишу клавиатуры F4 (SHIFT+F4). Для преобразования из относительной в абсолютную при редактировании необходимо добавить символ $. Например, для того чтобы ссылка на ячейку G2 стала абсолютной, необходимо ввести $G$2.

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

36 В ЭТ определено семь ошибочных значений: #ДЕЛ/0! (#DIV/0!) — попытка деления на 0. #ИМЯ? (#NAME?) — в формуле используется имя, отсутствующее в списке имен диалога Присвоение имени; #ЗНАЧ! (#VALUE)— выдается при указании аргумента или операнда недопустимого типа; #ССЫЛКА! (#REF)— отсутствует диапазон ячеек, на который ссылается формула; #Н/Д — нет данных для вычислений.; #ЧИСЛО! — задан неправильный аргумент функции; #ПУСТО! — в формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

37 Сортировка данных Сортировка - расположение данных на листе в определенном порядке. Данные/Сортировка. 4. Обработка данных в ЭТ.

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

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

40

Расширенный фильтр

Стандартный фильтр

43 Формирование сводной информации ЭТ содержат средства формирования сводной информации для проведения анализа данных. Сводная информация может быть получена: объединением данных промежуточных итогов; методом консолидации; формированием сводных таблиц. 1. Итоги. Для выполнения этой команды необходимо: представить данные в виде списка; командой Сортировка меню Данные упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги; установить указатель на ячейку списка; выполнить команду Итоги меню Данные; в диалоговом окне Промежуточные итоги задать нужные параметры.

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

46 2. Консолидация — это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе.

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

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

Ячейка с формулой В ячейке с формулой введите ссылку на ячейку, содержащую формулу. Она содержит ссылку на текущую ячейку. Щелкните другую ячейку на листе, чтобы применить ссылку на нее к текстовому полю. Целевое значение Здесь указывается значение, которое требуется получить в качестве нового результата. Изменяемая ячейка Здесь указывается ссылка на ячейку, содержащую значение, которое требуется настроить для подбора значения.

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

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

54