Пример разработки таблицы.

В этом разделе содержится пример применения программы Ехсel для решения задачи о начислении заработной платы сотрудникам некоторого отдела.

Имеется коллектив сотрудников из пяти человек, которым начисляется зарплата один раз в месяц. За истекший месяц отделу дали премию, которая составляет 40% от зарплаты каждого человека. Подоходный налог будем брать с общей суммы, причитающейся каждому человеку, следующим образом: сумма 83,49 руб. — не облагается налогом, с оставшейся суммы берется налог — 12% (если зарплата плюс премия не превосходят 83,49 руб., подоходный налог не берется вообще). Мы должны подсчитать сумму, которую получит на руки каждый человек. Она составляется следующим образом: зарплата + премия ‑ подоходный налог. Кроме того, нас будут интересовать следующие сведения: суммарные зарплата, премия, подоходный налог и общая сумма, выплаченная всему отделу, а также максимальные и средние те же значения.

Таблица будет иметь следующий вид:

№ п/п	Ф.И.О.	з/плата	премия	под/налог	к выдаче
Итого
Максимальное значение
Среднее значение

Ясно, что второй столбец таблицы заполняется текстовой информацией, первый и третий столбцы — исходными данными-числами, в 4-й, 5-й, 6-й столбцы будут помещены формулы, при этом после занесения формулы в соответствующей ячейке высвечивается результат.

Итак, запустим Ехсеl. Перед Вами появится пустой рабочий экран, причем будет выделена левая верхняя клетка с именем А1.

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

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

Теперь введем "шапку" таблицы:

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

№ п/п

2) в ячейку В1 поместим заголовок второго столбца:

Ф. И. О.

(на ширину столбцов А, В, С, D и т.д. внимания не обращаем);

3)в ячейку С1 поместим заголовок третьего столбца:

З/плата

4)продолжим заполнение «шапки» в столбцах D, E, F (выполняется аналогично пунктам 1 - 3).

"Шапка" есть; теперь по набранной "шапке" редактируем ширину столбцов таблицы — A, В, С, D, E, F. Отредактируем сначала ширину столбца А. Для этого поставим курсор мыши на правую линию столбца А, на линейке имен (граница между столбцами А и В). При этом курсор превратится в перекрестие с направленными в разные стороны стрелками. Теперь Вы можете, держа нажатой кнопку мыши, передвигать эту линию вправо или влево, соответственно уменьшая или увеличивая ширину столбца.

Аналогично изменяем ширину остальных столбцов таблицы.

Теперь будем заполнять таблицу.

1) Сначала заполним первый и второй столбцы. Выделим ячейку А2 и нажмем на клавишу 1 (первый номер по порядку), затем Enter либо клавишу Стрелка вправо (напомним, что одной их этих клавиш заканчивается каждый ввод). Затем выделим ячейку В2 и занесем туда первую фамилию и инициалы, например, Иванов И.И. Аналогично заполним столбцы А и В для 5 человек.

2) Далее заполним столбец С — занесем туда зарплату для каждого сотрудника.

3) В столбец D заносится премия, которая вычисляется по следующей формуле (для первого сотрудника): D2=С2*40%.

Итак, сделаем активной ячейку D2 и запишем формулу в следующем виде:

=С2*40%

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

Аналогичными формулами нужно заполнить клетки D3:D6 (только в формуле С2 будет соответственно изменяться на СЗ:С6). Можно не вводить эти формулы вручную, а скопировать формулу, находящуюся в клетке D2 (при копировании формула будет скорректирована). Выделите клетку, подлежащую копированию (D2). Справа внизу на рамке, показывающей выделенную ячейку, находится маленький черный квадрат. Поставьте на него курсор, и он превратится в маленький черный крест. Не отпуская левую кнопку мыши, водите курсор вниз. Когда отметите нужные Вам ячейки (D3:D6), отпустите кнопку мыши, и Ехсеl автоматически заполнит помеченные ячейки нужными Вам формулами. Итак, формула скопирована, а в ячейках D3:D6 появится результат, вычисленный по соответствующим формулам. Если мы посмотрим содержимое ячейки DЗ, то убедимся, что скопированная формула была скорректирована. Она будет иметь следующий вид: =СЗ*40%. Аналогичная корректировка произошла во всех клетках, в которые мы поместили копируемую формулу.

4) Теперь будем заполнять столбцы Е и F. Сначала заполним их для первого сотрудника (Иванова И.И.), т.е. будем заполнять ячейки Е2 и F2.

В ячейку Е2 введем следующую формулу

=ЕСЛИ(С2+D2>83.49;(С2+D2-83.49)*12%;0)

т.е. если зарплата + премия превосходят 83.49 руб., то с суммы, превосходящей 83,49 руб., берем подоходный налог 12%, иначе подоходный налог не берется — в ячейку заносим 0.

В ячейку F2 поместим формулу

=С2+D2-Е2

т.е. на руки сотрудник получит следующую сумму:

з/плата + премия - под/налог.

Для следующих четырех человек произведем копирование содержимого ячеек Е2 и F2 (аналогично способу, описанному в п.З).

5) Подводим итоги:

Итого

Максимальное значение

Среднее значение

Для этого сначала в ячейку А7 помещаем следующий текст:

Итого

Затем в ячейку А8 — Максимальное значение, а в ячейку А9 — Среднее значение.

6)"Итого" будем вычислять с помощью функции СУММ. Сначала вычислим суммарную зарплату. Воспользуемся функцией автоматической суммы. Для этого поставьте курсор в ту ячейку, в которую Вы хотите поместить результат, т.е. в ячейку С7. Нажмите знак суммы «∑» на линейке инструментов. С помощью мыши (удерживая кнопку мыши) выделите ячейки таблицы, сумму значений который Вы хотите вычислить, т.е. С2:С6. Эти ячейки будут выделяться бегущей пунктирной рамкой; в строке формул и в самой ячейке появится формула =Сумм(С2:С6) — т.е. сумма содержимого всех ячеек таблицы, начиная с С2 и кончая С6. Если Вы набранную формулу введете, то в ячейке С7 получите результат.

Для столбцов D, Е, F используем команду копирования содержимого ячейки С7 (копирование выполняется аналогично описанному выше способу).

7) "Максимальное значение" вычисляем с помощью функции МАКС. Перед вводом функции выделите ячейку С8, куда будет помещен результат. Вы можете ввести эту функцию через диалоговое окно, называемое Мастером функций, которое можно открыть с помощью кнопки линейки инструментов Мастер функций; на ней изображено следующее: «f_х». В левом списке диалогового окна находятся имена тематических групп. Щелкнув на имени Статистические (функции), в правой части Вы получите список имен функций, содержащихся в данной группе. В правом окне найдите функцию МАКС. Функцию можно вызвать, если пометить се и щелкнуть на кнопке ОК. Для подстановки значения аргумента выделите блок, в котором находятся данные для подстановки, а именно: С2:С6. Адрес этого блока появится на месте помеченного аргумента.

8) "Среднее значение" вычисляем с помощью статистической функции СРЗНАЧ аналогично способу, рассмотренному в пункте 7 для функции МАКС.

9) Заполним ячейки D8:F9 формулами для вычисления максимальных и средних значений, скопировав их из столбца С.

Таблица готова. Оформим ее, выбрав один из видов, предлагаемых Ехсеl. Функция Автоформат... находится с меню Формат. Перед вызовом этой функции выделите свою таблицу.

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

Пересортируем таблицу в алфавитном порядке по фамилиям (вместе с относящимися к ним данными). Для этого выделим блок В1:F6 (начиная с ячейки В1) и нажмем на кнопку панели инструментов По возрастанию

А
Я

Для наглядности изобразим данные таблицы в виде диаграммы.

Выведем в виде диаграммы значения блока С2:F6.

Перед тем как строить график или диаграмму по данным, необходимо выделить эти данные. Итак, сначала выделим блок С2:F6.

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

Готовую диаграмму можно растягивать, сужать или перемещать по экрану с помощью мыши. Для перемещения графического объекта подведите курсор к контуру, и он превратится в белую стрелку. Теперь нажмите левую кнопку мыши — объект станет выделенным — и передвигайте картинку в нужное место.

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

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

Для выхода из программы Ехсel войдем в главное меню и выберем элемент Файл, а затем Выход.

ПРОГРАММА РАБОТЫ

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

3.1.1. Выполните пример разработки таблицы, описанный в п.2.

3.1.2. Дополните таблицу новыми сведениями, выполнив п.3.1.2.1.—3.1.2.6.

3.1.2.1. Вставьте в таблицу перед колонкой "к выдаче" колонку "аванс" и занесите туда какие-нибудь значения. Формула для вычисления значений последнего столбца — "к выдаче" — теперь будет зависеть и от выданного ранее аванса. Запишите ее.

3.1.2.2. Дополните таблицу сведениями о трех новых сотрудниках.

3.1.2.3. Выполните сортировку таблицы по убыванию сумм к выдаче.

3.1.2.4. Измените зарплату первому по списку, третьему и четвертому сотрудникам. Посмотрите, как изменятся остальные значения.

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

3.1.2.6. Запишите новую таблицу на диск. Таблица, разработанная в п.3.1.1. также должна остаться на диске.

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

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

3.2.1.1.средний балл каждого студента;

3.2.1.2. средний балл группы по каждому экзамену;

3.2.1.3. абсолютную успеваемость группы;

3.2.1.4. относительную успеваемость группы;

3.2.1.5. качество знаний.

3.2.2.Выполните сортировку таблицы по убыванию средних баллов студентов.

3.2.3.Выведите в виде графика сведения о средних баллах группы по каждому предмету.

3.2.4.Запишите созданную таблицу в свою папку.

3.2.5.Закончите работу с Excel.

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

3.3.1. Создать таблицу следующего содержания:

№ п/п	Ф.И.О.	Оклад	Премия в %	Оклад + премия	Подох. налог	Остаток с прошлого месяца	К выдаче	Отметка о получении (1 ‑ получил, 0 ‑ не получил)

В девятой колонке Отметка о получении (1 - получил, 0 - не получил) ставится единица тем, кто получил деньги за текущий месяц, и ноль — тем, кто не смог получить, для того, чтобы эта сумма (если он не получил) учитывалась в следующем месяце.

Первый лист содержит следующие данные за январь.

	Иванов П.
	Серова Г.
	Петрова Г.
	Сидоров И.
	Молчан П.
	Ветров Н.
	Петров Л.
	Лимонов Д.
	Хорунжий Г.
	Петренко П.
	Васильев П.
	Чалый Р.
	Широков В.
	Добрый К.
	Житков М.
	Смелый Ю.
	Иванов И.
	Сидоров А.
	Тарасов Д.
	Москвин Я.

3.3.2. Создать для остальных месяцев еще 11 таблиц на отдельных листах. Необходимые для этих таблиц данные брать с первого листа. Т.е. второй столбец в этих таблицах не заполнять, а использовать ссылку на таблицу первого листа (например, для ячейки В4 это будет выглядеть так: =Лист!В4). Значения остальных столбцов можно скопировать через буфер памяти. В этих таблицах седьмая колонка должна заполняться автоматически, в зависимости от того, получен ли в предыдущем месяце сотрудник деньги (девятый столбец) или нет (например, для ячейки G4 третьего листа это будет выглядеть так: =Если(Лист2!I4=0;Лист2!Н4;0)). В девятой колонке этих одиннадцати таблиц ставить ноль следующим сотрудникам:

во втором месяце — второму и третьему;

в третьем месяце — третьему и четвертому;

………………………………………………..

в двенадцатом месяце — двенадцатому и тринадцатому.

3.3.3. На тринадцатом листе создать еще одну таблицу следующего содержания:

№ п/п	Ф.И.О.	Зарплата за год	Дополнительный налог за годовой доход

Для получения зарплаты за год необходимо сложить значения пятого столбца по всем листам (например, для ячейки С4 это будет выглядеть так:

=Лист1!Е4+Лист2!Е4+ЛистЗ!Е4+Лист4!Е4+Лист5!Е4+Лист6!Е4+

Лист7!Е4+Лист8!Е4+Лист9!Е4+Лист10!Е4+Лист11!Е4+Лист12!Е4

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

с 12000р. по 30000р. дополнительный налог составляет 3 % от годового дохода;

с 30001р. по 50000р. — 5 % от годового дохода;

с 50001р. по 75000р. — 9 % от годового дохода;

с 750001р. по 100000р. — 15 % от годового дохода.

Четвертый столбец удобно формировать с помощью мастера функций вложенными ЕСЛИ. Например, для ячейки D4 формула будет выглядеть так:

=Если(С4>=750001;С4*!5%;Если(С4>=50001;С4*9%;

Если(С4=30001;С4*5%;Если(С4>=12000;С4*3%;0))))

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

3.3.5. Подсчитайте сумму третьего и четвертого столбцов отдельно.

3.3.6. Для третьего и четвертого столбцов рядом с таблицей постройте круговую и столбиковую диаграммы.

3.3.7. Сохраните таблицу на диске.