Лабораторные по Excel / MS_Office_97_2000_Система_электронных_таблиц_Excel

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

ПАКЕТ MS OFFICE: ОСОБЕННОСТИ ВВОДА ДАННЫХ

И ФОРМАТИРОВАНИЯ В ТАБЛИЧНОМ ПРОЦЕССОРЕ EXCEL

Цель работы:

∙ознакомление с типами данных, хранящихся в ячейках рабочего листа, со способами и особенностями ввода и редактирования раз- личных типов данных в табличном процессоре Excel;

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

2.1.Типы данных в ячейках рабочего листа

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

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

∙формула – это некоторая последовательность функций, операторов, констант, имен и ссылок на ячейки, которая выводит в ячейку новое значение как результат вычисления этой последовательности. Формула всегда начина- ется со знака равенства (=).

Константа в ячейке не меняется до тех пор, пока пользователь не введет

вэту ячейку новое значение. Значение, которое выводится как результат вы- числения формулы, может меняться, если меняются значения ячеек, входя- щих в эту формулу.

2.2. Способы ввода и редактирования данных в ячейках

Для ввода данных в пустую ячейку надо выделить эту ячейку, ввести данные и нажать клавишу Enter. До момента нажатия клавиши Enter вводи- мые данные могут редактироваться с использованием клавиш Backspace, Del и клавиш управления курсором. Клавиша Esc полностью удаляет только что введенные данные, оставляя предыдущую версию содержимого ячейки. После нажатия клавиши Enter только что введенные данные можно удалить командой Отменить (Undo) из меню Правка (Edit) или кнопкой Отменить (Undo) панели инструментов Стандартная (Standard).

11

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

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

кнопки:
				–	отмена изменений в строке формул, эквивалентно нажатию клави-
					ши Esc;
				–	подтверждение правильности ввода данных в строку формул, что
					эквивалентно нажатию клавиши Enter;
				–	вызов мастера функций для ввода формул.

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

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

Чтобы ввести одно и то же значение в несколько ячеек одновременно, надо выделить интервал ячеек, ввести требуемое значение и одновременно нажать клавиши Ctrl и Enter.

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

Чтобы удалить содержимое одной или нескольких ячеек, надо их выде- лить и нажать клавишу Del.

2.3. Особенности ввода текста, чисел, дат и времени

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

Если требуется ввести некоторые числа как текст, то перед числом надо поставить знак апострофа (‘).

При вводе чисел пробел может быть использован как разделитель тысяч (1 000 000). Запятая является десятичным разделителем, отделяющим целую часть числа от дробной. Введенный перед числом знак + игнорируется. Перед отрицательным числом надо ввести знак минус или заключить его в скобки.

12

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

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

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

Чтобы при вводе рациональных дробей они не воспринимались как дата, их лучше вводить как смешанные числа. Например, 1/2 интерпретиру- ется как 1 февраля, а для ввода 1/2 как дроби надо ввести 0 1/2.

2.4. Особенности ввода формул

Формулами следует пользоваться тогда, когда в ячейку рабочего листа необходимо ввести вычисляемое значение. Формула всегда начинается со знака равенства (=). Чтобы ввести формулу как текст, надо перед ней поста- вить символ апострофа (‘).

Вформулах можно использовать арифметические операторы: +, –, *, / и операторы сравнения: >, <, >=, <=, <>, =. Результатом операции сравнения является логическое значение ЛОЖЬ (FALSE) или ИСТИНА (TRUE).

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

Текст, содержащийся в формуле, должен заключаться в двойные кавыч- ки. Символ амперсанд (&) используется для конкатенации (соединения) двух текстовых величин (например, = "Итоги за "&1997).

Дата и время в формуле могут присутствовать только в виде текста, то есть в двойных кавычках. Excel преобразует их в соответствующее число при вычислении формулы (например, "15/1/98"–"10/1/98" выдаст разность 5).

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

Формулы могут содержать ссылки на другие ячейки, позволяя исполь- зовать при вычислениях значения, которые хранятся в этих ячейках (напри- мер, =A10+B3; =(D2+D3)*5; =C5>C7). Ссылка на ячейку представляет собой

ееадрес. Ссылку в формуле можно не набирать на клавиатуре, достаточно

13

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

щелкнуть мышью на нужной ячейке рабочего листа. В этом случае Excel вставляет в формулу адрес выделенной ячейки.

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

Если при вычислении произведения в указанной ячейке информация от- сутствует, то по умолчанию принимается, что значение в текущей ячейке равно 1. В случае суммирования Excel принимает значение пустой ячейки равным 0.

2.5. Использование функций

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

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

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

Имеющийся в Excel мастер функций позволяет автоматизировать про- цесс создания формул. Вызов мастера функций можно осуществить коман- дой Функция (Function) из меню Вставка (Insert), что эквивалентно нажа- тию кнопки Вставка функции (Insert Function) панели инструментов Стандартная (Standard). Аналогичную кнопку имеет также строка фор- мул, когда производится ввод или редактирование содержимого ячейки.

Вкачестве первого шага мастер функций позволяет выбрать необходи- мую функцию. Для этого в поле Категория (Category) выводится список ти- пов (логических групп) всех доступных функций.

14

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

В поле Функция (Function) перечисляются все функции, доступные

ввыбранной категории. Внизу приводится описание выделенной функции. После выбора нужной функции надо нажать кнопку ОК, осуществляю-

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

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

Когда активизировано поле ввода соответствующего аргумента, то в об- ласти отображения появляется его описание. Если для успешного заверше- ния ввода функции этот аргумент обязательно должен быть задан, то справа от имени аргумента в области отображения появляется надпись «обязатель-

ный» («required»).

Между именем аргумента и полем его ввода расположена кнопка Вставка функции (Insert Function). Щелчок по этой кнопке приведет к еще одному вызову мастера функций, что позволяет вводить вложенные функции в качестве аргумента.

После того как введено значение каждого из требуемых аргументов, вы- численное значение функции появляется в поле Значение (Value) в верхней части области отображения.

Окончание формирования функции осуществляется кнопкой OK, при нажатии которой мастер функций помещает законченную функцию в формулу.

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

Функция (Function) из меню Вставка (Insert) или кнопкой Мастер функ-

ций (Function Wizard) панели инструментов Стандартная (Standard).

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

15

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

менты. Щелчок по кнопке OK приводит к окончанию ввода изменений в эту функцию и возможен переход к следующей функции в данной формуле для ее редактирования. Кнопка OK помещает отредактированную формулу в ячейку.

2.6. Автосуммирование

Функция СУММ (SUM) – наиболее часто используемая функция, по- зволяющая заменить длинные формулы (например, =A1+A2+A3+A4) на бо- лее короткие, при этом задавая интервалы ячеек для суммирования (напри-

мер, =СУММ(А1:А4) (=SUM(A1:A4))).

Для суммирования значений смежных ячеек удобно пользоваться сред- ством Excel, которое носит название автосуммирование. При нажатии кнопки Автосумма (Autosum), находящейся на панели инструментов Стандартная (Standard), Excel не только самостоятельно набирает функцию СУММ (SUM), но и предлагает интервал ячеек для суммирования. В качестве такого интервала Excel ищет среди ячеек, примыкающих к выделенной ячейке свер- ху или слева, непрерывный заполненный числами интервал ячеек наиболь- шей длины, а затем помещает ссылку на этот интервал в формулу.

Если предложенный интервал для суммирования не соответствует же- лаемому, то пользователь может выделить нужный интервал ячеек протаски- ванием курсора мыши. Таким образом, прежде чем нажать кнопку Автосумма (Autosum), надо выделить ячейку, примыкающую к ряду ячеек в строке или столбце, содержимое которых надо просуммировать.

При выполнении автосуммирования описанным выше способом Excel предлагает для суммирования интервал ячеек с однотипными данными (либо числами, либо формулами). Если необходимо суммировать значения ячеек, часть из которых содержит числа, а часть – формулы, то надо выделить ин- тервал ячеек для суммирования, а затем нажать кнопку Автосумма (Autosum). При этом формула суммирования будет помещена в первую пус- тую строку снизу от выделенного интервала.

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

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

16

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

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

2.7. Использование имен

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

Соответственно формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки (например, формула =Приход-Расход понятнее, чем формула =H3-D3).

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

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

Чтобы присвоить формуле имя, надо в команде Имя (Name) из меню Вставка (Insert) выбрать команду Присвоить (Define) и в поле Имя (Name) набрать имя для формулы, а в поле ввода Формула (Formula) набрать саму формулу, начав ее со знака равенства.

Кнопка Добавить (Add) добавляет созданное имя к списку имен актив- ной рабочей книги. Для удаления любого имени из этого списка использует- ся кнопка Удалить (Delete).

17

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

Для вставки формулы в ячейку надо в команде Имя (Name) из меню Вставка (Insert) выполнить команду Вставить (Paste) и выбрать требуемое имя формулы.

Содержание работы

1.Откройте новую рабочую книгу и выполните следующие действия:

на рабочем листе Лист 1 (Sheet 1) начиная с ячейки A1 введите

следующие данные:

ФИО	Дата	Школа	Ср. балл
Петров	10.11.80	431	4,5
Иванов	21.03.81	234	3,75
Сидоров	01.01.81	366	4,2

(номер школы должен вводиться как текстовое значение);

к полученной таблице в колонку Е добавьте графу «Отклонение», вычисляющую отклонение среднего балла аттестата абитуриента от проходного балла в институт; значение проходного балла 4,3, поместите в ячейку H2, а в ячейке H1 введите текст «Проходной балл»;

в ячейке H2 измените величину проходного балла и посмотрите изменение значений в графе «Отклонение».

2.Для рабочего листа Лист 2 (Sheet 2) открытой рабочей книги выпол- ните следующие действия:

начиная с ячейки A1, введите следующие данные:

ФИО	Экзамен 1	Экзамен 2	Экзамен 3 Всего С учетом аттестата
Петров	4	4	3
Иванов	5	4	5
Сидоров	5	5	3

в графу «Всего» введите формулу, вычисляющую сумму баллов, полученных на экзаменах конкретным абитуриентом; формулу создайте следующими способами:

используя знак +;

используя мастер функций для создания функции СУММ (SUM) с тремя аргументами;

используя мастер функций для создания функции СУММ (SUM) с одним аргументом в виде интервала ячеек;

в графу «С учетом аттестата» введите формулы, вычисляющие общую сумму баллов с учетом среднего балла аттестата для каж-

18

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

дого абитуриента (для создания формулы использовать мастера функций, а средний балл брать из графы «Ср. балл» рабочего листа

Лист 1 (Sheet 1));

обратите внимание на способ записи внешней ссылки в формулах, находящихся в графе «С учетом аттестата».

3.Для таблицы рабочего листа Лист 2 (Sheet 2) выполните следующие действия:

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

удалите ячейки, содержащие результаты автосуммирования;

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

4.Для таблицы рабочего листа Лист 1 (Sheet 1) выполните следующие действия:

для ячейки со значением проходного балла, создайте имя;

внесите информацию о новом абитуриенте, сдавшем экзамены, а в формуле графы «Отклонение» используйте созданное имя;

перейдите к рабочему листу Лист 2 (Sheet 2) и просмотрите спи- сок доступных имен в этом рабочем листе.

5.Для таблицы рабочего листа Лист 2 (Sheet 2) выполните следующие действия:

создайте имя для формулы, вычисляющей значение графы «С уче- том аттестата»;

в качестве значения графы «С учетом аттестата» для нового аби- туриента вставьте созданное имя формулы.

6.Сохраните созданную рабочую книгу.

19

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com

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

ПАКЕТ MS OFFICE: ФОРМАТИРОВАНИЕ РАБОЧЕГО ЛИСТА

В ТАБЛИЧНОМ ПРОЦЕССОРЕ EXCEL

Цель работы:

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

∙получение практических навыков по перечисленным способам форматирования рабочего листа.

3.1.Изменение ширины столбцов и высоты строк

На новом рабочем листе все столбцы имеют стандартную ширину. Чтобы изменить стандартную ширину столбцов, надо выделить ярлычки тех рабочих листов, к которым это действие будет относиться. Затем во вкладке

Столбец (Column) из меню Формат (Format) выбрать команду Стандарт-

ная ширина (Standard Width) и ввести новое число (от 0 до 255) в поле Ширина столбца (Standard Column Width). Это число представляет собой количество символов, которое поместится в столбце при использовании стан- дартного шрифта.

Вкладка Столбец (Column) из меню Формат (Format) имеет следую- щие опции изменения параметров столбца:

Ширина (Width) – задает ширину выделенного столбца или столбцов (можно выделить только ячейку или интервал ячеек, находящихся в столбцах, ширину которых надо изменить);

Автоподбор ширины (AutoFit Selection) – устанавливает ширину столбца в соответствии с содержимым его ячеек, чтобы вместить самый длинный элемент столбца (если содержимое ячеек впоследст- вии меняется, то команду надо применить снова);

Скрыть (Hide) – скрывает выделенные столбцы;

Отобразить (Unhide) – выводит скрытые столбцы внутри выделения (область выделения должна включать ячейки, расположенные непо- средственно слева и справа от скрытых столбцов).

20

PDF Created with deskPDF TS PDF Writer - DEMO :: http://www.docudesk.com