Табличный редактор Microsoft Excel в упражнениях

12

Упражнение № 1 Ввод и редактирование формул в Excel

1. Загрузите Excel. Для этого выполните последователь­но команды Пуск – Все про­граммы - Microsoft Office - Microsoft Excel.

1. Освоим выделение ячеек, строк, столбцов.

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

• Выделите несколько смежных ячеек. Например, установите курсор мыши на ячейку АЗ, нажмите на левую кнопку мыши и, не отпуская ее, протяните мышь до ячейки ЕЗ, затем отпустите мышь. Диапазон ячеек АЗ:ЕЗ выделится черным цветом.

• Выделите несмежные ячейки. Например, нажмите клавишу Ctrl и, удерживая ее, щелкните последовательно по ячейке С5, Е8, В1, диапазон С8:Н11, С4, затем отпустите клавишу мыши и Ctrl.

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

• Выделите столбец, щелкнув мышью по его заголовку.

• Выделите все листы книги, щелкнув по кнопке пересечения заголовков строк и столбцов. Отмените выделение, щелкнув в лю­бом месте рабочего поля листа.

3. Научимся вводить и редактировать формулы.

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

• Введите в ячейку А1 цифру 2 и нажмите клавишу Enter. Ввод значения в ячейку А1 будет завершен. Темная рамка автоматиче­ски перейдет в ячейку А2.

• Введите в ячейку А2 цифру 3 - Enter. Темная рамка перей­дет в ячейку АЗ. Запомните правила ввода формул:

Правило 1. ввод формулы в ячейку всегда начинается со знака «=».

Правило 2. Вводите формулу только латинскими буквами

• Введите в ячейку АЗ формулу «=А1+А2» - Enter. В ячейке АЗ появится результат 5.

4. Отредактируем формулу в ячейке АЗ, заменив знак «+» на «-». Для этого:

• Выделите ячейку АЗ, нажав клавишу управления курсором Стрелка вверх или щелкнув по ячейке АЗ левой кнопкой мыши.

• Нажмите клавишу F2. Включится режим редактирования со­держимого ячейки.

• Нажимая клавишу Стрелка влево, поместите текстовый кур­сор перед знаком «+», нажмите клавишу Delete. Знак «+» будет уда­лен.

• Введите знак «-» (минус) и нажмите клавишу Enter. В ячейке АЗ появится результат разницы содержимого ячеек А1 и А2. Таким образом, мы отредактировали формулу в ячейке АЗ.

5. Научимся копировать содержимое ячеек в буфер обмена

• Выделите ячейку А1 .

• Нажмите на вкладке Главная, группы Буфер обмена кнопку Копировать. Вокруг ячейки А1 появится бегущая штриховая рамка, содержимое ячейки скопировано во внутренний буфер обмена Windows.

• Нажмите клавишу Стрелка вправо, чтобы выделить ячейку В2.

• Нажмите кнопку - Вставить на панели инструментов Стан­дартная. Содержимое буфера обмена будет вставлено в ячейку В2 - Enter.

Самостоятельно скопируйте содержимое ячейки А2 в ячей­ку B2.

6. Научимся быстро вычислять сумму ячеек по столбцам и строкам таблицы.

а) Вычислите сумму ячеек А1:В1 (в Excel так обозначаются диапазоны ячеек). Для этого:

• Выделите диапазон ячеек от А1 до С1.

• Щелкните мышью по кнопке ∑ - Автосумма, которая нахо­дится на вкладке Главная. В ячейку С1 будет автоматически вставлена формула =СУММ(А1 :В1), что означает вычисление сум­мы значений, начиная с ячейки А1 и заканчивая В1.

б) Вычислите самостоятельно сумму ячеек В1 :В2.

7. Освоим еще один метод редактирования формул, который позволяет быстро вставлять адреса ячеек в формулу не с клавиатуры, а при помощи мыши.

• Выделите ячейку АЗ. Обратите внимание: значение текущей ячейки отображается в строке формул.

• Нажмите клавишу «=» и щелкните мышью по ячейке А1. В фор­мулу будет вставлен адрес этой ячейки.

• Нажмите клавишу «+» и щелкните мышью по ячейке В1. В фор­мулу будет вставлен адрес этой ячейки.

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

8. Выйдите из Excel любым из четырех способов, известных вам из работы с Word.

Упражнение № 2 Форматирование ячеек, сохранение и печать документа

1.Загрузите Excel

2.Изучим основные возможности форматирования таблица примере простого бланка-счета за ремонт телевизора.

• Введите в ячейку А1 строку «Счет за ремонт телевизора», в ячейку АЗ символ «№», в А4 - 1, в А5 – 2.

• Введите в ячейку ВЗ строку «Наименование работ», в В4 -Замена кинескопа, в В5 - Ремонт антенны, в В6 - Итого:, в В7 -НДС:, в В8 - Спецналог, в В9 - К оплате:.

• Введите в ячейку СЗ строку «Стоимость работ», в С4 - 200000, в С5-15000.

3. Изменение ширины столбцов. Обратите внимание, строка «Стоимость работ» закрывает со­бой строку «Наименование работ». Исправим это. Но сначала умень­шим ширину столбца А.

• Установите указатель мыши на границу столбца А так, чтобы указатель мыши изменил вид с белого крестика на черный.

• Нажмите и, удерживая левую кнопку мыши, передвиньте гра­ницу столбца А влево до символа «№». Это первый способ изме­нения ширины столбцов.

• Теперь подгоните ширину столбцов В и С, используя второй способ. Для этого установите указатель мыши на заголовке столб­ца В.

• Нажмите и, удерживая левую кнопку мыши, переместите мышь вправо, выделяя столбцы В и С. Отпустите левую кнопку мыши.

• Выберите во вкладке Главная, группы Ячейки, значок Формат, команду Автоподбор ширины. Ширина столбца изменится так, чтобы в столбцах уме­стилась самая длинная строка.

4. Выбор ширины и выравнивание текста. Первое, что обычно делают при форматировании таблиц, - это выбирают шрифт для заголовков, чисел и пояснений.

• Щелкните мышью по ячейке А1 и выберите шрифт Times New Roman и раз­мером 14 .

• Щелкните мышью на заголовке третьей строки. Выделится вся строка. Назначьте выделенной строке шрифт Times New Roman размером 14 пт. При этом строки в ячейках ВЗ и СЗ выйдут за пределы столбцов.

• Подберите ширину столбцов, перетаскивая границы столбцов в заголовках. Отцентрируйте текст в ячейках строки 3, нажав кнопку По центру (во вкладке Главная, группы Шрифт). Выделите диапазон ячеек А1 :С1 и нажмите кнопку - Объединить и поместить в центре на вкладке Главная, группы Выравнивание. Текст будет выровнен по центру выделенного диапазона.

• Выделите диапазон ячеек В6:В9 и выровняйте текст в ячейках по правому краю, нажав кнопку По правому краю (на вкладке Главная, группы Выравнивание).

• Подсчитайте, сколько будет стоить замена кинескопа и ремонт антенны, и результат занесите в ячейку Итого:. Для этого выделите диапазон ячеек С4:С6 и щелкните по кнопке Автосумма во вкладке Главная, группы Редактирование. В ячейке С6 появится результат вычислений «215000».

• Введите в ячейку С7 формулу для вычисления НДС «=С6*0,2», в ячейку С8 формулу для вычислений спецналога «=С6*0,015».

• Выделите диапазон ячеек С6:С9 и щелкните мышью по кнопке Автосумма. В ячейку С9 будет вставлен результат вычислений «261225».

Наш бланк почти готов. Осталось отформатировать числа и ус­тановить обрамление и фон ячеек, чтобы придать бланку «товар­ный вид».

5. Форматирование чисел. Суммы чисел в нашем бланке плохо различимы из-за большого количества нулей. Непонятно также, в какой валюте выписан счет.

• Выделите диапазон ячеек С4:С9,

• Щелкните по выделенному фрагменту правой кнопкой мыши и выберите из вкладки Главная, группы Ячейки, значок Формат, команду Формат ячеек, Число - в списке Числовые форматы – Денежный (или во вкладке Главная, группы Ячейки, подгруппы Формат, команда Формат ячеек).

• Если в поле Обозначение не стоит денежная единица (р.), то установите, выбрав ее из выпадающего списка - ОК.

Выделенные суммы отобразятся с разделением тысяч и симво­лом «р.» в конце.

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

• Выделите диапазон ячеек А1 :С9,

• Выберите команду во вкладке Главная, группы Ячейки, подгруппы Формат, команда Формат ячеек, Граница, Тип линии - и затем в поле Все щелкните по кнопке Внешние. В поле Отдельные появится образец обрамления двойной линией. - ОК.

• Выделите диапазон ячеек АЗ:СЗ,

• Щелкните мышью во вкладке Главная, группы Шрифт, значок Границы и выберите там шаблон Толстая нижняя граница. Выделенный диапазон ячеек будет подчеркнут толстой черной линией.

• Выделите диапазон ячеек ВЗ:В9 и создайте Правую границу (тонкая черная чер­та справа) с помощью кнопки Границы.

• Выделите диапазон ячеек А9:С9,

• Откройте цветовую палитру (во вкладке Главная, группы Шрифт, кнопка Цвет заливки) для выбора цвета фона. Щелкните мы­шью на квадратике желтого цвета в палитре. Фон выделенного диа­пазона ячеек станет желтым. Черные цифры на желтом фоне хоро­шо видны на экране, а при печати на черно-белом принтере жел­тый цвет будет выглядеть как светло-серый. На этом создание на­шего бланка закончено.

7. Изменение информации в бланке. Посмотрите внимательно на свой бланк и представьте, что в связи с изменением цен вам нужно изменить стоимость работ по замене кинескопа с 200000 на 250000.

Для этого выделите ячейку С4 и введите новую стоимость 250000, нажмите Еntег.

Проанализируйте, как изменились остальные значения.

7. Подготовка документа к печати всегда должна начинать­ся с установки параметров страниц.

   • Выберите вкладку Разметка страницы, группу Параметры страницы, значок Ориентация, кнопка Книжная. Затем здесь же откройте значок Размер и выберите кнопку А4 (210x297 мм).

   • Выберите значок Поля (во вкладке Разметка страницы, группы Параметры страницы, значок Поля). Установите желаемые отступы от краев листа бумаги полей Верхнее, Нижнее, Левое, Правое или оставьте прежние, с помощью команды Настраиваемые поля (или выберите готовые значки по образцу). В поле Центрировать на странице установите флажок Гори­зонтально и сбросьте флажок Вертикально.

Теперь наш бланк будет размещен в верхней части листа и выровнен по центру шири­ны листа.

• Нажмите кнопку Предварительный Просмотр (на панели быстрого доступа). На экране появится изображение листа бумаги с нашим бланком. Для выхода из просмотра - кнопка Закрыть окно предварительного просмотра.

Если вы сделали все правильно, то должны получить бланк сле­дующего вида

№ Наименование работ

Стоимость работ

1 Замена кинескопа 2 Ремонт антенны

Итого:

НДС:

Спецналог:

К оплате:

250 000р.

15 000р.

265 000р.

53 000р.

3 975р.

321 975р.

9. Выведем бланк-счет на печать, нажав на кнопке «Office» (верхний правый угол окна) кнопку Печать, затем команду Печать.

Упражнение № 3 Построение диаграмм

1. Загрузите Excel

2. При помощи вкладки Разметка страницы, группы Параметры страницы установите желаемые поля и выберите ориентацию Книж­ная.

3. Создайте следующую таблицу

СТРУКТУРА преступности по г. Бобруйску за 2000 и 2001 гг.

Уд. вес за 2001 г.

Уд. вес за 2002 г.

Убийства

0,65

0,82

Тяжкие телесные повреждения

1,2

1,72

Тяжкие

51,7

63,1

Разбои

1,82

1,05

Кражи личного имущества

58,8

56,2

В том числе из квартир

26,6

26,6

Кражи и угоны транспорта

2,6

2,3

Кражи гос. имущества

6,5

5,9

Изнасилования

0,62

0,66

Грабежи

9,2

8

4. Постройте диаграмму по данным таблицы. Для этого:

• Выделите всю таблицу.

• Во вкладке Вставка, группы Диаграммы откройте значок Гистограмма и выберите одну из предложенных гистограмм.

• В открывшейся дополнительной вкладке Работа с диаграммами, во вкладках Конструктор, Макет и Формат внесите все необходимые изменения к нашей гистограмме, а именно: введите Название диаграммы, названия осей, легенду, подписи данных, в группе Подписи. Во вкладке Формат (при необходимости) в группах Стили фигур, Стиль WordArt, внесите необходимые изменения для нашей гистограммы, а также формат выделенного фрагмента (в группе Текущий фрагмент)

5. Вы увидите диаграмму. Она выделена, т. е. вокруг диаграм­мы расположена светло-голубая рамка. Установив курсор мыши на квадратик так, чтобы он принял вид двусторонней стрелки, можно изменять размеры диаграммы. Если вас не устраивают размеры диаграммы, то измените их (во вкладке Формат, группа Размер). Если не устраивает размещение диа­граммы - установите курсор мыши на границу диаграммы так, что­ бы он принял вид четырех черных стрелок, нажмите левую клавишу мыши и, не отпуская ее, переместите диаграмму в другое место.

6. Просмотрите, как будет выглядеть таблица вместе с диаграм­мой на листе. Для этого:

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

Вы создали внедренную диаграмму, т. е. диаграмму на имею­щемся листе.

7. Создайте по данным этой же таблицы диаграмму на от­дельном листе. Для перемещения диаграммы зайдите во вкладку Конструктор (при выделенной диаграмме), выберите значок Переместить диаграмму

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

9. Сохраните ваш документ в вашу папку.

10. Закройте документ. Закройте программу Excel.

Упражнение № 4 Автозаполнение таблицы, вставка верхних и нижних индексов, выравнивание текста по центру выделения.

Упражнение 1. Автозаполнение.

• В ячейку А1 введите число 1, в ячейку А2 введите число 2, выделите обе эти ячейки.

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

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

Упражнение 2. Составьте таблицу для вычисления n-го чле­на и суммы арифметической прогрессии.

Формула n-го члена арифметической прогрессии an=a1+d*(n-1).

Формула суммы n первых членов арифметической прогрессии: Sn=(a1+an)*n/2.

Первый член возьмем a1 =-2

Выполнение упражнения можно производить следующим образом:

• В ячейку А1 введите заголовок таблицы «Вычисление n-го члена и суммы арифметической прогрессии». Заголовок будет размещен в одну строку и займет несколько ячеек правее a1 .

• Сформатируйте строку заголовка таблицы. В ячейку А2 введи­те «d», в ячейку В2 - «n», в С2 - «an», в D2 -«Sn».

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

Приступите к заполнению таблицы.

• В ячейку АЗ введите величину разности арифметической про­грессии d (в нашем примере это 0,725). Далее заполните ряд нижних ячеек таким же числом. Растиражируйте это значение вниз, используя маркер заполнения.

В следующем столбце размещена последовательность чисел от 1 до 10. Воспользуйтесь маркером заполнения и заполните стол­бец (упражнение 1 ).

• В ячейку СЗ поместите формулу для вычисления n-го члена арифметической прогрессии an и зафиксируйте ее нажати­ем клавиши Еntег. Выполните автозаполнение нижних ячеек, «протащив» форму­лу за маркер заполнения. Сверьте полу­чившиеся значения с образцом (см. таб.).

• Аналогично введите в ячейку D3 фор­мулу для подсчета суммы п первых чле­нов арифметической прогрессии Sn и рас­пространите заполнение на прилегающие ячейки.

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

Выделите столбцы А,В,С,D, протянув мышью по заголовкам, и выполните команду вкладка Главная, подгруппа Ячейки, значок Формат, Автоподбор ширины столбца.

d

п

An

Sn

0,725

1

-2

-2

0,725

2

-1,275

-3,275

0,725

3

-0,55

-3,825

0,725

4

0,175

-3,65

0,725

5

0,9

-2,75

0,725

6

1,625

-1,125

0,725

7

2,35

1,225

0,725

8

3,075

4,3

0,725

9

3,8

8,1

0,725

10

4,525

12,625

• Для заголовка и шапки таблицы выберите полужирное начер­тание. Шапку таблицы отцентрируйте.

• Заголовок довольно неэстетично «вылезает» вправо за преде­лы нашей маленькой таблички. Выделите диапазон ячеек А1:D1 и выполните команду Главная- Ячейки- Формат-Формат ячеек - выберите вкладку Вырав­нивание – в поле отображение выберите Переносить по словам - В поле По вертикали установите По цен­тру – в поле По горизонтали По значению – ОК.

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

Обрамите таблицу при помощи кнопки Границы во вкладке Главная, подгруппы Шрифт.

Задания для тренировки:

1. Составьте таблицу значений линейной функции

У= 3,5х - 9,5.

Для заполнения ячеек значениями x введите первые два значе­ния, выделите обе ячейки и протяните маркер заполнения вправо. Для заполнения ячеек значениями у введите формулу в первую ячейку ряда и заполните вправо. При форматировании таблицы ширину столбцов возьмите равную 4 пт.

ТАБЛИЦА ЗНАЧЕНИЙ ФУНКЦИИ у = 3,5х - 9,5

[pic] 2. Оформите таблицу, позволяющую рассчитывать расход материалов для покраски в зависимости от площади поверхно­стей. Площади придумайте сами. Обратите внимание на форматиро­вание рамок таблицы и на центрирование записей по выделению.

Поверхность

Двери

Подоконники

кг на 10 м⁷

Площадь

Расход

кг на 20 м²

Площадь

Расход

Олифа

Упражнение № 5 Закрепление основных навыков работы с электронными таблицами, знакомство с понятием «Сортировка данных»

Содержание работы: Упражнение заключается в создании и заполнении бланка то­варного счета

Наименование

Ед.измерения

Кол-во

Цена

Сумма

1

2

3

4

5

6

ИТОГО

1-й этап. Создание таблицы бланка счета.

2-й этап. Заполнение таблицы.

3-й этап. Оформление бланка.

1-й этап. Заключается в создании таблицы.

• Создайте таблицу по предлагаемому образцу с таким же чис­лом строк и столбцов

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

• Введите нумерацию в первом столбце таблицы, воспользовав­шись маркером заполнения.

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

2 этап.

Заключается в заполнении таблицы, сортировке данных и ис­пользовании различных форматов числа.

• Заполните столбцы «Наименование», «Кол-во» и «Цена» по своему усмотрению.

• Установите денежный формат числа в тех ячейках, в которых размещены суммы, и требуемое число десятичных знаков, если они нужны.

• В нашем случае это пустые ячейки столбцов «Цена» и «Сум­ма». Их можно выделить и выполнить команду Главная – Ячейки – Формат - Формат ячеек выбрать вкладку Число, категорию Денежный, а в поле Обо­значение - р. (рубли) (или вкладка Главная, подгруппа Число, значок Числовой формат, Денежный). Это даст вам разделение на тысячи, чтобы удобнее было ориентироваться в крупных суммах.

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

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

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

• Отсортируйте записи по алфавиту.

• Для этого выделите все строки таблицы, кроме первой (заголов­ка) и последней («Итого»), можно не выделять и нумерацию.

• Выполните команду вкладка Данные - подгруппа Сортировка и фильтр – значок Сортировка, выбери­те столбец, по которому нужно отсортировать данные (в нашем слу­чае это столбец В, так как именно он содержит перечень товаров, подлежащих сортировке), и нажмите значок «По возрастанию».

3-й этап.

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

• Наберите необходимый текст и после таблицы. Следите за вы­равниванием. Обратите внимание, что текст «Дата получения « » 200____г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец нашей таблички), только применено выравнива­нию вправо. Текст «СЧЕТ №» внесен в ячейку самого левого столбца, и при­менено выравнивание по центру выделения (предварительно вы­делены ячейки одной строки по всей ширине таблицы счета). Вся остальная текстовая информация до и после таблицы вне­сена в самый левый столбец, выравнивание влево.

Грузоотправитель и адрес

Грузополучатель и адрес

К Реестру № Дата получения «___»_____________200___г.

СЧЁТ№ 123 от 13.08.02

Поставщик Торговый дом Пресненский

Адрес: 123456, Москва, ул. Рочдельская, 4

Р/счет № 456789 в АВС-банке, МФО 98765,4

Дополнения:

Наименование

Ед. измерения

Кол-во

Цена

Сумма

1.

2.

ИТОГО

Руководитель предприятия Чижов Е. Ю.

Главный бухгалтер Стасова А. И.

Упражнение №6 Абсолютные ссылки

Содержание работы: Загрузите Ехсеl.

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

• В ячейку АЗ введите число 1, в ячейку А4 - число 2, выделите обе ячейки и протащите маркер выделения вниз, чтобы заполнить столбец числами от 1 до 9. Аналогично заполните ячейки В2 - К2 числами от 0 до 9.

• После заполнения строчки числами от 0 до 9 вы увидите, что все необходимые для работы ячейки одновременно не видны на экране. Сузьте их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой).

0

1

2

3

4

5

6

7

8

9

1

100

121

144

169

196

225

256

289

324

361

2

400

441

484

529

576

625

676

729

784

841

3

900

961

1024

1089

1156

1225

1296

1369

1444

1521

4

1600

1681

1764

1849

1936

2025

2116

2209

2304

2401

5

2500

2601

2704

2809

2916

3025

3136

3249

3364

3481

6

3600

3721

3844

3969

4096

4225

4356

4489

4624

4761

7

4900

5041

5184

5329

5476

5625

5776

5929

6084

6241

8

6400

6561

6724

6889

7056

7225

7396

7569

7744

7921

9

8100

8281

8464

8649

8836

9025

9216

9409

9604

9801

В ячейку ВЗ нужно поместить формулу, которая возводит в квад­рат число, составленное из десятков, указанных в столбце А, и еди­ниц, соответствующих значению, размещенному в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячей­ке ВЗ, можно задать формулой =АЗ*10+В2 (число десятков, умно­женное на десять плюс число единиц). Остается возвести это чис­ло в квадрат. Попробуйте ставить функцию.

Для этого выделите ячейку, в которой разместится результат вычислений (ВЗ), и выполните команду вкладка Формулы – группа Библиотека функций – значок Вставить функцию. Из предложенных категорий функций выберите Математиче­ские, выберите функцию - Степень, нажмите кнопку ОК.

В следующем диалоговом окне введите в поле Число основание степени =АЗ*10+В2 и в поле Степень показатель степе­ни-2.

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

В этом же диалоговом окне можно увидеть значение самого чис­ла (10) и результат вычисления степени (100). Остается только на­жать кнопку ОК. В ячейке ВЗ появится результат вычисления.

Хотелось бы распространить эту формулу на остальные ячей­ки таблицы. Выделите ячейку ВЗ и заполните, протянув маркер выделения вправо, соседние ячейки. Что произошло? Почему результат не оправдал наших надежд?

В ячейке СЗ не видно числа, так как оно не помещается целиком в ячейку. Расширьте мышью столбец С. Число появится на экране, но оно явно не соответствует квадрату числа 11. Почему? Дело в том, что если мы распространили формулу впра­во, то Ехсеl автоматически изменил с учетом нашего смещения ад­рес ячеек, на которые ссылается формула, и в ячейке СЗ возводит­ся в квадрат не число 11, а число, рассчитанное по формуле =ВЗ*10+С2.

Во всех предыдущих упражнениях нас вполне устраивали отно­сительные ссылки на ячейки таблицы (при перемещении формулы по такому же закону смещаются и ссылки), однако здесь возникла необходимость зафиксировать определенные ссылки, т. е. указать, что число десятков можно брать только из столбца А, а число еди­ниц - только из строки 2 (для того чтобы формулу можно было рас­пространить вниз). В этом случае применяют абсолютные ссылки. Для фиксирования любой позиции адреса ячейки перед ней ста­вят знак $. Таким образом, верните ширину столбца С в исходное положе­ние и выполните следующие действия.

Выделите ячейку ВЗ. Установите текстовый курсор в Строку формул перед ссылкой, которую будете изменять, и исправьте имеющуюся формулу =СТЕПЕНЬ(АЗ*10+В2;2) на правильную =СТЕПЕНЬ($АЗ*10+В$2;2)

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

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

Задания для тренировки:

Задание 1. Составьте таблицу умножения от 1

0

1 23456789

0

0

000000000

1

0

123456789

2

0

2 4 6 8 10 12 14 16 18

3

0

3 6 9 12 15 18 21 24 27

4

0

4 8 12 16 20 24 28 32 36

5

0

5 10 15 20 25 30 35 40 45

6

0

6 12 18 24 30 36 42 48 54

7

0

7 14 21 28 35 42 49 56 63

8

0

8 16 24 32 40 48 56 64 72

9

0

9 18 27 36 45 54 63 72 81

Задание 2. Оформите таблицу, в которую внесена раскладка продуктов на одну порцию. Введите общее число порций (Всего пор­ций), например 100, в отдельную ячейку.

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

ПЛОВ ИЗ КАЛЬМАРОВ

Продукт

Раскладка на 1 порцию (г)

Всего (г)

Кальмары

48

Лук репчатый

17

Морковь

9

Рис

12

Упражнение № 7 Введение понятия «Имя ячейки»

Содержание работы: Загрузите Ехсеl .

Упражнение 1. Введение понятия «имя ячейки».

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

• Подготовьте таблицу, состоящую из столбцов: «Наименование товара», «Эквивалент $ US», «Цена в р.». Заполните все столбцы, кроме «Цена в р.». Столбец «Наименование товара» заполните тек­стовыми данными (перечень товаров по вашему усмотрению), а столбец «Эквивалент $ US» - числами (цены в долларах).

Понятно, что в столбце «Цена в р.» должна разместиться фор­мула: «Эквивалент $ US».

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

Наименование товара

Эквивалент $ US

Цена в р.

Кресло рабочее

39

60450

Стеллаж

35

54250

Стойка компьютерная

60

93000

Стол приставной

42

65100

Стол рабочий

55

100750

Стул для посетителей

20

31000

Тумба выкатная

65

100750

Шкаф офисный

82

127100

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

Как задавать абсолютные ссылки, мы уже знаем. Однако суще­ствует еще один способ: ссылаться не на адрес ячейки, а на имя, которое можно присвоить ячейке.

• Выделите ячейку, в которую будет вводиться курс доллара (выше таблицы), введите в нее значение курса доллара на сегодняшний день и выполните команду Вкладка Формулы – подгруппа Определенные имена – значок Присвоить имя. В появившемся диалоговом окне введите имя ячейки (Если имя ячейки состоит из нескольких слов, то вместо пробела обязательно должен стоять знак подчеркивания _) и нажми­те кнопку ОК. Обратите внимание на то, что в строке формул в поле «Имя» вместо адреса ячейки теперь размещено ее имя.

• В ячейку, расположенную левее ячейки «Курс_доллара», можно ввести текст «Курс доллара».

• Теперь остается ввести формулу для подсчета цены в рублях.

• Для этого выделите самую верхнюю пустую ячейку столбца «Цена в р.» и введите формулу следующим образом: введите знак =, затем щелкните мышью по ячейке, расположенной левее (в которой размещена цена в дол.), после этого ведите знак * и щелк­ните по ячейке, в которой записан сегодняшний курс (1550). Формула должна выглядеть так: =В7*Курс доллара.

• Заполните формулу вниз, воспользовавшись услугами марке­ра заполнения.

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

• Оформите заголовок таблицы: выровняйте по центру, приме­ните полужирный стиль начертания шрифта, расширьте строку и примените вертикальное выравнивание по центру, воспользовав­шись вкладкой Главная – подгруппой Выравнивание –значок Выровнять по середине. Измените ширину столбцов. Выделите таблицу и задайте для нее обрамление.

Упражнение 2. Вставка ежедневно меняющейся даты.

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

• Выбрать вкладу Формулы, подгруппу Библиотека стилей, значок Дата и время, команду Сегодня. Можно разместить в этом документе также рисунок, характе­ризующий направление торговли. Для этого необходимо выполнить такие же действия, как в ре­дакторе Word. Во вкладке Вставка, подгруппа Иллюстрации, значок Клип, команда Начать вы­берите подходящий рисунок. Рисунок, вставленный в Ехсеl, можно перемещать мышью по до­кументу, а также изменять традиционным способом его ширину и высоту. Изменить формат рисунка, цвет линий форму рисунка, добавить эффекты и др. можно в появившейся вкладке работы с рисунками Формат. Можно вставить не рисунок, а логотип (фирменный знак) пред­приятия, воспользовавшись для этого возможностями WordArt (ана­логично тому, как делали это в редакторе Word). В заключение можно отсортировать товары по алфавиту.

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

Измените таблицу таким образом, чтобы вместо одного столбца «Цена в р.» были два столбца «Цена в р. по безналичному расчёту» и «Цена в р. за наличный расчет». Соответственно у вас вместо одной именной ячейки «Курс_доллара» бу­дет две именные ячейки, отражающие коэффициенты.

Упражнение № 8 Связь между листами рабочей книги, удаление, переименование листов

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

1. Загрузите Ехсеl.

Подготовка ведомости на выдачу заработной платы (уп­рощенный вариант).

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

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

• на первом разместим сведения о начислениях;

• на втором - ведомость на выдачу заработной платы;

• а на третьем - ведомость на выдачу компенсаций на детей.

• На отдельном листе будет построена диаграмма.

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

Для этого щелкните мы­шью по ярлычку 3-го листа, затем, воспользовавшись правой кнопкой мыши, Добавьте лист. Теперь стали ярлычки четырех листов. Активен (ярлычок выделен цветом) Лист 1. Именно на нем нач­нем создавать таблицу.

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

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

Фамилия, имя, отчество

Оклад

Налоги

Сумма к выдаче

Количество детей

профс.

пенс.

подох.

1

2

3

4

Примените следующие операции:

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

• изменение ширины столбца (в зависимости от объема вводи­мой информации);

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

• задание формата числа «денежный» для ячеек, содержащих сум­мы. Можно сделать это до ввода данных в таблицу (выделить соот­ветствующие ячейки и установить для них формат числа «денежный»);

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

• распространение формулы вниз по столбцу и в некоторых слу­чаях вправо по ряду;

• заполнение таблицы текстовой и фиксированной числовой ин­формацией (столбцы «ФИО», «Оклад», «Количество детей»;

• сортировку строк (сначала отсортировать по фамилиям по ал­фавиту, затем отсортировать по суммам).

Для форматирования формул понадобится дополнительная ин­формация. Профсоюзный и пенсионный налоги (взносы) составля­ют по 1 % от оклада. Удобно ввести формулу в одну ячейку, а затем распространить ее на оба столбца. Самое важное - не забыть про абсолютные ссылки, так как и профсоюзный и пенсионный налоги нуж­но брать от оклада, т. е. ссылаться только на столбец «Оклад». При­мерный вид формулы: =$СЗ*1 %, или =$СЗ*0,01, или =$С3*1/100. По­сле ввода формулы в ячейку D3 ее нужно распространить вниз (про­тянув за маркер заполнения) и затем - вправо на один столбец.

Подоходный налог подсчитываем по формуле: 12 % от оклада за вычетом минимальной заработной платы и пенсионного налога. Примерный вид формулы: =(СЗ-ЕЗ-75900)*12 %, или =(СЗ-ЕЗ-75900)*12/100, или =(СЗ-ЕЗ-75900)*0,12. После ввода формулы в ячейку FЗ ее нужно распространить вниз.

Для подсчета «Суммы к выдаче» примените формулу, позволяю­щую вычислить разность оклада и налогов. Примерный вид фор­мулы: =СЗ-D3-ЕЗ-РЗ, размещенной в ячейке GЗ и распространен­ной вниз.

Заполняйте столбцы «Фамилия, имя, отчество», «Оклад» и «Чис­ло детей» после того, как введены все формулы. Результат будет вы­числяться сразу же после ввода данных для заполнения таблицы.

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

Фамилия, имя, отчество

Оклад

налоги

Сумма к выдаче

Количество детей

Профс.

Пенс.

подох

1.

Иванов А.Ф.

230000

2300

2300

18216

207184

1

2.

Иванова Е.П.

450 000

4500

4500

44352

396 648

2

3.

Китова В. К.

430 000

4300

4300

41 976

379 424

0

4.

Котов И. П.

378 000

3780

3780

35798

334 642

0

5.

Круглова А.Д.

230 000

2300

2300

18216

207184

1

6.

Леонов И. И.

550 000

5500

5500

56232

482 768

3

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

Поскольку мы в дальнейшем будем работать сразу с нескольки­ми листами, имеет смысл переименовать их ярлычки в соответст­вии с содержимым. Переименуем активный в настоящий момент лист. Для этого выполните правой кнопкой мыши на Лист1 - Переимено­вать - вместо Лист 1 введите новое название листа - Начисления

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

Построим диаграмму, отражающую начисления каждого сотруд­ника. Понятно, что требуется выделить два столбца таблицы: «Фа­милия, имя, отчество» и «Сумма к выдаче». Но эти столбцы не рас­положены рядом, и традиционными способами мы не сможем их выделить. Однако, если удерживать нажатой клавишу Ctrl, то мож­но одновременно выделить ячейки в разных местах таблицы.

Выделите заполненные данными ячейки таблицы, относящие­ся к столбцам «Фамилия, имя, отчество» и «Сумма к выдаче».

Во вкладке Вставка – подгруппа Диаграммы - выберите тип диаграммы - Круговая, Объемная. Далее в подгруппе Расположение (вкладка Конструктор –Работа с диаграммами) нажмите кнопку Переместить диаграмму и укажите на отдельном лис­те – ОК.

Перед листом «Начисления» появится новый лист Диаграмма 1.

Для того чтобы проверить, какая связь существует между таблицей начислений и диаграммой, перейдите на лист Начисления, в середину таблицы вставьте новую строку (выделите строку таб­лицы и выполните команду Вставка - Строки). Распространите на новую строку формулы, заполните данные нового сотрудника. Те­перь перейдите на лист Диаграмма 1 и проверьте, как новые дан­ные отразились на диаграмме - новый сотрудник сразу же внесен в диаграмму.

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

Перейдите к Листу 2. Сразу же переименуйте его в Детские. Мы хотим подготовить ведомость, поэтому в ней будут три столб­ца: «Ф.И.О.», «Сумма» и «Подпись».

• В графу «Ф.И.О.» нужно поместить список сотрудников, кото­рый мы имеем на листе Начисления. Можно скопировать на одном листе и вставить на другой, но хотелось бы установить связь между листами (как это выполняется для диаграммы и листа начислений). Для этого на листе Детские поместим формулу, по которой данные будут вставляться из листа Начисления.

• Выделите ячейку А2 листа Детские и введите формулу: =Начисления!ВЗ, где имя листа определяется восклицательным зна­ком, а ВЗ - адрес ячейки, в которой размещена первая фамилия сотрудника на листе Начисления. Можно набрать формулу с кла­виатуры, а можно после набора знака равенства перейти на лист Начисления, выделить ячейку, содержащую первую фамилию и нажать Enter (не возвращаясь к листу Детские).

• Перейдите на лист Детские, проверьте полученную формулу и распространите ее вниз, воспользовавшись маркером заполнения. Список фамилий сотрудников теперь есть и на листе «Детские». Больше того, если внести новые данные в таблицу начислений, то они отразятся и на листе Детские. (Нужно будет только распространить формулу ниже в случае необходимости.)

• В графе «Сумма» аналогичным образом нужно разместить фор­мулу =Начисления!НЗ*53130, где НЗ адрес первой ячейки на лис­те Начисления, содержащей количество детей. Заполните эту фор­мулу вниз и примените денежный формат числа.

• Выполните обрамление таблицы.

• Для того чтобы список состоял только из сотрудников, имею­щих детей, установите фильтр по наличию детей (вкладка Главная – подгруппа Редактирование – значок Сортировка и фильтр – команда Фильтр), в раскрывающемся списке «Сумма» в числовых фильтрах окна Фильтр вы­берите больше и установите критерий Больше 0.

Приблизительный вид ведомости:

Сумма

Подпись

Иванов А.Ф.

53130

Иванова Е.П.

1 06 260

Чудов А. Н.

106260

Круглова А.Д.

53 130

Леонов И. И.

106260

• Осталось поместить выше таблицы заголовок ведомости (для этого понадобится вставка дополнительных строк) и можно распе­чатать.

Задание для самостоятельной работы:

В нашей рабочей книге осталось оформить последний лист -ведомость на выдачу заработной платы. Таблица должна содер­жать следующие столбцы: «Ф. И. О», «Сумма к выдаче» и «Под­пись».

Сумма к выдаче

Под­пись

Иванов А.Ф,

207184

Иванова Е.П.

396 648

Китова В. К.

379 424

Котов И. П.

334 642

Чудов А.Н.

379 424

Круглова А.Д.

207 184

Леонов И. И.

491 380

• Переименуйте Лист 3 в К выдаче.

• Отформатируйте заголовок таблицы.

• Вставьте фамилии сотрудников формулой со ссылкой на лист Начисления (как на листе Детские). Заполните формулу вниз.

• Вставьте суммы аналогичным образом (ссылка на лист Начис­ления).

• Выполните обрамление таблицы, как показано на следующем образце:

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

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

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

Упражнение № 9 Создание бланка - шаблона

Создание бланка-шаблона.

1. Фамилия,

   имя, отчество

   Профессия

   Разряд

   Числа месяца

   Дни явок

   Дни неявок

   Отработано часов

   1

   2

   3

   …

   29

   30

   31

   отпуск

   болезнь

   прогул

   1

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   2

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   10

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

   
   

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

2. Сформируйте заголовок табеля учета рабочего времени за текущий месяц и подготовьте таблицу-бланк по образцу

Введите числа месяца с 1-го по 31-е. Для столбцов, содержа­щих даты, установите ширину столбца, равную 2.

Если на вашем предприятии постоянный состав сотрудников, то внести в шаблон фамилии и профессии.

3. Файл сохраните в свою папку под именем Табель (при сохранение, в списке Тип файла установите Шаблон)

Применение шаблона.

Для создания нового файла с применением шаблона выполни­те следующие действия:

• Дни явок

  Дни неявок

  Отработано часов

  23

  24

  25

  26

  27

  28

  29

  30

  31

  
  

  
  

  отпуск

  болезнь

  прогул

  
  

  
  

  
  

  8

  8

  8

  8

  8

  
  

  
  

  8

  
  

  
  

  
  

  
  

  
  

  
  

  8

  8

  8

  8

  8

  
  

  
  

  8

  
  

  
  

  
  

  
  

  
  

  
  

  4

  4

  4

  4

  4

  
  

  
  

  4

  
  

  
  

  
  

  
  

  
  

  
  

  б

  б

  б

  б

  б

  
  

  
  

  б

  
  

  
  

  
  

  
  

  
  

  
  

  0

  0

  0

  0

  0

  
  

  
  

  8

  
  

  
  

  
  

  
  

  
  

  
  

  8

  8

  8

  п

  8

  
  

  
  

  8

  
  

  
  

  
  

  
  

  
  

  В меню Файл выберите Создать. В списке Создать диалогового окна Создание документа вы­делите шаблон Табель, на основе которого хотите создать новую книгу. Выберите кнопку ОК.

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

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

Проставьте для каждого сотрудника:

• количество часов, отработанных за день, или о, если он находился в отпуске, или б, если в этот день сотрудник болел, или п, если прогуливал

Имея такую широкую таблицу, можно столкнуться с неудобства­ми при заполнении. Дело в том, что, перемещаясь вправо для за­полнения таблицы, вы теряете из виду столбец с фамилиями, и ста­новится трудно определить, кому из сотрудников проставляете ра­бочие часы. Ехсеl позволяет зафиксировать заголовок на стра­нице, чтобы при перемещении нужные столбцы (или строки) оста­вались на своем месте. Для того чтобы зафиксировать столбец «Фамилия»:

• выделите столбец справа от столбца «Фамилия» («Профессия»);

• во вкладке Вид, подгруппы Окно, значок Закрепить области, выберите команду Закрепить области;

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

Дни явок

Дни неявок

Отработано часов

23

24

25

26

27

28

29

30

31

отпуск

болезнь

прогул

8

8

8

8

8

8

22

0

176

8

8

8

8

8

8

22

0

176

4

4

4

4

4

4

22

0

88

б

В

б

б

б

б

16

0

128

0

0

0

0

0

я

17

5

136

8

8

8

п

8

8

21

0

168

• чтобы зафиксировать горизонтальные заголовки, выделите строку ниже заголовков;

• чтобы зафиксировать вертикальные заголовки, выделите стол­бец справа от заголовков;

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

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

Самостоятельно введите формулу суммирования соответствую­щих ячеек строки для подсчета отработанных часов (используйте вкладка Формулы, подгруппа Библиотека функций, категория Математические, функция СУММ). Заполните формулу вниз.

Для подсчета дней явок необходимо в каждой строке (для каж­дого сотрудника) подсчитать количество ячеек, содержащих числа (не суммируя их). Для этого:

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

• во вкладке Формулы, подгруппа Библиотека функций, значок Другие функции, категория Статисти­ческие, функция СЧЕТ).

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

• Заполните формулу вниз.

Для подсчета количества дней, проведенных в отпуске, вставь­те функцию СЧЕТЕСЛИ и, в качестве критерия введите образец (что нужно подсчитывать) русскую букву «о» (обязательно в кавыч­ках), т. е. тот символ, который вы вносили в таблицу, отмечая отпуск. Заполните формулу вниз по столбцу.

Задания для тренировки:

1. Самостоятельно введите формулу для подсчета количества дней, пропущенных по болезни (функция СЧЕТЕСЛИ, критерий «б»).

2. Самостоятельно введите формулу для подсчета количества прогулов (функция СЧЕТЕСЛИ, критерий «п»).

3. Самостоятельно в отдельную ячейку введите формулу для под­счета количества нерабочих дней месяца. Вставьте для этого функцию СЧИТАТЬ ПУСТОТЫ, так как в такие дни в рабочем табеле ничего не отмечено.

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

5. Сохраните документ следуя инструкции: кнопка Office, Сохранить как, книга Excel, тип файла Шаблон Excel.

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

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

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

• выделите ярлычок листа текущего месяца;

• в контекстном меню ярлычка листа (вызывается щелчком пра­вой кнопки мыши по ярлычку листа) выберите Вставить;

• выделите шаблон Табель, лист которого хотите вставить; выберите кнопку ОК.

Новый лист вставился перед исходным. Переместите новый лист (схватив за ярлычок) после исходного, переименуйте и заполните данными.