Минимальная заработная плата – 400 руб.

№ п/п

Ф. И. О.

з/плата

премия

под/налог

к выдаче

 

Итого

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

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

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

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

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

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

Запишем сначала название таблицы. Т.к. оно должно быть симметрично таблице, выделим блок А1:F1 (5 колонок) и объединим их, нажав на кнопку ленты Главная. В эту объединенную ячейку и поместим название таблицы, а именно:

НАЧИСЛЕНИЕ ЗАРАБОТНОЙ ПЛАТЫ

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

Минимальная заработная плата –

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

после чего наложим на эту ячейку денежный формат, нажав на кнопку ленты Главная.

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

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

№ п/п

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

Ф. И. О.

(на ширину столбцов A, B, C, D и т.д. пока внимания не обращаем);

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

З/плата

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

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

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

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

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

Номера по-порядку можно ввести и другим способом, используя функцию заполнения. Для этого:

a) выделите ячейку А4 и занесите туда 1;

b) выделите ячейку А5 и занесите туда 2;

c) выделите блок А4:А5;

d) подведите курсор мыши с левому нижнему углу блока (к маленькому квадратику). Курсор превратиться в маленький черный крестик;

e) зажмите левую кнопку мыши и протяните ею вниз еще на три ячейки. После того, как Вы отпустите кнопку мыши, в ячейках появятся следующие по порядку числа, а именно 3, 4, 5.

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

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

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

=С4*40%

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

Аналогичными формулами нужно заполнить ячейки D5:D8 (только в формуле C4 будет соответственно изменяться на С5–С8). Можно не вводить эти формулы вручную, а опять же использовать функцию заполнения. Выделите ячейку D4 (при использовании этой функции формула будет скорректирована). Справа внизу на рамке, показывающей выделенную ячейку, находится маленький черный квадрат. Поставьте на него курсор и он превратится в маленький черный крест. Не отпуская левую кнопку мыши, ведите курсор вниз. Когда отметите нужные Вам ячейки (D5:D8), отпустите кнопку мыши, и Excel автоматически заполнит помеченные ячейки нужными Вам формулами. В ячейках D5:D8 появится результат, вычисленный по соответствующим формулам. Если мы посмотрим содержимое ячейки D5, то убедимся, что исходная формула была скорректирована. Она будет иметь следующий вид: =С5*40%. Аналогичная корректировка произошла во всех ячейках, в которые мы поместили исходную формулу.

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

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

=ЕСЛИ(С4+D4>$F$2;(C4+D4-$F$2)*13%;0)

т.е. если зарплата + премия превосходят 400 руб., то с суммы, превосходящей 400 руб., берем подоходный налог 13%, иначе подоходный налог не берется - в ячейку заносим 0. Минимальный оклад записан в ячейке F2. Литера $ говорит о том, что при копировании формулы адрес этой ячейки изменяться не будет (адрес этой ячейки абсолютный) и всегда будет указывать именно на эту ячейку – F2.

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

=C4+D4-E4

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

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

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

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

Итого

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

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

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

Итого

Затем в ячейку A10 - Максимальное значение, а в ячейку A11 - Среднее значение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для выхода из программы Excelнажмем на кнопку Office и выберем из открывшегося меню элемент .

Программа работы

Лабораторная работа №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.3.1. Создать таблицу следующего содержания:

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

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

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

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

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

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

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

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

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

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

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

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

=Лист1!Е4+Лист2!Е4+Лист3!Е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 % от годового дохода;

с 5000177р. по 75000р. – 9 % от годового дохода;

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

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

=ЕСЛИ(С4>=750001;С4*15%;ЕСЛИ(С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. Сохраните таблицу на диске.

КОНТРОЛЬНЫЕ ВОПРОСЫ

4.1. Каково назначение электронных таблиц?

4.2. Какова структура электронных таблиц?

4.3. Что такое адресация в электронных таблицах?

4.4. Как задать блок?

4.5. Как вводятся формулы?

4.6. Как организуется выравнивание информации в ячейках?

4.7. Как изменить ширину столбцов?

4.8. Как осуществляется копирование информации в электронных таблицах?

4.9. Какие Вы знаете функции в электронных таблицах?

4.10. Как построить диаграмму?

4.11. Как осуществить сортировку данных в электронных таблицах?