Тема 1. Основы работы в среде табличного процессора MS Excel

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

Тематический план для студентов ФЭМП 1 курса всех специальностей 2-ой семестр ТемаЛекціїЛабораторніСРСФорми контролю Розділ 8.Табличний процесор MS Excel ТЕМА 8.1, 8.2Основи роботи у сердовищі табличного процесора MS Excel: інтерфейс; технологія створення, редагування та форматування електронних таблиць; 224Тестування, опиту­вання на ПЕОМ ТЕМА 8.3Робота з функ­ціями і формулами у середовищі табличного проце­сора MS Excel. 244Тестування, опиту­вання на ПЕОМ ТЕМА 8.4.Створення, редагування і форматування графіків та діаграм. 2410Тестування, опиту­вання на ПЕОМ ТЕМА 8.5.Робота з базами даних у середовищі MS Excel: впорядкування та пошук даних в базі даних; фільтри; підсумки; групування та структура. 2610Тестування, опиту­вання на ПЕОМ ТЕМА 8.6.Аналіз даних у середовищі MS Excel з використанням апарату: майстер зведених таблиць. 2410Тестування, опиту­вання на ПЕОМ ТЕМА 8.7.Аналіз даних у середовищі MS Excel з використанням апарату: фінасового аналізу; таблиця підстановок, підбід параметрів, пошук рішення, сценарії Тестування, опиту­вання на ПЕОМ Всього144279Іспит

Навчально-методичні розробки Барміна В.А., Кустова Н.П., Цензура М.О. Методичні вказівки та індивідуальні завдання до створення, редагування і форматування електронних таблиць, графіків та діаграм у середовищі Excel. – К.: Київ. держ. торг.-екон. ун-т, – 82 с Барміна В.А., Цезура М.О. Методичні вказівки та індивідуальні завдання для аналізу та статистичної обробки даних у середовищі табличного процесора Excel. – К: КНТЕУ, Александрова В.І. Методичні вказівки для проведення лабораторного практикуму … Аналіз даних з використанням формул та функцій. Робота з базою даних. Побудова зведених таблиць. – К: КНТЕУ, Струневич Л.М. Методичні вказівки та індивідуальні завдання до проведення лабораторної роботи студентів по темі Робота з функціями Excel та робота з формулами для масивів з курсу Інформатика та КТ. – К.: КДТЕУ, – 60 с.

1. Общая характеристика табличного процессора MS Excel. Табличный процессор MS Excel является одним из приложений интегрированного пакета MS Office, который предназначен для организации данных в табличной форме для документирования, сопоставления и графического представления информации. Разработал Чарльз Шимоньи ( Charles Simonyi) MS Excel позволяет решать разные классы задач, так или иначе связанных с вычислениями и расчетами: вычисление с использованием аппарата встроенных функций и формул; обработка текстовых данных; построение графиков и диаграмм; управление базами данных; получение выборки данных, которые удовлетворяют определенным критериям; статистический анализ данных; решение задач прогнозирования и оптимизации.

Табличные процессоры находят широкое использование в разных отраслях экономики. С помощью стандартных средств этого приложения разрабатываются отчетные документы о финансовом состоянии фирмы, производится оценка ее товарно­ материальных запасов, производятся расчеты технико­ экономических показателей и проводится бизнес­анализ финансовой деятельности предприятия, строятся экономико­ математические модели и решаются много других экономических задач, которые касаются работы современного предприятия. В MS Excel реализована концепция рабочих книг, суть которой заключается в том, что рабочая книга MS Excel представляет собой файл, который содержит от 1 до 255 листов разных типов, основными из которых являются: рабочий лист и лист диаграммы. Концепция рабочих книг позволяет создавать всю деловую информацию, которая принадлежит к одной теме в одной рабочей книге и хранить ее в одном файле с произвольным именем и расширением.XLS.

2. Запуск та структура прикладного окна MS Excel Загрузка программы может быть выполнена разными способами запуска прикладных программ в среде Windows и зависит от настроек, которые установлены на ПК пользователя. Стандартный способ это использование кнопки "Пуск" панели задач: После запуска будет открыто прикладное окно MS Excel и окно документа с именем Книга1(Book1). Это значит, что система готова к созданию нового документа. После загрузки или при создании новой книги MS Excel открывает только рабочие листы и по умjлчанию им присваиваются именем Лист1(Sheet1), Лист2(Sheet2), и т.д., их количество зависит от системных установок, как правило, это 3 листа. Дадим короткую характеристику элементов прикладного окна программы.

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

Главное меню Управление работой MS Excel осуществляется через главное меню, которое состоит из девяти пунктов. Каждый пункт главного меню ­ это своего рода кнопка управления, при нажатии на которую раскрывается подменю. При активизации любого пункта меню в подменю сначала отображаются только наиболее часто используемые команды, а внизу появляется строка с двойной стрелкой, при нажатии на которую раскрывается полное подменю. Некоторые команды подменю заканчиваются черным треугольником, а некоторые ­ многоточием. Черный треугольник значит, что для данной команды существует подменю второго уровня. Многоточие после команды значит, что при выборе этой команды открывается диалоговое окно для установки дополнительных параметров команды. Команды меню по фунциональному назначению упорядочены в группы, между которыми проставлен разделитель - ­ горизонтальная черта.

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

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

Рабочее поле ЭТ Рабочее поле електронной таблицы ­ это прямоугольная матрица, которая состоит из ячеек упорядоченных в строки и столбцы. Ячейка это минимальная информационная единица электронной таблицы, в которую непосредственно вводятся данные для обработки. Электронная таблица Excel может состоять из строк и 256 столбцов. Строки нумеруются целыми числами от 1 до 65536, а столбцы – только буквами латинского алфавита A,B..., Z,AA., AZ,BA.,BZ., IA...,IV. Для указания на конкретную ячейку используется адрес ячейк, кторый состоит из имени столбца и номера строки, на пересечении которых находится ячейка, например А1 или B4. В любую ячейку таблицы заносится единица информации: символ, строка символов, число, формула, встроенная функция, график, рисунок, видео или аудио клип и т.п. В одну ячейку можно ввести около 32 тысяч символов, но рекомендуется чтобы этот размер был не больше 256 символов. Диапазон ячеек - это ­ две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными. Для определения диапазона ячеек используется адрес крайней верхней левой ячейки диапазона и адрес крайней нижней правой ячейки диапазона, разделенных двоеточием. Например, адрес A1:D5 задает диапазон ячеек, размещенных в столбцах от A до D и в сроках от 1 до 5.

Кнопки прокрутки вкладок листов Под рабочим полем электронной таблицы находится четыре кнопки, с помощью которых можно прокручивать перечень вкладок листов. Для активизации листа нужно с помощью кнопок прокрутки отобразить соответствующую вкладку и щелкнуть на ней. Можно переместиться с одного листа на другой, выбрав внизу окна книги соответствующую вкладку. Чтобы облегчить поиск нужных вкладок, их можно пометить разными цветами. Вкладка активного листа подчеркивается выбранным цветом, вкладки неактивных листов закрашиваются полностью. Строка состояния Строка состояния выводит сведения о состоянии рабочей области электронной таблицы при решении конкретной задачи. В левой части строки состояния отображается информация двух типов. Первый тип отображаемой информации это ­ сообщение об успешном или неуспешном завершении того действия, что только что завершилось (ГОТОВО или код ошибки) или указывается операция, которая выполняется (например, открытие или сохранение файлов). Второй тип отбражаемой информации это сообщение­ о том действия, что вы только собрались сделать, например, объяснение действия некоторых из кнопок панели инструментов (появляется в строке состояния при нажатии на кнопку). Правая часть строки содержит Калькулятор и отображает включение режимов Caps Lock, Num Lock, Scroll Lock. Калькулятор включается если выделен диапазон ячеек с числовыми данными, и отображает СУММА=.

3. Объекты табличного процессора MS Excel и управление ими Рабочая книга ­ это файл, который содержит в себе один или несколько рабочих листов одного или нескольких типов. MS Excel допускает создание следующих типов листов рабочей книги: Рабочий лист ­ лист книги (электронная таблица), предназначенный для сохранения и обработки табличных данных. Лист диаграммы - лист книги, который предназначен для анализа данных рабочего листа в виде диаграмм различных типов. Лист модуля - лист книги, который содержит макросы или операторы языки VBA, предназначенные для автоматизации обработки данных ЭТ. Строка ­ горизонтальный блок ячеек, который идентифицируется цифрой. Столбец - вертикальный блок ячеек, который идентифицируется буквой алфавита. Ячейка - прямоугольная область, которая находится на пересечении строки и столбца электронной таблицы и в которую можно вносить и редактировать информацию. Модель ячейки ­ для каждого ячейки сохраняется следующая информация: изображение, формат, формула, имя, комментарии.

Активная ячейка - ячейка, на которую установлен указатель текущей ячейки и с которой будет выполнено следующее действие или команда. Адрес ячейки - буквенно-­цифровая комбинация, которая идентифицирует положение ячейки на рабочем листе по столбцу и строке (А3 или В16). Если ссылаются на ячейку в формуле, то в этом случае адрес ячейки называют ссылкой на ячейку. Диапазон (блок) ячеек - это две и больше ячеек, координаты которых определяются адресами верхнего левого и правого нижнего углов выделенного блока (A15:F27).

4. Операции создания, редактирования и форматирования электронных таблиц 4.1. Ввод и редактирование данных электронной таблицы Excel позволяет обрабатывать следующие типы данных: числовые данные; данные типа Даты и Времени; текстовые данные. Группа специальных типов данных По умолчанию установлены следующие общие правила ввода данных: 1. Ввод данных любого типа выполняется только в активной ячейке. Операция ввода данных в ячейку обязательно должна завершаться нажатием клавиши, или одной из клавиш перемещения курсора. 2. Система автоматически распознает тип введенных данных непосредственно при вводе в ячейку и выполняет их форматирование в соответствии с параметрами установленными по умолчанию: текст выравнивает по левому краю, числовые данные – по правому краю ячейки, дату и время – по правому краю ячейки.

Примеры типов данных

3. При вводе числовых данных Excel допускает использование следующего набора символов: ­ + / Е или е. Символы Е или е используется для отображения чисел в научном (экспоненциальном) формате. При этом не каждая запись с использованием этого символа содержит число, например: запись 12е3 является числом ­ правильная запись, а запись 123е ­ является ошибочной и будет интепретироваться Excel как текст. Символ % используется для отображения чисел в процентном формате. Не каждая запись с этим символом буде правильной, например: запись 12,3% является числом 0.123*100 ­ правильная запись, а запис12%3 или %12,3 ­ ошибочная запись. Символ "," или "." используются в качестве знака, который разделяет целую часть числа от дробной и зависит от того, какой разделительный знак установлен в окне Язык и стандарты, панели управления ОС Windows. 3. Если вводится текст, который не может полностью отобразиться в одной ячейке, Excel отобразит текст в соседней правой ячейке, если она пустая. Если в правой ячейке есть данные, то текст, который перекрывается, будет спрятан.

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

4.2. Форматирование данных электронной таблицы Форматирование таблиц ­ это процесс создания таблиц с высоким уровнем дизайна. Excel позволяет выполнять такие типовые операции форматирования: форматирование чисел и текстов; форматирование цвета палитры и бордюров ячеек; копирование форматов ячеек; создание и редактирование стилей форматирования; применение автоформатирования. Для отображения числовых значений Excel по умолчанию использует числовой формат Общий, при котором значения в ячейах отображаются так, как они введены с клавиатуры. Для изменения числовых форматов необходимо выполнить следующие действия: выделить ячейки и активизировать командуФормат ячейки из меню Формат. Выбрать соответствующую категорию формата и определить числовой формат, установив необходимые опции на вкладке Число. Для создания собственных форматов необходимо в окне Формат ячеек активизировать вкладку Число и в поле Числовые форматы выделить категорию (Все форматы), а в поле Тип: создать собственный шаблон формата или изменить существующий. Для этого допустимые следующие операции:

отображение чисел со значащими цифрами. Применяются символы подстановки # и 0 изменение цвета цифр в соответствии с логическим условием центрирование цифр по ширине шаблона Применяется символ вопроса ? отображение пояснительных символов рядом с числами изменение цвета цифр Символ подстановки 0 задает позицию обязательного отображения цифр в числе. Например, отображение чисел со значащими цифрами: Шаблон число результат 000,00 12,50 12,50 Символ # задает позицию отображения цифры в числе, при этом незначащие цифры отображаться не будут. Например, отображение чисел без значимых цифр: Шаблон число результат ###,## 12,50 12,5 Символ ? используется для центрирования чисел по ширине шаблона. Например, выравнивание числа по десятичной запятой: Шаблон число результат ###,00?? 12,45

Для выделения цифровых значений цветом отличающимся от черного необходимо перед шаблоном формата, в квадратных скобках, написать цвет выбранный из списка: чорный синий циан (голубой) фиолетовый красный белый желтый. Например: положительные значения отобразить фиолетовым цветом, а отрицательные красным: Шаблон фиолетовый ; красный Отображение объяснительных символов рядом с числом - используется, например, для отображения наименования единицы измерения количества товара или стоимости товара и т.д. Например, отобразить единицу измерения количества товара: Шаблон Результат ###шт 12шт ###\ шт 12 шт Изменение цветов цифр в соответствии с логическим условием задается в шаблоне, в котором задано логическое выражение записанное в квадратных скобках. Например, значения данных меньше 100 отображать синим цветом, большие 200 ­ красным, а другие ­ черным: Шаблон синий 100 # ; красный 200 #

Выравнивание содержимого ячеек Режим выравнивания данных задается с помощью соответствующих установок на панели Выравнивание диалогового окна Формат ячеек. Например, для форматирования длинных текстовых данных в одной ячейке можно установить опции перенос по словам и выравнивание "по горизонтали" и "по вертикали". Для этого необходимо выделить ячейку и щелкнуть правой кнопкой мыши для вызова контекстно­зависимого меню, выбрать команду Формат ячеек и на вкладке Выравнивание установити флажок Перенос по словам, а затем соответствующие параметры выравнивания:

Выбор вида шрифта В Excel шрифты могут быть заданы как для всего рабочего листа, так и для блока ячеек. Чтобы изменить шрифт необходимо выделить соответствующий объект, а затем в окне Формат ячеек активизировать вкладку Шрифт. С помощью этой вкладки есть возможность задавать виды, размеры и начертания шрифтов. Размер шрифта задается в пунктах. Одному пункту соответствует размер 0,375 мм. В окне Размер можно задавать промежуточные размеры шрифтов Оформление ячеек таблицы рамкой или линиями Вкладка Граница для создания рамок, из окна диалога Формат ячеек дает возможность создать любой бланк или формуляр. Для создания линий рамки необходимо выполнить следующие действия: 1. Выделить диапазон ячеек, где следует создать рамки. 2. Активизировать контекстно­зависимое меню правой кнопкой мышки, и выполнить команду Формат ячеек. 3. В окне Формат ячеек активизировать вкладку Граница. 4. Определить тип линии, используя образцы опции "Линия" 5. Определить цвет линии, используя поле из списка "Цвет".

6. Определить место расположения линии, используя кнопки области "Все" или "Отдельные". Кнопка Вокруг задает положение линий рамки по внешним краям диапазона ячеек. Кнопка Внутренние задает размещение линий рамки по периметру всех ячеек, которые были выделены, кроме внешнего периметра выделенного блока ячеек. Кнопки из области Отдельные задают расположение отдельных линий в выделенном диапазоне ячеек. 7. После выполнение всех вышеупомянутых действий нажать кнопкуОК. Для ускоренного оформления таблицы линиями можно использовать пиктограмму "Границы" панели инструментов "Форматирование". Для этого выполняются такие действия: 1. Выделить диапазон ячеек, в которых необходимо создать линии. 2. Активизировать пиктограмму "Границы" панели инструментов "Форматирование" 3. Выбрать необходимое место размещения линии. Для исключения рамки или линий в таблице необходимо: 1. Выделить ячейку или диапазон ячеек. 2. Выполнить команду Ячейки из пункта меню "Формат" 3. Активизировать вкладку Граница и с помощью одиночных щелканий по соответствующим кнопкам изъять ненужные линии рамки. Другой способ исключение рамки или линии в таблице это использование кнопки "Границы" панели инструментов "Форматирование" или команды Очистить Форматы" из меню "Правка", но в этом случае вместе с линиями рамки будут удалены все параметры форматирования ячейки.

Защита данных в таблицах 1. Определить ячейки, которые не будут заблокированы для редактирования, т.е. выделить эти ячейки. 2. Вызвать контекстно-зависимое меню и выбрать команду Формат ячеек. 3. Активизировать вкладку Защита и отключить опцию Защищенная ячейка. 4. Установить защиту на весь лист с помощью команды Защита из меню Сервис.

Автоматическое форматирование таблиц Для использования автоформата необходимо: 1. Выделить блок ячеек, который следует форматировать. 2. Активизировать команду Автоформат из меню Формат. 3. Выбрать необходимый формат из предложенного списка и нажать кнопку ОК. Функция автоформат работает следующим образом: Excel анализирует диапазон смежных ячеек, на которой указывает указатель активной Ячейки, и автоматически применяет формат, базируясь на позициях заглавия, формул и данных Форматирование числовых значений ячеек с помощью пиктограмм панели инструментов Форматувание Наиболее удобно выполнять форматирование числовых значений с помощью пиктограмм панели иструментов "Форматирование": Пиктограмма Распределения на группы разрядов используется для деления разрядов чисел на триады за помощью запятых. ПиктограммыУвеличения разрядности и Уменьшение разрядности дают возможность соответственно увеличить или уменьшить количество цифр, которые отображаются после десятичной запятой.