Для студентов EXCEL

РАЗДЕЛ 3. EXCEL Тема 1. Экран EXCEL

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

СТРОКА ЗАГОЛОВКА СТРОКА ОСНОВНОГО МЕНЮ

ПАНЕЛЬ СТАНДАРТНАЯ

ПАНЕЛЬ ФОРМАТИРОВАНИЕ

КНОПКИ:

ОТМЕНЫ, ВВОДА, МАСТЕРА ФУНКЦИЙ

ЯРЛЫЧОК ЛИСТА

К ПЕРВОМУ ЛИСТУ НА ЛИСТ НАЗАД

НА ЛИСТ ВПЕРЕД К ПОСЛЕДНЕМУ ЛИСТУ

ПАНЕЛЬ РИСОВАНИЕ

СТРОКА СОСТОЯНИЯ

Рис. 1

1.Найти на экране (см. рис. 1):

•СТРОКУ ОСНОВНОГО МЕНЮ; СТРОКУ СОСТОЯНИЯ; СТРОКУ ФОРМУЛ.

•Панели инструментов СТАНДАРТНАЯ и ФОРМАТИРОВАНИЕ, как их

выставить или убрать?1

•Заголовки: СТОЛБЦОВ, СТРОК.

1 Вид|Панели инструментов|выбрать нужную панель

83

•Кнопки ПРОКРУТКИ ЯРЛЫЧКОВ.

•Кнопки ВВОДА, ОТМЕНЫ И МАСТЕРА ФУНКЦИЙ в строке формул.

•ПОЛЕ ИМЕНИ в строке формул

•ЯРЛЫЧОК листа, МАРКЕРЫ: к первому листу, к последнему листу, на лист вперед, на лист назад

2.Увеличить масштаб и изображения на экране до 150%, восстановить 100%

3.Свернуть окно Excel, восстановить его.

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

действия можно выполнить при помощи команд Формат| Столбец|

Ширина или Формат| Строка| Высота.

4.Ввести в ячейку A1 свою фамилию и имя.

5.Увеличить ширину столбца A так, чтобы введенный текст полностью отображался в ячейке.

6.Выделить текст в ячейке и изменить шрифт, задав Times New Roman, размер 16 пт.

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

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

полнить команды Формат| Столбец| Автоподбор ширины, Формат| Строка| Автоподбор высоты.

Выделение диапазона ячеек

Возможно выделить смежные (например, А2:C5) и несмежные (например, А2:В10; D2:E10) диапазоны.

Выделить смежный диапазон можно так:

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

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

Для выделения несмежного диапазона ячеек используется клавиша

.

84

8.Ввести в ячейки А2:C5 числа от 1 до 12.

9.Выделить ячейки А3; C4 (А3 и C4).

10.Выделить диапазон ячеек А3:C4.

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

10.2.Выделить диапазон ячеек А3:C4 и очистить содержимое, используя правую кнопку мыши.

10.3.Выделить диапазон ячеек F3:H4 и удалить содержи-

мое ячеек.

Тема 2. Ввод и редактирование данных

Цель занятия: изучить механизм ввода и редактирования данных в ячейку.

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

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

178654 примет вид 1,79Е+05).

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

сле числа помещается знак валюты, например: 6 153,00 €); финансовом (этот формат отличается от денежного вынесением знака отрицательного числа в крайнюю левую позицию);

даты и времени (формат дает различное представление даты и времени; даты сохраняются в виде чисел — количества дней, прошедших с 01.01.1900 г); процентном (число отображается в процентах, например 73%); дробном (формат выводит смешанные и простые дроби);

экспоненциальном (число отображается в виде мантиссы, десятичного основания Е и порядка, например 1,79Е+05); дополнительном (формат предназначен для работы с почтовыми индексами, телефонными номерами и пр.).

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

85

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

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

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

11.Ввести в последовательные ячейки столбца

источник

истинное

отношения

Обратить внимание на текст, который появляется на экране при пере-

мещении курсора.

12. Ввести в нижестоящую ячейку букву о, нажать Enter ввести в ячейку следующей строки иста, нажать Enter

в ячейке строкой ниже вызвать контекстное меню, выбрать команду Выбрать из списка, затем выбрать «источник» и нажать ввод1.

13. Ввести в ячейку D3 слово «суббота», предварительно расширив столбец D.

13.1. Выделить ячейку D3, установить курсор в ее нижнем правом углу и, когда он примет вид черного крестика (+)2, распространить текст последовательно на ячейки D3: D5, D3: F3, D3: A3, D3: D1, удерживать маркер заполнения левой кнопкой мыши. После выполнения вышеуказанных действий в смежных ячейках должны появиться названия последующих (предыдущих) дней недели

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

2Этот крестик называется маркером автозаполнения

86

14. Перейти на Лист2, расширить ячейку A1 и ввести слово Редактирование. Для внесения изменений в содержимое ячейки можно воспользоваться одним из следующих способов:

14.1.Подвести курсор к ячейке A1 и нажать клавишу F2. После этого содержимое ячейки станет доступным для редактирования. Изменить шрифт в ячейке, задав Times New Roman и цвет Красный На-

жать Enter.

14.2.Двойной щелчок левой кнопкой мыши по ячейке A1 по ячейке также делает доступным для редактирование ее содержимое. Изменить цвет на Зеленый, нажать Enter.

14.3.Подвести курсор к ячейке A1 и щелкнуть левой кнопкой мыши по строке формул. В строке формул также можно вносить необходимые изменения. Установить цвет Авто и продолжить ввод текста, чтобы в ячейке получилась фраза Редактирование и форматирование.

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

15.1.Выделить ячейку и нажать

15.2.Выделить ячейку, вызвать контекстное меню и выбрать

Очистить содержимое.

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

Тема 3. Форматирование таблиц

Цель занятия: изучить возможности Excel, позволяющие улучшать визуальное восприятие таблицы.

Приемы форматирования

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

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

87

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

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

1. В диапазоне ячеек A1:D4 создайте копию приведенной ниже таблицы.

1.1.Ячейки A1:D1 нужно объединить и расширить 1-ю строку, набрать и отформатировать соответствующий текст.

1.1.Расширить столбец A.

1.2.Расширить 2-ю строку и набрать в ячейке A2 текст.

1.3.Расширить столбец B и набрать текст в ячейке B2.

1.4.Расширить 3-ю строку, набрать текст в ячейке A3 и установить следующее выравнивание: Формат| Формат ячеек| Вы-

равнивание| По вертикали по верхнему краю| По горизонтали по левому краю.

1.5.Аналогично набрать текст и отформатировать ячейку B3.

1.6.В ячейках C2, C3, D2 (ячейки D2 и D3 объединить) нужно набрать текст и изменить направление текста: Формат| Фор-

мат ячеек| Выравнивание| Ориентация.

1.6.1.Для каждой ячейки задать нужные границы: Формат| Формат ячеек| Граница и выбрать такую, как приведена на образце.

1.7.Расширить 4-ю строку, в ячейке A4 набрать текст и сделать за-

ливку ячейки: Формат| Формат ячеек| Заливка.

1.8.Ячейки B4:D4 объединить, выполнить форматирование и вставить необходимые символы: Вставка| Символ. Для набора

88

что при просмотре файла в сети или выводе на печать формат файла останется неизменным и данные файла не могут быть легко изменены. Формат Pdf удобен и для воспроизведения документов с использованием коммерческих методов печати.

Тема 4. Форматы чисел

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

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

1.Сделать шире столбец A. Ввести число 12345,678, нажать клавишу .

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

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

2.1.#,## Формат|Ячейки| (Все форматы)|Тип| #,##| ОК.

2.2.0,0 Формат|Ячейки| (Все форматы)|Тип| 0,0| ОК.

2.3.0,0000 Формат|Ячейки| (Все форматы)|Тип| 0,0000 | ОК.

2.4.0,???? Формат|Ячейки| (Все форматы)|Тип| 0,???? | ОК.

2.5.0,#### Формат|Ячейки| (Все форматы)|Тип| 0,####| ОК.

2.6.# #,0 Формат|Ячейки| (Все форматы)|Тип| # #,0| ОК.

90

2.7. #,# Формат|Ячейки| (Все форматы)|Тип| #,# | ОК.

2.8.#,#0_% Формат|Ячейки| (Все форматы)|Тип| #,#0_%| ОК.

2.9.#,##% Формат|Ячейки| (Все форматы)|Тип| #,##%| ОК.

2.10.[Синий]0,00 формат|ячейки| (все форматы)|Тип| [Си-

ний]0,00| ОК.

Сделать выводы и ответить на следующие вопросы:

a)для чего используется знак пробела до запятой и послеа?

b)для чего используется знак подчеркивания _b?

c)чем отличаются форматы 2.1 и 2.2c?

d)чем отличаются форматы 2.3 и 2.4d?

e)чем отличаются форматы 2.3 и 2.5?

f)чем отличаются форматы 2.4 и 2.5f??

3.Ввести число 0,078, скопировать его в 10 ячеек, используя технологию Drag and Drop, т. е. выделить ячейку, содержащую число, подвести курсор к любой границе ячейки (при этом курсор должен

принять вид стрелки ) и, удерживая клавишу , скопировать содержимое ячейки в рядом стоящую ячейку. Эти ячейки потребуются при выполнении п. 3.1-3.11. Изменить формат ячеек (Фор-

мат|Ячейки| (Все форматы)| Тип| Создать тип| ОК) ( означает про-

бел):

3.1.#,0000

3.2.#,##

3.3.0,0

3.4.?,????

3.5.0,000

3.6.#,00_$

3.7.#,##$

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

а Пробел, стоящий в формате после запятой делит число на 1000! Если он присутствует до запятой, то просто разделяет число на группы по 3 цифры

bСправа оставляется место, равное по ширине следующему за _ знаку

c0 и # округляют число до указанного числа знаков. Различие в применении этих форматов проявляется, если число дробное (см. 4.2 и 4.3)

dЕсли дробная часть короче, чем число 0 или ?, то справа дописываются 0 или пробелы

f Если дробная часть короче, чем количество #, то она не изменяется

91

леньких чисел. В экспоненциальной форме число представляется в виде мантиссы (до буквы E) и порядка (после буквы E). Число 0,000000000078 в экспоненциальной форме представляется как 7,8 10–11, а число 780000000000 7,8 1011. Мантиссы у обоих чисел в данном случае равны, равны и их порядки, но они отличаются знаком.

3.8.0,00Е+00

3.9.0,00Е+##

3.10. #,##Е–0_% Если порядок числа положительный, то в этом формате он выводится без знака, а если отрицательный, то со знаком.

3.11.#,00Е+0%

3.12.Ввести число 12345,678 и задать формат 0,00Е+00

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

4.Ввести текущую дату и время: задать функцию =ТДАТА() или =СЕГОДНЯ(). Для этого можно воспользоваться МАСТЕРОМ ФУНК-

ЦИЙ либо просто набрать имя функции в ячейке. Эти функции не требуют аргументов, поэтому никаких данных в круглых скобках вводить не надо. Скопировать дату в 6 смежных ячеек, затем задать

ипоследовательно применить форматы:

4.1.ДД.ММ.ГГ

4.2.ДД.МММ.ГГГ

4.3.ДДД.ММ.ГГГГ

4.4.ДДДД.ММММ.ГГ ―года‖

4.5.Ч:ММ:СС

4.6.Ч:ММ:СС AM/PM

4.7.Ч:ММ:СС A/P

Сделать выводы и ответить на следующие вопросы:

a)какой формат нужно задать, чтобы определить, в какой день недели Вы родились?

b)На что указывают маски am/pm в форматах 4.6 и 4.7?

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

92