Excel. Практическая работа №10. (4 часа).

 

 "Основные приемы создания электронных таблиц"

 

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

 

Порядок выполнения работы

1.      Запуск Excel

Программу можно запустить:

•    из    главного    мeню Windows,     нажав     клавишу    Пуск, Программы,

•    дважды    щёлкнув    имя    любого    файла    документа    с расширением .xls,

•    используя Проводник Windows.

Ø     Запустите программу Excel.

 

2.      Окно Excel

Данные в Excel организованы в виде книги, которая состоит из рабочих листов. Рабочая книга сохраняется на диске в едином файле, который имеет расширение .xls.

При открытии рабочей книги ей автоматически присваивается имя «Кннга 1». После операции сохранения это имя заменяется на имя определяемое пользователем.

Новая рабочая книга изначально состоит из рабочих листов, названных по умолчанию «Лист 1», «Лист 2» и т.д.. Эти имена отображены на ярлычках листов. Листы могут содержать информацию разного типа. Для перехода к другому листу необходимо щёлкнуть по ярлыку требуемого листа.

 

Ø     Попробуйте переключиться между листами.

Ø     Добавьте новый лист.

Указание

Активизируйте лист, перед которым нужно вставить новый

лист.

Из меню Вставка выбрать команду Лист.

Ø     Удалите вставленный лист.

Указание

Выделите удаляемый лист.                                        

Из меню Правка выбрать команду Удалить лист.

Ø     Переименуйте первый лист.

Указание

1-й способ:

дважды щёлкнуть по ярлычку листа и ввести новое имя.

2-й способ:

из     меню     Формат     выбрать     команду     Лист,     затем  Переименовать.

 

Рабочие листы содержат по 256 столбцов и 65536 строк. Заголовки столбцов формируются так: А, В, С и т.д.; после Z используются двухбуквенные наименования АА, АВ,..., AZ, ВА, BВ и т.д. Заголовки строк представлены числами от 1 до 65536.

На пересечении строки и столбца - ячейка. Адрес ячейки определяется заголовками соответствующих столбца и строки (например, А2, ВЗ). Адрес ячейки называют ещё ссылкой на ячейку.

 

3.   Ввод и редактирование информации

В ячейки рабочей книги может быть введена информация 3-х типов:

•   текст;

•    число;

•   формула.

Одновременно в одной ячейке может содержаться информация только одного типа.

 

3.1 Ввод информации

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

•   нажав клавишу Enter,

•   нажав кнопку перед полем ввода, помеченную птичкой,

•   щёлкнув мышью по другой ячейке,

·        покинув  текущую  ячейку  с   помощью   клавиш  управления курсором.

 

3.2 Редактирование информации.

Для     того     чтобы     исправить     введённую     информацию, воспользуйтесь одним из способов:

  1-й способ:

выбрать ячейку.

Нажать клавишу F2.

Отредактировать содержимое ячейки;

2-й способ:

выбрать ячейку.

Щёлкнуть мышью по строке формул.

Отредактировать содержимое ячейки;

3-й способ:

дважды щёлкнуть по нужной ячейке.

Отредактировать содержимое ячейки.

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

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

 

Все задания выполнять в одном файле PR10.xls на разных листах!

 

Ø     Создайте таблицу следующего вида (Задание 1).

 

 

 

Ø     Для этого введите в следующие ячейки информацию.

А1 - Фамилия

В1-Начисления

В2- Зарплата               

С2 - Премия

D2 -Больничный лист

Е2 - Прочие начисления

F1 -Всего начислено       

 

Ø     Внесите данные в графы таблицы

 

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

 

4. Выделение ячеек

 

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

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

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

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

Точно также выделяются полные столбцы, только щёлкать надо на заголовках столбцов.

Для выделения полных строк с клавиатуры достаточно нажать комбинацию клавиш Shift + Пробел.

Для выделения полных столбцов с клавиатуры достаточно нажать комбинацию клавиш Ctrl + Пробел.

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

 

Ø     Попробуйте выделить столбец В.

 

Ø     Попробуйте выделить столбцы С, D, Е.

 

Ø     Попробуйте выделить строку №4.           

 

Ø     Попробуйте выделить диапазон ячеек В2 : D5.  

 

Ø     Попробуйте выделить всю таблицу.               

 

 

5. Форматирование ячеек

 

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

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

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

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

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

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

 

Исправим созданную вами таблицу.

Ø     Объедините ячейки А1 и А2. 

 

Ø     Объедините ячейки F1 и F2.

 

Ø     Объедините ячейки В1 и El.      

 

Ø     Разместите данные в таблице по центру столбцов.

 

Ø     Для заголовка таблицы установите полужирный шрифт и голубой фон заливки.

 

Ø     Для ячеек А1 и F1 установите перенос по словам.

 Указание

Выделить ячейку.

В меню Формат выбрать команду Ячейки.

На закладке Выравнивание установить флажок Переносить по словам.

 

Ø     Дтя ячеек В2, С2, D2 установите размер по ширине текста.

Указание

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

 

Ø     Установите границы для ячеек.

 

 6. Ввод и копирование формул

Формула должна начинаться со знака равенства (=), за которым следует набор вычисляемых величин.

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

При создании формулы ссылки обычно изменяются относительно расположения ячейки, содержащей формулу. В следующем примере ячейка В6 содержит формулу =А5 + А6.

 

	А	В
6	200	=А5+Аб
7	150

При копировании формулы, содержащей относительные ссылки, и вставке её в другое место, ссылки будут указывать на другие ячейки. Например, если формулу из ячейки В6 скопировать в ячейку В7. В ячейке В7 будет содержаться формула =А6 + А7.

 

	А	В
6	200	=А5+Аб
7	150	=Аб + А7

 

Такие ссылки называются относительными.

Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками. Например, если имеется формула, умножающая содержимое ячейки А5 на ячейку С1 (=А5*С1), то при копировании формулы в другую ячейку изменятся обе ссылки. Для создания абсолютной ссылки на ячейку С1 поставьте знак доллара ($) перед той частью, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку С1, поместите знак доллара так, как показано в примере.

=А5*$С$1.

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

1-й способ:

выделить ячейку или группу ячеек.

Выбрать в меню Вставка  команду Имя,  а затем

Присвоить.

Ввести имя в поле Имя в появившемся диалоге.

Нажать кнопку Ok;

 

2-й способ:

выделить ячейку или столбец (строку) ячеек.

Ввести  в   поле  Имя   необходимое   имя   и   нажать клавишу Enter.

Для задания диапазона ячеек служит символ ":" (двоеточие). Например, для того чтобы указать диапазон ячеек от A3 до В5, достаточно написать АЗ:В5.

Для того чтобы указать все строки данной колонки, достаточно задать диапазон без указания номеров строк. Например, все строки колонки F - (F:F).

Аналогично этому, если надо указать все колонки данной строки, достаточно задать диапазон без указания номеров колонок. Например, все колонки строки 5 - (5:5).

 

Функции Excel.

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

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

 

Ø     Введите в ячейку F4 формулу, обеспечивающую подсчёт суммы    начислений.

Указание

Встать на ячейку F4.

На панели инструментов выбрать кнопку Автосумма.

Выделить мышью ячейки, в которых надо подсчитать сумму (В4 -

Е4).

Нажать Enter.

 

Ø     Скопируйте введённую формулу в ячейки F5- F6.

 Указание.

Встать на ячейку F4.

Протянуть    указателем   мыши    за    правый    нижний    угол    до

необходимого места (ячейки F6) и отпустить клавишу мыши.

В ячейках F5 – F6 должны появиться суммы баллов по каждому

из абитуриентов.

 

Ø     Посчитайте суммы ИТОГО: в каждой колонке.

 

7.      Вставка и удаление ячеек.

 

 Чтобы вставить ячейку, надо выделить ячейку, перед которой её надо вставить, и выбрать из меню Вставка команду Ячейки. Если нужно добавить столбец ячеек, то выделите столбец, перед которым должен быть вставлен пустой, и выберите из меню Вставка команду Столбец. Точно так же можно вставить несколько столбцов или строк. Для этого выделите столько столбцов и строк, сколько надо вставить.

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

                       

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

 

Ø     Вставьте перед таблицей  2 строки и  озаглавьте таблицу «ВЕДОМОСТЬ НАЧИСЛЕНИЙ»

 

Ø     Вставьте перед таблицей 2 столбца.

 

Ø     Удалите столбец Е.

 

8.      Сортировка данных.

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

б) Из меню Данные выбрать команду Сортировка.

в) В списке Сортировать по выбрать первое поле, по которому следует упорядочить данные.

г) Выбрать переключатель По возрастанию или По убыванию.

д) В списке Затем по выбрать второе поле сортировки, если возникает необходимость.

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

Ø     Отсортируйте данные в  таблице     в  порядке  возрастания начислений.

  ___________

      4 балла

 

Задания для самостоятельного выполнения

 

Задание 2. Оформите и рассчитайте в Excel следующую таблицу:

Ведомость начисления заработной платы рабочим

                             Мин. зар. Плата       310,00 р.

                         Примечания:

                                          1) Графа 3 = графа 2 * Мин. зар. Плата

                                                2) Графа 5 = графа 3 * графа 4 /100

  3) Графа 6 = графа 3 + графа 5

Знак «*» в таблице указывает на подсчёт итога по данной графе. Отсортируйте таблицу по возрастанию табельных номеров.

 

  ___________

      2 балла

 

Задание 3.

Потребность в спецодежде и обуви

                                 Количество рабочих             20

 

Произведите необходимые вычисления в 3, 4, 6 столбцах. Знак «*» в таблице указывает на подсчёт итога по данной графе. Отсортируйте данные в таблице по количеству выдач в год.

  ___________

      2 балла

Задание 4. Ведомость успеваемости

 

Заполните таблицу данными и произведите необходимые расчёты.

Отсортируйте фамилии в алфавитном порядке.

Используя условное форматирование выделите шрифтом разного цвета средние баллы уч-ся.  >  и  < среднего балла группы.

 

  ___________

      2 балла

 

• Сдать работу - скопировать папку с работой НомерГруппы_Фамилия_НомерРаботы, например, 218т_Иванов_10 в папку //Aud27_0/ЛР_Сдача/ (ярлык к папке на рабочем столе).