Новости

Основы создания документов в табличном процессоре Microsoft EXCEL

Работа добавлена:






Основы создания документов в табличном процессоре Microsoft EXCEL на http://mirrorref.ru

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«ОРЕНБУРГСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

Кафедра ИТ в экономике

Н.В. Андреева, В.В. Кучерова, А.А. Попов

основы создания документов

в ТАБЛИЧНОМ ПРОЦЕССОРЕMicrosoftEXCEL

Учебно-практическое пособие для студентов экономических специальностей очной формы обучения

Оренбург

Издательский центр

2010

УДК 002

А - 65

Рекомендовано к печати редакционно-издательским советом Оренбургского государственного аграрного университета (председатель совета – профессорВ.В. Каракулев).

Рассмотрено и рекомендовано к печати на заседании методической комиссии экономического факультета (протокол № 2 от 19 октября 2009 г.).

РЕЦЕНЗЕНТЫ:

доктор технических наук, профессор, зав. кафедрой информационных

систем и технологий ОГУВ.И. Чепасов;

кандидат технических наук, доцент, зав. кафедрой информатики и информационного обеспечения ОГАУА.С. Боровский

Андреева, Н.В.

Основы создания документов в табличном процессоре MicrosoftExcel: учебно-практическое пособие для студентов экономических специальностей очной формы обучения / Н.В. Андреева, В.В. Кучерова, А.А. Попов. – Оренбург: Издательский центр ОГАУ, 2010. – 77 с.

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

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

Н.В. Андреева, В.В. Кучерова, А.А. Попов, 2010

Издательский центр ОГАУ, 2010

СОДЕРЖАНИЕ

Введение.4

  1. Структура электронной таблицы.5
  2. Создание и заполнение таблицы постоянными данными и

формулами19

  1. Построение, редактирование и форматирование диаграмм26
  2. Применение стандартных функций в таблице36
  3. Связанные таблицы. Расчет промежуточных итогов в

электронных таблицах45

  1. Подбор параметра. Организация обратного расчета55
  2. Задачи оптимизации (поиск решения)60
  3. Связи между файлами и консолидация данных66
  4. Сводные таблицы73
  5. Комплексное использование приложенийMicrosoftOffice

для создания документов76

Коротко о главном79

Литература81

ВВЕДЕНИЕ

Информационная технология (ИТ) – это процесс, включающий совокупность способов сбора, хранения, обработки и передачи информации на основе применения средств вычислительной техники. В качестве инструментария ИТ выступают такие программные средства, как текстовый процессор, электронные таблицы, системы управления базами данных, издательские системы.

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

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

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

Задача: освоить базовые технологии работы в табличном процессореMicrosoftExcel корпорацииMicrosoft для персональныхIBM-совместимых компьютеров.

Знать: архитектуру современного компьютера и принципы его работы, основные элементы программного обеспечения.

Уметь: создавать и редактировать таблицы, производить расчеты по формулам, представлять данные в виде диаграмм, осуществлять структурирование данных, делать выборку из больших таблиц, создавать консолидированные таблицы.

Владеть: специальной компьютерной терминологией и лексикой, навыками самостоятельного овладения новыми знаниями

Практическая работа1

Тема:Структура электронной таблицы

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

Общие сведения

ЗапускEXCEL можно осуществить одним из следующих способов.

  • В меню «Пуск» выберите пункт «Создать документMicrosoftOffice». В открывшемся окне выберите вкладку «Общие» и дважды щелкните на пиктограмме «Новая книга».
  • В меню «Пуск» выберите пункт «Программы», «MicrosoftExcel».
  • В меню «Пуск» выберите пункт «Выполнить». В открывшемся окне «Запуск программы» в строке «Открыть» наберитеExcel и щелкните по кнопкеOK.
  • Дважды щелкните мышью на пиктограмме «MicrosoftExcel» на рабочем столеWindows.
  • ЗапуститьExcel можно с одновременным открытием файла, с которым недавно работали. В меню «Пуск» выберите пункт «Документы», укажитеимя файла, который нужно открыть.

ПунктДокументы служит для вызова меню, в котором находится 15 имен документов, открывавшихся и редактировавшихся пользователем при работе сWindows.

Первоначальные сведения о табличном процессореExcel

Табличный процессор – прикладная программа, позволяющая автоматизировать труд экономистов, бухгалтеров и др., которым приходится работать с информацией, представленной в виде разнообразных таблиц.

Первым табличным редактором, получившим широкое распространение, сталLotus 1-2-3, разработанный в 1982г. для компьютеров типаIBM. Этот табличный процессор интегрировал в своем составе не только набор обычных инструментов, но и графику, а также возможность работы с системами управления базами данных.

В 1987г. появляется табличный процессорExcel фирмыMicrosoft. Эта программа предложила более простой графический интерфейс в комбинации с ниспадающими меню, значительно расширив при этом функциональные возможности пакета и повысив качество выходной информации. Разработчики максимально облегчили пользователю освоение программы и работу с ней. Благодаря этомуExcel завоевала популярность среди широкого круга пользователей.

Электронная таблица – компьютерный эквивалент обычной таблицы, в клетках (ячейках) которой записаны данные различных типов: тексты, даты, формулы, числа.

Главное достоинство электронной таблицы – это возможность мгновенного пересчета всех данных, связанных формульными зависимостями при изменении значения любого операнда.

Электронные таблицы используются для:

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

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

Файлы, хранящие электронные таблицы, имеют расширениеxls. Один файл может хранить многотабличнуюкнигу, содержащую несколько листов-таблиц, а также листов-диаграмм.

Структура электронной таблицы

Строка заголовка содержит название документа и кнопки управления окном.

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

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

Строка формул предназначена для отражения вводимых в текущую ячейку данных. Адрес текущей ячейки отражен в левой части строки (отдельное окошко). В этой строке можно просмотреть и отредактировать хранимую в ячейке формулу; в самой ячейке пользователь при этом может видеть результат вычисления по формуле.

Рабочее поле содержит главную часть электронной таблицы – ячейки.

Табличный курсор выделяет текущую ячейку. ВMSExcel максимальный номер строки равен 65536, а последний столбец имеет имяIV (всего 256 столбцов).

Строка состояния отражает текущий режим работы табличного процессора. Здесь же выводятся сообщения пользователю о возможных действиях при данном состоянии таблицы. Например, когдаEXCEL ожидает ввода данных, то находится в режиме «ГОТОВО» и индикатор режима показываетГОТОВО.

Вертикальная и горизонтальная полосы прокрутки предназначены для перемещения окна по электронной таблице.

Рисунок 1.1 Интерфейс стандартной рабочей книги с листами

Настройка экрана. Работа с меню

Чтобы войти в меню, необходимо нажать клавишу <Alt> или <F10>. После этого одно слово в меню будет выделено подсветкой. При помощи клавиш <> и <> выбирается нужный пункт в меню. При нажатии клавиши <Enter> раскрывается подменю. Перемещение по подменю осуществляется клавишами <> и <>, а выбор команды фиксируется нажатием <Enter>. Выход из меню осуществляется нажатием клавиши <Esc>.

С менюExcel удобно работать при помощи мыши. Выбрав необходимый пункт, нужно навести на него курсор и сделать щелчок по левой кнопке мыши.

Щелчком по левой клавише мыши выбираются необходимые команды подменю и раскрываются вкладки, а также устанавливаются флажки.

Щелчок мыши за пределами меню приводят к выходу из него и закрытию подменю.

Выполните следующие действия.

1. Щелкните по кнопкеРазвернутьв зоне заголовка, если после вызова программы ее окно не занимает всего экрана. Окно документа также должно быть полноэкранным.

2.В менюВидвыберите командуПанели инструментов. В раскрывшемся диалоговом окне установите флажки рядом с названиями панелей инструментовСтандартная иФорматирование, если они отсутствуют.

Установка и снятие флажка производится щелчком мыши в соответствующем окне.

Для вывода панелей инструментов вы можете также использовать контекстно-зависимое меню, которое появляется после щелчка правой кнопкой мыши в зоне панелей инструментов.

3. В менюСервис выберите командуПараметры.

  • Раскройте вкладкуВид. Проверьте, установлены ли флажки следующих параметров:

в полеОтображать: строку формул; строку состояния;

в полеПараметры окна: горизонтальная полоса прокрутки; вертикальная полоса прокрутки; ярлычки листов; заголовки строк и столбцов; сетка.

Если нет, то произведите соответствующую установку.

Замечание: установка флажка на строкуФормулы обозначает режим отображения формул (т.е. вместо результата в таблице можно видеть введенную формулу).

  • Раскройте вкладкуОбщие. Установите следующие параметры:

стиль ссылок: А1, т.е. нет флажка.

Далее в тексте подобные действия по работе с меню будут описываться в краткой форме:

Меню/Сервис/Параметры/Вид/Строка формул.

Основные понятия электронных таблиц. Строки, столбцы, ячейки

Рабочее поле электронной таблицы состоит из строк и столбцов. Максимальное количество строк равно 65536, столбцов – 256.

Каждое пересечение строки и столбца образуют ячейку, в которую можно вводить данные (текст, число или формулы).

Номер строки определяет ряд в электронной таблице. Он обозначен на левой границе рабочего поля.

Буквы столбца определяют колонку в электронной таблице. Буквы находятся на верхней границе рабочего поля. Имена столбцов обозначаются в следующем порядке:AZ, затемAAAZ, затемBABZ и т.д.

Ячейка – первичный элемент таблицы, содержащий данные. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки. Например, адрес В3 определяет ячейку на пересеченииСтолбца В иСтроки номера 3.

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

Текущая ячейка помечена указателем. Ввод данных и некоторые другие действия по умолчанию относятся к текущей ячейке.

Блок (область) ячеек

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

Адрес блока состоит из координат противоположных углов, разделенных двоеточием, например,B13:C19,A12:D27.

Блок можно задать при выполнении различных команд или вводе формул посредством указания координат или выделения на экране.

Рабочий лист, книга

Окно с заголовком Книга1 состоит из нескольких рабочих листов (по умолчанию таких листов 3). При открытии рабочей книги автоматически загружаются все ее рабочие листы. На экране виден только один лист – верхний. Нижняя часть листа содержит ярлычки других листов. Щелкая мышью на ярлычках листов, можно перейти к другому листу.

Выделение столбцов, строк, блоков, таблицы

Для выделения с помощью мыши:

столбца – щелкнуть мышью на букве – имени столбца;

нескольких столбцов – не отпуская кнопку после щелчка, протянуть мышь;

строки – щелкнуть мышью на числе – номере строки;

несколько строк – не отпуская кнопку после щелчка, протянуть мышь;

блока – щелкнуть мышью на начальной ячейке блока и, не отпуская кнопку, протянуть мышь на последнюю ячейку;

рабочего листа – щелкнуть мышью на пересечении имен столбцов и строк (левый верхний угол таблицы).

Для выделения блока с помощью клавиатуры, необходимо, удерживая клавишу <Shift>, нажимать на соответствующие клавиши перемещения курсора или, нажав клавишу <F8>, войти в режим выделения и произвести выделение при помощи клавиш перемещения курсора.

<Esc> выход из режима выделения.

Для выделения нескольких несмежных блоков необходимо:

  • выделить первую ячейку или блок ячеек;
  • нажать и удержать клавишу <Ctrl>;
  • выделить следующую ячейку или блок и т.д.;
  • отпустить клавишу <Ctrl>.

Для снятия выделения достаточно щелкнуть мышью по любому невыделенному участку рабочего листа. Новое выделение снимает предыдущее.

Выполните следующие действия.

1. Сделайте ячейкуD4 текущей при помощи мыши.

2. Вернитесь в ячейку А1 при помощи клавиш перемещения курсора.

3. Сделайте текущим лист 3.

4. Вернитесь к листу 1.

5. Выделите строку 3.

6. Снимите выделение.

7. Выделите столбецD.

8. Выделите блокA2:E13 при помощи мыши.

9. Выделите столбцыA,B,C,D.

10. Снимите выделение.

11. Выделите блокC4:F13 при помощи клавиатуры.

12. Выделите рабочий лист.

13. Снимите выделение.

14. Выделите одновременно следующие блоки:F5:G10,H15:I15,C18:F20,H20.

Данные в ячейках таблицы

В работе с электронными таблицами можно выделить три основных типа данных:текст, числоиформула.С числом и текстом всё понятно: помещаете курсор в ячейку и вводите их с клавиатуры. Число, в отличие от текста, может участвовать в вычислительных операциях. Так, например, можно число 6 умножить на число 3 и получить результат вычислений. Но если вы попробуете из текста «Начисления» вычесть текст «Налоги», то получите сообщение об ошибке.

Числа разделяются на целые и вещественные. Вещественные числа можно записать двумя способами: в форме с фиксированной запятой и в экспоненциальной форме (в форме с плавающей запятой). Числовая константа в экспоненциальной форме трактуется как мантисса, умноженная на 10 в степени, равной порядку. Например, число 1000000 можно записать как 1Е+6, 0,0001 – 1Е-4

ЧисловMicrosoftExcel может состоять только из следующих символов: 0 123456789 + -(). Все другие комбинации, состоящие из цифр и нецифровых символов, интерпретируются как текст. Если перед числом стоит знак плюс (+), он игнорируется. Перед отрицательным числом необходимо ввести знак минус (-) или заключить его в круглые скобки (). В качестве десятичного разделителя используют запятую.

По умолчанию числа выравниваются в ячейке по правому краю. Это объясняется тем, что при размещении чисел друг под другом (в столбце таблицы) удобно иметь выравнивание по разрядам (единицы под единицами, десятки под десятками и т.д.).

ТекстомвMicrosoftExcel является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например, приведённые ниже записи обрабатываются как текст: 987$$78, 100 рублей.

По умолчанию текст выравнивается в ячейке по левому краю. Это объясняется традиционным способом письма (слева направо).

Формулы.Подформулойв электронной таблице понимают выражение, состоящее из операндов и операций. Формулы строятся как выражение для вычисления нового значения. Тип значения, полученного в результате вычисления по формуле, определяется типом операндов выражения. Формула всегда начинается с символа равно (=).

В качествеоперандовиспользуются:

  • числа;
    • тексты (вводятся в двойных кавычках, например, «Неявка»);
    • логические значения (например, ИСТИНА и ЛОЖЬ, условия типа А23=А45 и т.д.);
    • значения ошибки (типа #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!);
    • ссылки — адреса ячеек. При перечислении ссылки разделяются точкой с запятой, например:А4; С5; С10: Е20;
    • встроенные функцииExcel.

Операнды в формулах соединяются с помощью символовопераций:

  • арифметических операций: + (сложение), — (вычитание), / (деление), * (умножение), ^(возведение в степень);
  • операций отношения: > (больше),>=(не меньше), < (меньше), <= (не больше), = (равно), <> (не равно).

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

  • относительныессылки, напримерА2илиС23,которые всегда изменяются так, чтобы отобразить правило их вхождения в формулу относительно ее нового местоположения;
  • абсолютныессылки, которые перед именем столбца и номером строки имеют символ