Создание и редактирование входных/выходных форм документов с использованием табличного процессора excel

Лабораторная работа № 7

Тема: Создание и редактирование входных/выходных форм документов с использованием табличного процессора EXCEL.

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

Продолжительность - 4 часа.

Список литературы

Обязательная литература

Иванов В. Microsoft Office System 2003 Учебный курс. – СПб.: Питер; Киев: Издательская группа ВНV, 2004. – 640с.: ил..

Дополнительная литература

Windows 98/ME/2000/{H/ Книга + Видеокурс.: Учебное пособие. – М.: Лучшие книги. 2003. – 400с.
Ульрих Л.А. Электронные таблицы Microsoft Excel. Проблемы и решения: Практич. пособие / Пер. с англ. – Издательство ЭКОМ, 2002, 400 с.
Виллетт Эдвар, Кроулер Дэвид, Кроулер Ронда. Microsoft Office 2000 / Библия пользователя. Пер. с англ. – М.: Издательский дом «Вильямс». 2001. – 1026 с.
Левин А. Самоучитель работы на компьютере. Начинаем с Windows. 3-е изд. – СПб.: Питер, 2005. - 718с., ил.

Содержание теоретического минимума.

Рабочее окно, рабочие книги и рабочие листы.
Ввод данных.
Редактирование данных.
Форматирование данных.
Создание новой таблицы. Обрамление таблицы.
Выделение ячеек, их диапазонов, строк и столбцов.
Ввод данных через формы.
Копирование данных или формул в смежные ячейки.
Обработка числовых значений.

Создание формул;
Функции в Microsoft Excel.

Ссылки на ячейки. Имена ячеек.
Условное форматирование.
Рабочие листы Microsoft Excel.
Подготовка документа к печати.

Краткие теоретические сведения.

Рабочие книги и рабочие листы Microsoft Excel.

Чтобы начать работать в Microsoft Excel нажмите кнопку Пуск → Программы → Программы Microsoft Office → Microsoft Excel. При запуске программы Excel открывается окно, содержащее новую рабочую книгу. Рабочая книга – это многостраничный документ Excel, каждая страница которого называется рабочим листом. В окне документа отображается активная страница. Пользователь может вставить в книгу дополнительные листы (максимально – 255).

Рабочее окно Excel (рис. 2) содержит стандартную строку заголовка, меню и панели инструментов. Под ними расположена панель, в составе которой имеются поле имени и строка формул.

Рис. 2

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

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

Каждая ячейка имеет свой адрес, он определяется именем столбца и номером строки, которым принадлежит ячейка (например А1, В6 и др.). Активная ячейка высвечивается в поле имени и она окружена рамкой, которая называется рамкой активной ячейки, а заголовки столбца и строки этой ячейки подсвечиваются. В каждый момент времени активной является только одна ячейка, и данные можно вводить лишь в неё.

При работе в Excel вид указателя мыши постоянно меняется при его перемещении по рабочему листу и зависит от режима работы программы и доступных команд.

Когда указатель мыши имеет вид «контурного» толстого креста , его можно использовать для выделения ячеек.

В правом нижнем углу рамки активной ячейки указатель мыши принимает вид тонкого чёрного креста . Он указывает на возможность выполнения операции заполнения смежных ячеек.

При установке указателя на границе активной ячейки появляется четырёхнаправленная стрелка . Этот указатель используется для перемещения содержимого ячейки в другое место.

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

Ввод данных.

В ячейки рабочего листа могут быть введены данные трёх типов:

- числа (к ним относятся также значения даты и времени), представляющие собой величины, используемые в вычислениях;

- формулы, которые являются инструкциями, вводимыми в ячейки, в соответствии с которыми производятся вычисления;

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

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

Если при вводе произошла ошибка, щёлкните в строке формул на кнопке Cancel (Отмена) с изображением крестика красного цвета . Или нажмите клавишу Esc на клавиатуре.

Для того чтобы закончить ввод данных нажмите кнопку Enter (Ввод) – изображена в виде галочки зелёного цвета в строке формул или клавишу Enter на клавиатуре для завершения ввода или перехода к другой ячейке.

Редактирование данных.

Существуют два способа изменения содержимого ячейки.

При вводе в выбранную ячейку нового значения старая информация заменяется вводимой.

Если необходимо очистить содержимое ячейки: выделите ячейку и нажмите клавишу Delete или щёлкните на ячейке правой кнопкой мыши и в открывшемся контекстном меню выберете пункт «Очистить содержимое».

! Не вызывайте команду Delete (Удалить), поскольку при этом ячейки удаляются с листа, а соседние ячейки сдвигаются и занимают их место.

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

Форматирование данных.

Все действия по форматированию можно выполнить, пользуясь вкладками Число, Шрифт, Выравнивание, Вид и Граница в Меню → Формат → Ячейки.

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

Для изменения типа вводимых в ячейку данных используется команда Меню → Формат → Ячейки → вкладка Число, где изменяются числовые форматы активной ячейки. Это же можно сделать с помощью кнопок панели инструментов форматирования.

Чаще всего использующиеся операции форматирования выведены в виде кнопок на панель инструментов Форматирование: .

Наибольший интерес представляют кнопки:

- кнопка «Объединить и поместить в центре». Она используется для создания заголовков путём объединения нескольких ячеек в одну.

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

- кнопка «формат с разделителем» преобразует большие числа к наиболее удобному для восприятия виду. Например: 300000 преобразуется к 300 000, при этом меняется только вид числа, а не само число.

- кнопки «Увеличить/Уменьшить разрядность» числа. При уменьшении разрядности в отображаемом числе уменьшается количество значащих цифр после десятичной запятой. Однако не следует путать понятия «форматирование» и «округление». Когда число отформатировано, оно только кажется округлённым. При создании ссылки на ячейку с отформатированным числом оно будет применяться в вычислениях в исходном виде, то есть со всеми знаками после запятой.

Создание новой таблицы.

Для создания новой таблицы нужно:

Ввести сначала названия столбцов в выбранную в качестве начала таблицы новую строку (шапка таблицы).
Определиться с выравниванием названий в шапке таблицы (Меню → Формат → Ячейки → вкладка Выравнивание).
Аналогичные действия проделать со строками.
Заполнить таблицу.
Создать обрамление таблицы (этот пункт смотри ниже).

Обрамление таблицы.

Excel позволяет затенить некоторые ячейки или заключить их в рамку. Можно рисовать произвольные комбинации горизонтальных и вертикальных линий слева, справа, вверху или внизу ячейки, а также задавать двойное подчеркивание и пунктирное. Эти операции выполняются в режиме Меню → Формат → Ячейки → вкладка Граница или с помощью кнопки Границы панели инструментов Форматирование

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

Выделение ячеек, их диапазонов, строк и столбцов.

Операция выделения выполняется для обозначения ячеек, к которым должна относиться следующая команда или действие. В Excel всегда выделена рамкой по крайней мере одна – активная ячейка. Группа выделенных ячеек называется – диапазоном. Для выделения диапазона ячеек переместите указатель в первую ячейку диапазона и, удерживая кнопку мыши, перетащите его в нужном направлении до последней ячейки, которую необходимо включить в диапазон, затем отпустите кнопку. Или с помощью клавиатуры: поместите указатель ячейки в начало выделяемой области, нажмите клавишу Shift, а затем, удерживая её, расширите выделенный диапазон, используя клавиши управления курсором.

Для выделения одного столбца (строки) достаточно щёлкнуть на его заголовке.

Объединение и пересечение – особые виды диапазонов ячеек.

Объединение – это область, содержащая все ячейки, принадлежащие нескольким диапазонам.

Например: при вычислении суммы чисел, находящихся в ячейках С2:С8 и С20:С28, в аргументе функции следует записать выражение С2:С8;С20:С28. Точка с запятой в данном случае используется для обозначения объединения всех ячеек в обоих диапазонах.

Пересечение ячеек – это область, которая содержит ячейки, принадлежащие одновременно нескольким диапазонам. Для обозначения пересечения вместо точки с запятой используется пробел.

Например: пересечение двух диапазонов, задаваемое как С2:С10 А10:J20, содержит только одну ячейку – С10.

Копирование данных или формул в смежные ячейки.

Чтобы скопировать данные или формулу можно воспользоваться командами копирования вставки в Меню → Правка → Вставить или Специальная вставка. Используя пункт Специальная вставка можно скопировать только значения, только формулы или только форматы копируемых ячеек или др.

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

Ввод данных через формы.

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

С помощью формы данных можно просматривать в каждый момент времени только одну полную запись таблицы (строку). Форма данных автоматически создается на основе имен столбцов. Чтобы вывести форму на экран необходимо выделить любую ячейку списка (таблицы) и выбрать команду Меню → Данные → Форма.

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

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

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

Обработка числовых значений.

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

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

Таблица арифметических и логических операций.

Операция	Символ
Сложение	+
Вычитание	-
Умножение	*
Деление	/
Возведение в степень	^
Равно	=
Не равно	<>
Больше	>
Меньше	<
Амперсанд	&

Создание формул.

Для того чтобы в формуле использовать значение, находящееся в какой-либо ячейке, необходимо сослаться на эту ячейку (виды ссылок смотри ниже), указав её адрес. Это можно сделать вручную, записав в формуле адрес ячейки (например: А5, С23), или с помощью способа создания формул - наведи и щёлкни (point-and-click), которым знаменита программа Excel. Этот приём является традиционным для всех программ управления электронными таблицами.

Для создания формулы методом наведи и щёлкни:

Выберите ячейку, в которую необходимо поместить результат;
Введите знак равенства (=) в данную ячейку;
Щёлкните на первой ячейке, содержимое которой необходимо использовать в формуле;
Введите символ операции;
Щёлкните на следующей ячейке формулы;
Повторите шаги 4) и 5), пока вся формула не будет введена;
Завершите ввод формулы нажатием клавиши Enter или щелчком на кнопке Enter (Ввод) в строке формул.

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

Порядок выполнения действий – это набор правил, в соответствии с которыми производятся вычисления в формуле:

Вычисления в формуле производятся слева на право;
Операции умножения и деления имеют более высокий приоритет и всегда выполняются раньше операций сложения и вычитания. Например: 15/3+2 равно 7, а не 3;
Любая последовательность операций в скобках выполняется в первую очередь.

Функции в Microsoft Excel.

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

Функция – это программа, которая выполняет определённые операции или вычисляет некоторую величину. Например: итоговое значение, среднее значение, среднее геометрическое группы чисел и др.

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

Чтобы использовать функцию, нужно ввести ее как часть формулы в ячейку рабочего листа. Последовательность, в которой должны располагаться используемые в формуле символы, называется синтаксисом функции. Все функции используют одинаковые основные правила синтаксиса. Если нарушены правила синтаксиса, Microsoft Excel выдаст сообщение о том, что в формуле имеется ошибка. Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства, как и во всякой другой формуле.

Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Помните о том, что нужны обе скобки, и при этом не следует вставлять пробелов ни перед ними, ни после них. Внутри скобок должны располагаться аргументы. В качестве аргументов можно использовать числа, текст, логические значения, диапазоны ячеек или ссылки. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Некоторые функции могут иметь необязательные аргументы, которые могут отсутствовать при вычислении значения функции.

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

Все функции объединены в несколько категорий, вот некоторые из них:

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

Финансовые – вычисление процентной ставки, ежемесячных и амортизационных отчислений и др.;

Дата и время – отображение текущего времени, дня недели, обработка значений даты и времени и др.;

Статистические – вычисление среднего арифметического, дисперсии, среднеквадратического отклонения, наибольшего и наименьшего чисел выборки, коэффициентов корреляции и др.;

Логические – обработка логических значений.

Для ввода функций:

Выберите ячейку, в которую нужно поместить возвращаемый функцией результат;
Нажмите на клавиатуре символ = (равно);
Выберите функцию в левой части строки формул, в раскрывающемся списке функций. (В списке представлены последние 10 используемых функций.)

Если нужной функции в списке нет, отметьте в нём элемент «Другие функции» или щёлкните на кнопке «Вставка функции» в строке формул или Меню → Вставка → Функция. В результате откроется диалоговое окно «Мастер функций». Укажите в верхнем списке нужную категорию, а в нижнем – функцию. Щёлкните на кнопке ОК, чтобы вернуться к строке формул.

В открывшемся диалоговом окне «Аргументы функции» установите курсор в поле первого аргумента (Число1). Введите аргумент функции.

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

Если окно аргументов закрывает ячейки, которые нужно выделить (ввести), щёлкните на кнопке сворачивания , расположенной в правой части окна аргумента (или поля ввода). В результате окно уменьшится до размера поля ввода и будет возможность выделить нужный диапазон ячеек. Для восстановления прежних размеров окна ввода аргументов щёлкните по кнопке развёртывания .

При необходимости заполните поле второго (Число 2) и последующих аргументов функции.
После ввода всех аргументов, щёлкните по кнопке ОК, чтобы завершить ввод функции.

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

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

Например: =А5*Лист3!В9 – ячейка А5 текущего листа умножается на ячейку В9, расположенную на Листе 3.

=H7+’С:\Отдел продаж\[прейскурант.xls]Лист1’!D4 - к ячейке H7 текущего листа прибавляется значение, находящееся в ячейке D4 на Листе1 файла прейскурант.xls, расположенного на диске С в папке Отдел продаж.

Ссылки на ячейки.

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

Относительной – задающей фактическое смещение ячейки, на которую производится ссылка, относительно ячейки, в которой эта ссылка указывается. Они задаются по умолчанию. И при копировании формулы адрес ячейки, на которую производится ссылка, изменяется так, что смещение остаётся прежним.
Абсолютной – ссылка указывает на конкретную ячейку. При перемещении или копировании формулы такая ссылка не изменяется. Признаком абсолютной ссылки является наличие двух знаков доллара ($) – перед именем столбца и перед номером строки. Например: $F$7.
Смешанной – ссылка, указывающая на конкретный столбец/строку. В таких ссылках содержится только один знак доллара. Например: $F7 – в данном случае имеется абсолютная ссылка на столбец F и относительная ссылка на строку 7.

Имена ячеек.

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

Имя должно давать представление о данных диапазона;
Содержать не более 255 символов;
Необходимо, чтобы имя начиналось с буквы либо с символа подчёркивания;
Нельзя использовать пробел, точку с запятой, восклицательный знак или специальные символы;
Имена не должны совпадать с адресами ячеек;
В именах не учитывается регистр символов клавиатуры;
Имя не должно повторяться внутри рабочей книги;
Нельзя использовать одно и то же имя на двух листах одной рабочей книги для обозначения разных диапазонов.

Для присвоения имени ячейке или диапазону ячеек:

Выделите ячейку или диапазон, который может содержать и не смежные ячейки;
Щёлкните в поле имени строки формул и введите имя диапазона. Нажмите клавишу Enter .

Либо активизируйте команду Меню → Вставка → Имя → Присвоить, а затем в поле «Имя» введите имя и щёлкните на кнопке «Добавить».

Изменение и удаление имён:

Выберите команду Меню → Вставка → Имя → Присвоить. В диалоговом окне «Присвоение имени» появится список имён, которые уже использовались в данной рабочей книге.
Чтобы изменить имя, выделите его в списке, введите новое имя и щёлкните на кнопке «Добавить».
Для удаления имени выделите его в списке и щёлкните на кнопке «Удалить».
После внесения изменений щёлкните на кнопке «ОК».

Условное форматирование.

Условное форматирование – это способ форматирования ячеек с учётом результатов проверки их содержимого. Excel автоматически применяет к ячейкам заданные параметры форматирования, если выполняется некоторое условие. Условие – это логическое выражение, которое может принимать значение True (Истина) или False (Ложь).

Для применения условного форматирования ячеек:

Выделите диапазон ячеек, подлежащих форматированию;
Выберите команду Меню → Формат → Условное форматирование;
В диалоговом окне «Условное форматирование» выберите элемент «Значение» или «Формула» и укажите условный оператор, необходимый для сравнения содержимого ячеек;
В поле ввода условия введите значение, ссылку на ячейку или формулу;
Щёлкните на кнопке «Формат», указав в открывшемся окне параметры форматирования, соответствующие заданному условию. Щёлкните на кнопке ОК;
Чтобы задать дополнительные условия форматирования или указать другие параметры форматирования, щёлкните на кнопке «А также» и повторите действия пунктов 3-5 для каждого условия.

В окне условного форматирования задаётся не более трёх условий;

Щёлкните на кнопке ОК для закрытия диалогового окна и применения параметров условного форматирования к выделенным ячейкам.

Внимание! Условное форматирование применяется только для ячеек одного рабочего листа. Сравнение с ячейками другого рабочего листа невозможно.

Рабочие листы Microsoft Excel.

При открытии рабочей книги Excel становятся доступны три рабочих листа. Имена им даны по умолчанию. Однако их можно переименовать: а) путём двойного нажатия левой кнопкой мыши на названии листа и ввода нового имени, б) использовав команду Меню → Формат → Лист → Переименовать, в) с помощью контекстного меню.

Имя листа должно содержать не более 31 символа и не может совпадать с именем другого листа в той же рабочей книге. В имени не допускаются следующие символы: двоеточие (:), знак вопроса (?), косая черта (\), звёздочка (*), квадратные скобки ([]).

Количество рабочих листов может быть увеличена до 255 шт. Для этого необходимо использовать команду Меню → Вставка → Лист.

Для удаления ненужных листов используют Меню → Правка → Удалить лист.

Excel также позволяет окрашивать ярлычки листа в различные цвета. Для этого:

Щёлкните по ярлычку листа, который нужно сделать цветным;
Выполните команду Меню → Формат → Лист → Цвет ярлычка;
В палитре цветов диалогового окна «Выбор цвета ярлычка» выберите необходимый цвет;
Нажмите кнопку ОК.

Подготовка документа к печати.

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

с помощью кнопки «Предварительный просмотр» на стандартной панели инструментов;
Меню → Файл → Предварительный просмотр;
Меню → Файл → Параметры страницы→Просмотр.

После щелчка на кнопке

«Предварительный просмотр» открывается окно просмотра, в котором содержимое рабочего листа (текст, числовые значения, таблицы, рисунки и т.д.) отображается так же, как оно будет выглядеть после печати. В данном режиме на экране всегда отображается одна печатная страница. Если содержимое рабочего листа не помещается на одной странице, то для просмотра других страниц будут активизированы кнопки «Далее» и «Назад».

В режиме «Предварительного просмотра» при активизации кнопки «Страница»:

Во вкладке «Страница» можно изменить ориентацию страницы, установить масштаб печатаемого документа (крупнее или мельче), установить количество страниц, на которых необходимо разместить печатаемый документ, при этом масштаб документа Excel подберёт автоматически и др.
Вкладка «Поля» позволяет изменить размеры полей. (Это же можно сделать вручную, активизировав кнопку «Поля» данного режима.)
Вкладка «Колонтитулы» позволяет выбрать их из уже имеющихся или создать новые.
Вкладка «Лист» позволяет изменять некоторые параметры листа. В частности, имеется возможность выставления и снятия сетки и печати заголовков строк и столбцов (т.е. нумерации строк и названий столбцов).

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

Установить параметры страницы возможно и через Меню → Файл → Параметры страницы. В этом разделе доступны все функции, что и при режиме «Предварительного просмотра» при активизации кнопки «Страница». Однако преимуществом его является то, что в данном разделе на вкладке «Лист» возможно выделение для печати не всего листа, а только его диапазона и возможно проставление для печати на каждой странице сквозных строк и столбцов.

Задание на лабораторную работу.

Ознакомьтесь с теоретическим минимумом (описанным выше) для выполнения данной лабораторной работы.
Откройте папку «МОИ ДОКУМЕНТЫ».
Создайте папку «ВАША ФАМИЛИЯ» в папке «МОИ ДОКУМЕНТЫ».
Создайте папку «ЛАБ_РАБ_1» внутри папки «ВАША ФАМИЛИЯ».
Запустите программу Microsoft Excel.

Часть I.

Сохраните данный файл как «Фамилия_Лаб.раб.1» в папке «ЛАБ_РАБ_1».
Подумайте и выберите товар, для которого Вы будете составлять таблицу. Он должен иметь следующие составляющие:

Разновидность товара: 4-5 видов;
Расфасовка товара: 4-5 видов;
Производители: 3-5 шт.;
Квартал или отдельные 3 месяца, по которым будет производиться сравнение.

В группе среди студентов виды товара не должны повторяться.

Создайте электронную таблицу, которая содержит столбцы с именами:

№ п/п;
товар;
производитель;
расфасовка;
первый месяц (например: январь);
второй месяц;
третий месяц;
столбец с наименованием: «Итого количество»;
столбец с наименованием: «Итого цена».

Отформатируйте (выровняйте) шапку таблицы таким образом:

выравнивание ячеек строки: по вертикали по центру;

по горизонтали по центру;

текст в ячейке переносить по словам;
выравнивание текста по контексту.

Заполните таблицу, не внося данные в два последних столбца. Таблица должна содержать не менее 50 строк.
Отформатируйте таблицу (В том числе создайте обрамление таблицы).
Вставьте пустую строку перед таблицей.
В ячейку А1 введите заголовок таблицы: «Таблица продаж (вид Вашего товара) за (номер квартала) квартал».
Отцентрируйте заголовок таблицы относительно шапки таблицы.
Покажите результат преподавателю.

Часть II.

Заполните столбец «Итого количество» таблицы, введя в ячейки формулу, суммирующую данные продаж по трём месяцам.
Справа от таблицы создайте новую таблицу № 2 со столбцами: расфасовка и цена.
Заполните таблицу № 2, введя цену за единицу расфасованного товара.
Заполните последний столбец таблицы продаж «Итого цена», используя столбец «Итого количество» и таблицу № 2 следующим образом:

при заполнении столбца ввести для первого вида цены расфасовки – ссылку на ячейку в таблице 2;
для второй цены расфасовки в таблице 2 изменить имя ячейки;
для ввода третьего и четвёртого вида цены расфасовки воспользуйтесь логической функцией ЕСЛИ;
при существовании пятого вида расфасовки итоговая цена за проданный товар вычисляется способом, выбираемым студентом самостоятельно.

Под таблицей № 2 создайте новую таблицу, содержащую столбцы: Расчёты, месяца (три месяца, используемые в таблице продаж).

Столбец «Расчёты» должен содержать следующие строки:

Общее за месяц;
Среднее за месяц;
Минимум за месяц;
Максимум за месяц;
% продаж за месяц от общего объёма продаж.

Заполните таблицу № 3, используя данные таблицы продаж и функции Microsoft Excel.
Под таблицей № 3 создайте таблицу № 4, содержащую столбцы: Производитель, Расфасовка, № п/п.
Используя для поиска записей «Форму», заполните таблицу таким образом:

выберите одного производителя;
выберите один вид расфасовки;
для этих критериев найдите №№ п/п в таблице продаж и вставьте их в таблицу через тире.

Примените условное форматирование к ячейкам столбцов таблицы данных по месяцам таким образом:

Для первого месяца выделить цветом те ячейки, в которых стоят значения меньше среднего количества продаж;
Для второго месяца выделить цветом границу ячеек и изменить вид шрифта на курсив у тех данных, в которых стоят значения меньше или равные значению min+10;
Для третьего месяца выделить цветным шрифтом ячейки, в которых стоят значения большие или равные значению max-10.

Покажите результат преподавателю.
Переименуйте свой рабочий Лист1 в «Исходная таблица».
Перенесите таблицы 2-4 на Лист2. (Естественно, убрав таблицы с листа «Исходная таблица»).
Восстановите формулы в исходной таблице продаж и в таблице 3.
Окрасьте ярлычок Листа 2 в другой цвет.
Покажите результат преподавателю.
Подготовьте таблицу к печати таким образом, чтобы:

Таблица (имеется ввиду таблица продаж) уместилась на 1 листе;
При печати было видно только обрамление таблицы, а не сетка листа;
Печатался верхний колонтитул с Вашей фамилией и номером группы;
Поля были следующими: верхнее – 1,0; верхнего колонтитула – 0,3; левое поле – 1,2.

Покажите результат преподавателю.

Внимание!

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

Выключите компьютер, приведите в порядок Ваше рабочее место.

Контрольные вопросы.

Перечислите категории вводимых в ячейку данных.
Как производится фиксация введенных данных в ячейке?
Что такое "Автоформат" и как им пользоваться?
Что называется адресом ячейки? Полный адрес ячейки?
Приведите примеры объединения диапазонов ячеек.
Приведите примеры пересечения диапазонов ячеек.
Как производится копирование и вставка данных в ячейки?
Что такое "Формы"?
Как осуществлять ввод данных с помощью «Форм»?
Как произвести форматирование чисел в ячейках?
Как можно уместить текстовую информацию в ячейке, не увеличивая ширину столбца?
Как осуществляется работа с формулами в EXEL?
Особенности способа «наведи и щёлкни»?
Что такое вложенные функции?
Как выделить несколько ячеек, не соединенных в один блок?
Как сделать обрамление ячеек и таблицы в целом?
Какие существуют виды ссылок?
Приведите пример смешанной, абсолютной ссылки.
Как присвоить ячейке или группе ячеек имя?
Как проставить для печати документа сквозные строки и столбцы?
Как уменьшить/ увеличить масштаб печатаемого документа?
Что такое условное форматирование данных?
При условном форматировании ячеек какие значения может принимать условие?
Как изменить имя рабочего листа?
Сколько новый документ содержит листов при открытии Excel?
Как вставить новый лист в рабочую книгу Excel и каково их максимальное количество?
Какие символы не допускаются в имени рабочего листа Excel?
Можно ли окрасить ярлычок рабочего листа в другой цвет?

Приложение № 1. – Основная таблица.

Таблица продаж сока за 1 квартал.
№ п/п	Товар (сок)	Производитель	Расфасовка	Янв.	Фев.	Март	Итого кол-во	Итого цена
1	Апельсиновый	"Добрый"	0,2	70	89	27	186	1711,2
2	Апельсиновый	"Добрый"	0,5	90	57	29	176	2956,8
3	Апельсиновый	"Добрый"	1,0	115	93	96	304	8907,2
4	Апельсиновый	"Добрый"	2,0	37	68	43	148	6806,52
5	Апельсиновый	"Фруктовый сад"	0,2	68	89	112	269	2474,8
6	Апельсиновый	"Фруктовый сад"	0,5	62	54	52	168	2822,4
	---	---	---	---	---	---
	---	---	---	---	---	---
55	Яблочный	"J7"	1,0	93	67	54	214	6270,2
56	Яблочный	"J7"	2,0	45	68	78	191	8784,09

Приложение № 2. - Вспомогательные таблицы.

Таблица № 2.

Расфасовка	Цена
0,2	9,2
0,5	16,8
1,0	29,3
2,0	45,99

Таблица № 3.

Расчёт	янв.	фев.	март
Общее за месяц	4370	4353	3851
Среднее за месяц	69	68	60
Минимум за месяц	10	18	12
Максимум за месяц	193	119	129
% продаж за месяц	34,75%	34,62%	30,63%

Таблица № 4.

Производитель	Расфасовка	№ п/п
"Добрый"	0,5	2-18-34-50