Лабораторная работа №5. Табличный процессор Microsoft Excel 2007

1. ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

Цель работы

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

1.1. Назначение и область применения табличных процессоров

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

         Именно для проведения расчетов на компьютере для исходных данных, заданных в табличной форме, были разработаны пакеты прикладных программ, получивших названия ²Электронные таблицы² (ЭТ), или табличный процессор. Например, широко известны такие пакеты, как Lotus 1-2-3, Excel, Multiplan, SuperCalc, Quatro-Pro и др.

         Табличные процессоры обеспечивают:

·       ввод, хранение и корректировку большого количества данных;

·       автоматическое проведение вычислений при изменении исходных данных;

·       дружественный интерфейс;

·       наглядность и естественную форму документов, представляемых пользователю на экране;

·       эффективную систему документирования информации.

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

·       возможность работы в локальных сетях;

·       организацию связи на уровне обмена файлами с другими программными средствами;

·       возможность работы с трехмерной организацией ЭТ;

·       введение системы макропрограммирования.

1.2. Интерфейс Excel 2007, рабочая книга и структура рабочего листа

Excel – это программа, которая относится к категории электронных таблиц и является частью пакета Microsoft Office. Одним из достоинств Excel является ее универсальность. Версия Excel 2007 имеет новый «фирменный» интерфейс, где не используются стандартные в других Windows-приложениях выпадающие меню. Вместо системы меню в Excel 2007 используется контекстно-зависимая ленточная система. Слова (такие как Главная, Вставка, Разметка страницы и т.д.), которые располагаются в верхней части окна программы  на месте бывшей строки меню (рис. 1), теперь представляют вкладки. Щелкнув на слове, представляющем вкладку, разворачивается лента, содержащие команды выбранной вкладки. Каждая команда имеет название, которое отображается рядом (или ниже) с пиктограммой, щелчком на которой выполняется данная команда. Команды собраны в группы, и каждая группа имеет собственное название, которое отображается внизу ленты.

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

Данные Þ Сортировка и фильтр  Þ Сортировка

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

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

Строки рабочего листа пронумерованы от 1 до 1 0485 76, столбцы обозначаются буквами от A до XFD (всего 16384 столбца). На пересечении строки и столбца расположена отдельная ячейка. В любой момент времени только одна ячейка может быть активной, т.е. готовой к приему информации или другим действиям. Активная ячейка выделяется темным контуром (Рис. 1). Её адрес, т.е. буква столбца и номер строки указывается в поле Имя. На рис. 1 выделена ячейка с адресом D2. Каждая ячейка может быть использована для трех основных целей:

·       хранения текста (заголовка таблицы, имя поля и т. п. );

·       хранения числа;

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

1.3. Интерфейс Excel 2007, рабочая книга и структура рабочего листа

Excel – это программа, которая относится к категории электронных таблиц и является частью пакета Microsoft Office. Одним из достоинств Excel является ее универсальность. Версия Excel 2007 имеет новый «фирменный» интерфейс, где не используются стандартные в других Windows-приложениях выпадающие меню. Вместо системы меню в Excel 2007 используется контекстно-зависимая ленточная система. Слова (такие как Главная, Вставка, Разметка страницы и т.д.), которые располагаются в верхней части окна программы  на месте бывшей строки меню (рис. 1), теперь представляют вкладки. Щелкнув на слове, представляющем вкладку, разворачивается лента, содержащие команды выбранной вкладки. Каждая команда имеет название, которое отображается рядом (или ниже) с пиктограммой, щелчком на которой выполняется данная команда. Команды собраны в группы, и каждая группа имеет собственное название, которое отображается внизу ленты.

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

Данные Þ Сортировка и фильтр  Þ Сортировка

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

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

Строки рабочего листа пронумерованы от 1 до 1 0485 76, столбцы обозначаются буквами от A до XFD (всего 16384 столбца). На пересечении строки и столбца расположена отдельная ячейка. В любой момент времени только одна ячейка может быть активной, т.е. готовой к приему информации или другим действиям. Активная ячейка выделяется темным контуром (Рис. 1). Её адрес, т.е. буква столбца и номер строки указывается в поле Имя. На рис. 1 выделена ячейка с адресом D2. Каждая ячейка может быть использована для трех основных целей:

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

Рис. 1 Окно Excel 2007

Кроме адреса отдельной ячейки, адресом можно отметить и диапазон ячеек. Адрес диапазона состоит из адреса начальной ячейки и адреса конечной ячейки. Например, B2: E10 или A2:A100.

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

Таблица 1 Клавиши перемещения по рабочему листу Excel

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

         Предположим, что вводимая информация с ячейки А1 переходит на ячейку B1 и т.д. Если ввод в этой строке завершен, то после < Enter > произойдет ввод информации в таблицу и активной становится ячейка, расположенная ниже первой (в нашем случае А2). Однако, если необходимо ввести ещё какую-то информацию в первую строку, то это можно сделать через < Tab >. При этом активной для приема информации становится ячейка В1. 

1.4. Ленты команд

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

• Главная. Здесь собраны наиболее часто используемые команды Excel.  На этой вкладке содержатся группы команд Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки и Редактирование.  Назначение команд видно из названия групп, в которые они входят.
• Вставка. Команды, расположенные на этой вкладке что-то вставляют в рабочий лист – таблицу, график, диаграмму, символ и т.д.
• Разметка страницы. Команды этой вкладки «руководят» внешним видом рабочих листов, включая внешний вид печатных страниц рабочих листов.
• Формулы. Команды данной вкладки используются для создания формул, именования диапазона ячеек, для доступа к средствам проверки формул и управления процессом вычисления в Excel.
• Данные. Здесь собраны все команды, необходимые для обработки и анализа данных.
• Рецензирование. На этой вкладке собраны команды, выполняющие различные действия: создающие примечания к ячейкам, управляющие правописанием и следящие за изменениями в рабочих книгах, включая их защиту, и управляющие ими.
• Вид. Команды этой вкладки управляют всеми аспектами отображения рабочих книг на экране компьютера.
• Разработчик. Эта вкладка по умолчанию не отображается на экране. Команды, содержащиеся в ней, будут полезны при программировании. Чтобы отобразить вкладку Разработчик, выполните команду OfficeÞ Параметры Excel,  в окне Параметры Excel на вкладке Основные установите флажок опции Показывать вкладку «Разработчик» на ленте.
• Надстройки. Эта вкладка появится в том случае, если вы открыли рабочую книгу или надстройку, содержащую созданные пользователем меню или панели инструментов.

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

Доступ к ленте команд можно осуществить с помощью клавиатуры. При нажатии клавиши <Alt>  рядом с названием вкладок (Рис. 2) появляются в рамочках маленькие буквы-указатели, которые надо ввести с клавиатуры, чтобы открыть желаемую вставку с командами. После нажатия клавиши <Alt> и ввода буквы-указателя открывается соответствующая букве вкладка, каждая команда на которой также будет иметь свою букву указатель. 

Рис. 2 Нажатие клавиши <Alt> приводит к появлению букв-указателей.

На рис. 2 показана открытая таким способом вкладка Главная. Теперь, введя с клавиатуры соответствующую букву-указатель команды, можно выполнить эту команду. При неверно выбранной команде можно нажать клавишу <Esc>, при неверно выбранной вкладке – дважды нажать <Alt>: один раз, чтобы снять выделение букв, второй, чтобы увидеть буквы-указатели вкладки. После нажатия <Alt> можно использовать клавиши перемещения курсора по названиям вкладок или команд.  После того, как нужная команда будет найдена, нажать <Enter>. Использование букв-указателей ускоряет процесс работы с документом Excel.

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

1.5. Типы данных в Excel. Форматирование и редактирование ячеек

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

На рабочем листе Excel могут находиться также графики, рисунки, диаграммы, изображения, кнопки и другие объекты. В действительности эти объекты расположены на графическом уровне. Графический уровень – это невидимый слой, расположенный поверх рабочего листа.

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

Таблица 2. Примеры представления чисел в разных форматах

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

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

При работе с числами в русском Excel необходимо помнить, что разделителем целой и дробной части является запятая « , ». Поэтому при вводе вроде бы числа «31.04» получится «31 апреля». К числовым данным нельзя приписывать буквы. Нельзя, например, вводить «50 р.» или «$100». Но, если эти размерности нужны, они вводятся через присвоение денежного типа через формат ячейки.

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

• Двойной щелчок на ячейке – редактирование «по месту» ‑ позволяет отредактировать содержимое сразу в ячейке. 
• Нажатие клавиши < F2 > ‑ также позволяет осуществить редактирование по месту – в ячейке.
• Активизация ячейки путем установки в ней курсора, а затем ‑ редактирование в строке формул.
• Ошибка в формуле ‑ проявляется тем, что в верхнем левом углу ячейки отображается маленький треугольник (Рис. 3а). Активизировав ячейку, увидим рядом с ней смарт-тег (Рис. 3б). Щелкнув по нему, можно выбрать одну из опций, позволяющих исправить ошибку (Рис. 3в). Опции могут быть различным, в зависимости от типа ошибок.

а)                                            б)                                              в)

Рис.3 Редактирование формулы через смарт-тег

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

Структура формулы. Помимо числовых и текстовых данных, ячейки таблицы Excel могут содержать формулы. Формулой считается любое математическое выражение. Формула в Excel начинается со знака «равно» ( = ).

В формулах допустимо использование следующих операторов: вычитание (-), деление(/), умножение (*), возведение в степень (^), <, > (меньше, больше), < >(не равно), => (больше или равно), <= (меньше или равно) и, так называемых операторов связи - диапазон (:), объединение (;) и объединение текстов (& ).

В формулах также можно использовать встроенные функции. Функция – это заранее определенная формула, которая по одному или нескольким аргументам, заключенным в скобки, вычисляет результат. Excel содержит огромное количество встроенных функций. К ним относятся как достаточно распространенные функции, например, СУММ, СРЗНАЧ и КОРЕНЬ, так и функции, специально предназначенные для определенных целей, например, статистические или инженерные. После имени функции следуют круглые скобки. Данные внутри скобок называются аргументами. В качестве аргумента может выступать адрес ячейки или диапазон, числовое значение, текстовая строка, выражение или другая функция. Если в функции несколько аргументов, то каждый из них отделяется точкой с запятой. Функции различаются по тому, как они используют аргументы:

• функции без аргумента, например, ПИ(), возвращающая число p, в этом случае ставятся просто пустые скобки;
• функции с одним аргументом, например, SIN(A1), возвращающая значение синуса числа, расположенного в ячейке А1;
• функции с фиксированным числом аргументов, например, ОКРУГЛ(B4; В2), реализующая округление числа из ячейки B4 до указанного в ячейке B2 числа десятичных разрядов;
• функция с неопределенным числом аргументов, например, СУММ(A1:B1; J2:K3; J3:L4), реализующая суммирование данных из разных диапазонов ячеек; каждый диапазон – аргумент функции, их может быть от 1 до 255.

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

1) выделить ячейку и нажать клавишу «=» (равно);

2) набрать формулу и нажать <Enter>.
Например: = 2*6-9 или = А1*5-ВЗ/2+С4^2.

Первая формула содержит только константы и знаки арифметических операций. Вторая формула использует ссылки на ячейки А1, ВЗ и С4. Содержимое этих ячеек подставляется в формулу и после нажатия клавиши <Enter> вычисляется результат.

Таким образом, в ячейке показывается результат вычисления формулы, а саму формулу можно увидеть и отредактировать а) либо в строке формул, предварительно выделив нужную ячейку, б) либо активизировав ячейку двойным кликом левой кнопки мыши или нажатием клавиши < F2 > – редактирование «по месту». Следует заметить, что ссылки на ячейки во второй формуле желательно набрать неcс клавиатуры, а с помощью мыши. Для этого после нажатия клавиши «= » щелкните мышью в ячейке А1 (в строке формул автоматически появится А1), затем наберите *5, щелкните мышью в ячейке ВЗ и т.д.

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

Абсолютный адрес – неизменяемый при копировании формулы адрес, например, $А$1, знак $ фиксирует и/или букву столбца и/или цифру строки. Ввод абсолютного адреса начинается с ввода обычного адреса, а затем нажимаем <F4> для вставки знаков $.

Относительный адрес – адрес, который при копировании формул корректируется программой автоматически.

Смешанный адрес – адрес, в котором номер строки (или столбца) является абсолютным, а столбца (строки) – относительным. Например, $А1 означает, что при копировании будут меняться строки столбца А, а для адреса D$23 – будут изменяться столбцы строки №23.

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

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

Например, вам нужно складывать построчно значения столбцов А и В (рис. 4а) и поместить результат в столбец С. Если вы копируете формулу =А2+В2 из ячейки С1 в ячейку С2 (и далее вниз по С), то Excel сам преобразует адреса формулы соответственно как =А2+В2 (и т. д.). Но если вам нужно поместить формулу, скажем, из С2 в ячейку D4, то формула уже будет выглядеть как =В4+С4 (вместо нужной =А4+В4), и соответственно результат вычислений будет неправильным! Иными словами, обратите особое внимание на процесс копирования и при необходимости вручную корректируйте формулы. Кстати, само копирование из С1 в С2 делается следующим образом:

1) выбираем ячейку С1, из которой нужно скопировать формулу;

2) нажимаем кнопку Копировать на вкладке Главная, или клавиши <Ctrl+C>, или выбираем в контекстном меню соответствующую команду;

3) выбираем ячейку С2, в которую будем копировать формулу;

4) нажимаем кнопку Вставить на вкладке Главная, или клавиши <Ctrl+V>, или через меню контекстном меню выбираем команду Вставить с нажатием <Enter>.

Рассмотрим режим автозаполнителя (не путать со средством Автозаполнение формул). Если необходимо перенести (скопировать) формулу в несколько ячеек (например, в С2:С10) вниз по столбцу, то это удобнее и проще сделать так:

1) выделить ячейку С1,

2) подвести курсор мыши к нижнему правому углу ячейки – курсор примет форму жирного черного креста

3) нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона (рис. 4б). В нашем случае это ячейка С10. Excel сам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам строк (рис. 4в).

a)                                     б)                                        в)

Рис. 4 Прием набора и автокопирования формул

1.7. Поименованные ячейки и диапазоны

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

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

• В именах не должно быть пробелов. Для лучшего восприятия имени можно использовать вместо пробела знак подчеркивания, например: Время_запуска.
• Можно использовать любые комбинации букв и цифр, но имя не должно начинаться с цифры (например, 3_семестр) и не должно быть похожим на имя ячейки (например, А56).
• Специальные символы и символы пунктуации, за исключением подчеркивания и точки, использовать не разрешается.
• Имена не должны быть слишком длинными (не более 255 символов), содержательными, понятными и запоминающимися.

1.8. Вставка функций в формулы

Вставка функций может осуществляться несколькими способами.

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

Пример 1. Покажем применение этого средства на примере построения таблицы функции для диапазона x c шагом Dx = 1,5.

При работе с электронными таблицами Excel напомним, что в качестве аргумента функции может быть использован адрес ячейки (или диапазона ячеек) листа. Поэтому вначале заполним столбец аргументов, используя команду Заполнить  с вкладки Главная Þ Редактирование:

а) введем в ячейку А2 число 1;

б) снова поставим курсор на ячейку А2;

в) включим прокрутку команды Заполнить и выберем команду Прогрессия, после чего откроется диалоговое окно (рис. 5);

Рис. 5 Диалоговое окно команды Заполнить Þ Прогрессия

г) установить в полях окна необходимые параметры: расположение – по столбцам, шаг – 1,5 (десятичная запятая) и предельное значение – 9;

д) после нажатия клавиши < OK > строки столбца А будут заполнены необходимыми значениями.

Теперь переходим к набору формулы.

1) Вводим в ячейку B2 знак равенства и первую букву l функции ln, чтобы открыть список названий функций и имен диапазонов, которые начинаются с буквы l. Средство Автозаполнение формул не чувствительно к регистру букв, поэтому можно вводить имена функций как строчными, так и прописными буквами соответственно, в русской или латинской клавиатуре..

2) В раскрывшемся списке видны все функции, имена которых начинаются на букву l. С помощью клавиш управления курсором или щелчком мыши перейдем к нужной нам функции и нажмем клавишу < Tab >. Excel введет выбранное название функции в ячейку и даже  напечатает открывающуюся скобку после имени. Кроме того, список теперь будет отображать возможные значения первого аргумента данной функции, как показано на рис. 6.

3) Однако, в нашем случае аргументом функции является не число, а функция  модуля (ABS) от sin, поэтому, вводим первую букву А и клавишей < Tab > выбираем функцию ABS, а затем, таким же образом, ‑ SIN.

4) Далее набираем с клавиатуры число 5 и степень ( ^ ) – все действия отображаются в строке формул.

Рис. 6 Демонстрация средства Автозаполнение формул

 5) Для указания аргумента щелкаем по ячейке А2 и вводим закрывающиеся скобки. Завершаем ввод формулы клавишей < Enter >.

После ввода формулы в ячейке появляется число – результат расчета по формуле. Для ввода формул в оставшиеся строки таблицы достаточно применить способ автокопирования, описанный в предыдущем параграфе. Чтобы придать таблице завершенный вид в первой в ячейку А1 внесем «x», в B1 – «y» ‑ и отформатируем эти две ячейки по центру. Получаем таблицу (рис. 7а).

 Рис. 7 Таблица значений функции y(x) для диапазона x 

c шагом Dx = 1,5 в разных представлениях: а) простой диапазон ячеек
б) таблица, как объект

 2. Библиотека функций. Чтобы использовать прием Автозаполнение формул, необходимо знать хотя бы первую букву названия функции. Другой способ вставки функции в формулу ‑ это выбор этой функции из группы команд Библиотека функций, расположенной на ленточной вкладке Формулы (рис 8а). Этот способ полезен в том случае, когда неизвестно название функции. Если щелкнуть мышью на кнопке, представляющей одну из категорий функций (такую как Финансовые, Математические и т.д.), откроется список функций выбранной категории. Выбрав щелчком мыши нужную функцию, открываем вкладку функции – диалоговое окно Аргументы функции (рис. 8б), в котором имеется возможность задать значения аргументов выбранной функции, а также дается краткое описание назначения функции и, при перемещении курсора по полям окна, ‑ назначение ее аргументов. На вкладке имеется ссылка Справка по этой функции, щелчок на которой переносит в справочную систему Excel с открытой статьей, посвященной данной функции. 

Рис. 8 Библиотека функций и вкладка Аргументы функции ЕСЛИ

3. Мастер функций. Еще один метод вставки функций в формулу заключается в использовании диалогового окна Мастер функций (Рис. 9). Открыть это окно можно одним из следующих способов.

•  Выбрать команду ФормулыÞБиблиотека функцийÞВставить функцию
•  Щелкнуть на кнопке Вставить функцию , которая находится слева от строки формул.
•  Нажать комбинацию клавиш < Shift + F3 >

Рис. 9 Диалоговое окно Мастера функций

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

1.9. Таблица Excel – как новый объект рабочего листа

В любой предыдущей версии Excel понятие таблица понималось как прямоугольный диапазон ячеек со структурированными данными. Каждая строка таблицы называется записью и содержит информацию о каком-то одном элементе, тогда как совокупность данных обо всех элементах и составляет таблицу. В таблице рис. 7а каждая запись представляет собой координаты точки заданной функции на плоскости. Новинка таблиц Excel 2007 – преобразование диапазона ячеек в «официальную» таблицу – особый объект, все элементы которого имеют общие свойства. Для такого превращения необходимо выделить диапазон ячеек и выбрать команду ВставкаÞТаблицы ÞТаблица или Главная ÞСтили ÞФорматировать как таблицу.

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

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

• Переход к любой ячейке таблицы предоставляет доступ к новой контекстной вкладке Работа с таблицами Þ Конструктор.
• Автоматическое применение табличных стилей.
• В заголовке каждого столбца таблицы имеется раскрывающийся список, который можно использовать для сортировки и фильтрации данных.
• При прокрутке строк таблицы заголовки столбцов замещают буквенные заголовки столбцов рабочего листа и поэтому  всегда видны на экране.
• Таблица автоматизирует единообразные вычисления по столбцам – чтобы выполнить вычисления в каком либо столбце, достаточно ввести только одну формулу.
• В таблицах облегчен процесс выбора отдельных столбцов и строк.

Для выполнения некоторых операций со строками и столбцами таблицы (например, вырезание или копирование) требуется процедура их выделения. Для выделения столбца курсор помещают на верхнюю границу ячейки. Указатель мыши принимает вид черной стрелки, направленной вниз. Один щелчок выделяет в столбце только данные, повторный щелчок добавляет к выделенным данным ячейку с заголовком. Для выделения столбца можно использовать также комбинацию клавиш < Ctrl + пробел > в момент, когда курсор находится в какой-либо ячейке столбца. При выделении строк курсор помещается в самую левую ячейку или используется комбинация < Shift + пробел >. При выделении всей таблицы, курсор помещается в верхний левый угол крайней левой ячейки заголовка столбца или используется комбинация клавиш < Ctrl + А >.

Чтобы вставить столбец  или строку в готовую таблицу, необходимо выделить ячейку, по отношению к которой и будет реализована вставка. Из контекстного меню выбрать команды Вставить Þ Строки таблицы выше или Вставить Þ Столбцы слева/справа.

Для удаления строи (столбца) также требуется установить курсор в одну из ячеек удаляемой строки (столбца) и в контекстном меню выбрать команду Удалить Строки (Столбцы).

1.10. Примеры записи и использования некоторых функций Excel

Некоторые математические функции представлены в табл. 3. Обозначение «арг» ‑ аргумент функции: константа, адрес/имя ячейки или диапазона.

Таблица 3. Некоторые математические функции

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

ЕСЛИ (Условие ; Значение_если_истина ; Значение_если_ложь )

Условие ‑ любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10 = 100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ.

Значение_если_истина – значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА.

Значение_если_ложь – значение, которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ.

         Пример 2.

         Введем в ячейки В2 и В3 числа 1500 и 500, соответственно, в ячейки С2 и С3 – числа 900, 900. В ячейке D2, введем формулу ЕСЛИ. Имя функции заносится в строку формул, а диалоговое окно-палитра функции изменяется, давая возможность ввести аргумент функции (Рис. 10).

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

Если протянуть формулу на ячейку D3, то в ней соответственно появится значение ОК.

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

Рис. 10 Палитра функции ЕСЛИ с введенными условиями
и действиями на выполнение

 Пример 3.

В ячейки A, B, C строки 5 вводим значения трех сторон треугольника. В ячейке D5 требуется определить, не является ли треугольник равнобедренным. Для этого потребуется заполнить формулами три ячейки:

F5: =ЕСЛИ(B5=C5;"равнобедренный";"разносторонний")

E5: =ЕСЛИ(A5=C5; "равнобедренный"; F5)

D5: =ЕСЛИ(A5=B5; "равносторонний"; E5).

 Логические функции И / ИЛИ используются как самостоятельно, так и для построения сложных условий в других функциях. Функция И дает результат ИСТИНА только в том случае, если все ее аргументы имеют истинное значение, во всех остальных случаях её значение – ЛОЖЬ. Функция ИЛИ – наоборот, дает значение ИСТИНА, если хотя бы один из её аргументов имеет истинное значение, только, если все аргументы ложны, то ИЛИ дает ЛОЖЬ. Примеры записи и работы описанных  функций показаны на рис. 11.

Рис. 11 Пример записи и результаты работы функции И /ИЛИ

С применением функций И / ИЛИ решение задачи из Примера 3 об определении типа треугольника возможно с помощью только одной функции ЕСЛИ. Пример такого решения показан на рис. 12.

Рис. 12 Пример записи и результат работы функции ЕСЛИ
с использованием функций И /ИЛИ

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

Таблица 4.Некоторые функции подсчета и суммирования

На рис. 13 представлен фрагмент листа с данными. В столбце С показаны результаты работы функции СЧЁТЕСЛИ для разных условий:

Рис. 13 Результаты работы по формуле функции СЧЁТЕСЛИ

 Функция СУММЕСЛИ (интервал; критерий; сумм_интервал). Суммирует ячейки, специфицированные заданным критерием.

Интервал – это интервал вычисляемых ячеек.

Критерий – это критерий в форме числа, выражения или текста, который определяет, какая ячейка добавляется. Например, критерий может быть выражен как 32, ²32², ²>² , ²вишня².

Сумм_интервал – это фактические ячейки для суммирования. Ячейки в сумм_интервал суммируются, только если соответствующие им ячейки в аргументе интервал удовлетворяют аргументу критерий.

Пример 4.

Пусть ячейки А1:А4 содержат следующие величины: 10000 руб.; 20000 руб.; 30000 руб.; 40000 руб. Это стоимости четырех ПК. Пусть ячейки В1:В4 содержат следующие комиссионные при продаже соответствующих ПК: 700 руб.; 1440 руб.; 2100 руб.; 2880 руб.

Тогда функция

СУММЕСЛИ (А1:А4;²>16000²;В1:В4) даст значение 6420 руб. – это сумма комиссионных (столбец В) от продажи ПК, стоимость которых (столбец А) выше 16000 руб.

Функции генерации случайных чисел используются в разных задачах, например, при компьютерном моделировании обработки измеряемых данных; для создания исходных данных при разработке и отладке программ и т.д. В Excel имеется две функции этого типа: СЛЧИС() и СЛУЧМЕЖДУ(нижн_граница;верхн_граница). Рис. 14 демонстрирует пример их записи и результатов работы.

СЛЧИС() – функция возвращает равномерно распределенное случайное вещественное число, которое большее или равно 0 и меньше 1. Функция не имеет аргументов. Новое случайное вещественное число возвращается при каждом вычислении листа. Если требуется применить функцию СЛЧИС для генерации случайного числа, но изменение этого числа при каждом вычислении значения ячейки нежелательно, можно ввести в строке формулы =СЛЧИС(), а затем нажать клавишу < F9 >, чтобы заменить формулу случайным числом.

(нижн_граница;верхн_граница) ‑ Возвращает случайное целое число, находящееся в диапазоне между двумя заданными числами. При каждом вычислении листа возвращается новое случайное целое число. Нижн_граница ‑ наименьшее целое число, которое возвращает функция СЛУЧМЕЖДУ. Верхн_граница ‑ наибольшее целое число, которое возвращает функция СЛУЧМЕЖДУ.

а)   б)

Рис. 14 Вычисление случайных чисел: запись функций и их результаты

1.11. Советы по работе с формулами

Есть много приемов при работе с формулами, которые помогают как создавать, так и эффективно использовать их. Приведем некоторые из них.

• Не используйте в формулах константы. При создании формулы, например, вычисления налога на зарплату (ставка которого составляет 13%,) не стоит вносить константу в формулу: = А1*0,13. Лучше ввести константу в ячейку (например, А2), а в формуле использовать ее адрес: = А1*А2. Если предполагается автокопирование формулы в таблице, то необходимо установить абсолютный адрес = А1*$А$2.  Эти действия в дальнейшем позволяют сберечь время при сопровождении и модификации созданной рабочей книги. Например, если ставка налога изменится, то при записи формулы с константой придется вносить изменения в каждую ячейку, где использовалось старое значение. В оптимальном варианте (с указанием адреса) достаточно просто изменить значение в ячейке А2. 
• Строка формул в качестве калькулятора. Если нужно выполнить какие-либо вычисления, то в строке формул можно записать, например, следующую формулу: = (145*ПИ()-2,73)/12,54. Поскольку данная формула всегда возвращает один и тот же результат, то имеет смысл хранить в ячейке не формулу, а само значение. Если после набора формулы нажать < Enter >, то в активную ячейку запишется формула, а если ‑ < F9 >, а только потом  < Enter >, то будет сохранен результат работы по формуле.
• Получение точной копии формулы. Известно, что при копировании формулы Excel изменяет в ней адреса ячеек, когда вставляет ее в другое место рабочего листа. Но иногда требуется получить точную копию формулы. Один из способов добиться этого – использовать абсолютные адреса ячеек, но это не всегда удобно и нужно. Гораздо лучше, находясь в режиме редактирования, выбрать формулу, а затем скопировать ее в буфер обмена в виде текста. Например, если необходимо перенести точную копию формулы из ячейки А1 в ячейку А2, можно сделать следующие операции:

1. Активизировать редактирование по месту двойным щелчком или клавишей <F2>.

2. Выделить формулу – либо указателем мыши, либо нажав комбинацию клавиш < Shift + Home >.

3. Скопировать формулу в Буфер обмена, выбрав команду Копировать из вкладки Главная или контекстного меню, или нажав комбинацию < Ctrl + C >.

4. Нажать < Esc >, чтобы выйти из режима редактирования.

5. Перейти в ячейку А2. Используя команду Вставить, выбирая ее из вкладок или меню или вводя комбинацией  клавиш < Ctrl + V >, ввести формулу в ячейку.

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

• Преобразование формулы в значение. Если в задаче есть формулы, которые всегда дают один и тот же результат , то рационально преобразовать их в значения. Например, в диапазоне А1:А20 содержаться формулы, которые дают всегда один и тот же неизменяемый результат. Чтобы преобразовать эти формулы в значения, следует выполнить следующие действия.

1. Выбрать диапазон А1:А20.

2. Скопировать данные в буфер

3. Выбрать команду Главная Þ Буфер обмена Þ Вставить Þ Вставить значение.

4. Отменить режим редактирования клавишей < Esc >.

1.12. Основы построения диаграмм

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

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

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

Если необходимо создать несколько диаграмм, то рационально располагать их на отдельном листе, присвоив листу имя соответствующей диаграмм. Диаграммы представляются в режиме WYSIWYG (What You See Is What You Get – Что видите, то и получаете). Это означает, что распечатанная диаграмма будет выглядеть точно так же, как и на листе диаграммы.

Создание диаграммы по рассчитанным данным реализуется следующими шагами.

1. Определить данные, по которым будет построена диаграмма. Желательно, чтобы выбранные данные включали заголовки строк и столбцов. Данные для диаграммы не обязательно должны быть расположены в одном смежном диапазоне. Чтобы выделить несколько диапазонов, нажмите клавишу < Ctrl > и щелкните на нужных ячейках. Чтобы построить диаграмму на основе данных из разных рабочих листов, следует добавить дополнительные ряды после создания диаграммы.

2. Вставить диаграмму командой Вставка Þ Диаграммы и выбрать тип диаграммы. При выборе типа открывается список подтипов, который и не обходимо выбрать. После выбора типа и подтипа excel построит на основе выделенных данных диаграмму выбранного типа.

3. (Не обязательно) С помощью команд контекстных вкладок Работа  с диаграммами изменить внешний вид диаграммы, ее структуру, добавить или удалить те или иные элементы диаграммы.

Чтобы поэкспериментировать с макетом диаграммы, необходимо выделить ее и выбрать команду Работа с диаграммами Þ Конструктор Þ Макеты диаграмм (рис 15).

Рис. 15 Применение макета № 3 для диаграммы, построенной
по таблице y(x).

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

Копирование и перемещение диаграммы можно реализовать стандартными для Windows приемами: методом Drug & Drop (зацепи и тащи, или просто – перетащи ) или командами работы с Буфером обмена. Чтобы переместить внедренную диаграмму на отдельный лист диаграмм, надо использовать команду Работа с диаграммами Þ Конструктор Þ Расположение Þ Переместить диаграмму. В открывшемся диалоговом окне указать, куда ее надо переместить.

Удаление выделенной диаграммы реализуется клавишей <Del>.

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

Перемещение элементов также возможно простым перетаскиванием элемента в нужное место.

Форматирование элементов диаграммы  возможно как с помощью команд ленточных вкладок, так и команд контекстного меню, открывающихся на выделенном (двойным щелчком) элементе диаграммы. Диалоговые окна Формат…, которые открываются при этом (рис. 16), имеют полные названия и редактируемого элемента и настраиваемых полей.

Рис. 16 Каждый элемент диаграммы имеет собственное диалоговое

окно форматирования, например, окно форматирования вертикальной оси.

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

1. Для чего нужны табличные процессоры?
2. Пояснить организацию экрана табличного процессора.
3. Для чего используется строка формул?
4. Что представляют собой ленты команд?
5. Как вызывается и используется контекстное меню?
6. Назовите составляющие элементы рабочей книги.
7. Как выделить ячейку или диапазон ячеек?
8. Какие типы можно использовать для данных в ячейках?
9. Как осуществить редактирование данных в ячейках?
10. Перечислить способы форматирования ячеек.
11. Какова структура формулы?
12. В чем отличие абсолютной, относительной и смешанной адресации?
13. Что в Excel используется в качестве аргумента функции?
14. Перечислить способы ввода в формулу какой-либо функции?
15. Как сохранить результаты работы в Excel? Какое расширение имеют файлы, хранящие Книги Excel?

3. ЗАДАНИЯ

Выполняются самостоятельно

3.1. Задание. Табулирование математических функций

3.2. Задание. Построение графика функций

Построение графика реализуем на основе таблицы, построенной в Задании 1.

1.  Выбор данных. Выделим столбцы у и u: диапазон ячеек B1:C12.

2. Выбор типа диаграммы осуществляется в группе Диаграммы ленточной вкладки Вставка: выберем тип График Þ График с маркерами.

3. Усовершенствуем полученную диаграмму, используя макеты диаграмм. На вкладке Работа с диаграммами Þ Конструктор Þ Макеты диаграмм выбираем макет №3. Он добавил заголовок диаграммы и переместил легенду. Заголовок можно ввести вручную: График функций y(x) и u(x). 

4. Установить вертикальные линии сетки, пользуясь командами Работа с Диаграммами Þ Макет Þ Оси Þ Сетка.

5. Установить данные по горизонтальной оси. Работа с диаграммами Þ Конструктор Þ Данные Þ Выбрать данные. Откроется диалоговое окно (рис. 18). В нем перейти на правую панель Подписи горизонтальной оси. Нажать кнопку Изменить. В открывшемся диалоговом окне укажем диапазон А2:А12

Рис. 18 Диалоговое окно для выбора данных

Ось размечена числами с 2-мя знаками после точки, что не очень удобно. Двойным кликом активизировать горизонтальную ось и вызвать контекстное меню. Открыть диалоговое окно команды Формат оси… (рис. 16). На вкладке Число установить Число десятичных знаков 1.

Неудобным является и тот факт, что главная ось попадает между делениями. В этом же окне Формат оси… на вкладке Параметры оси ÞПоложение оси включить переключатель по делениям. Настроить формат и вертикальной оси.

6. Подписать оси, используя вкладку Работа с диаграммами Þ Макет Þ Название осей. Дадим для оси абсцисс название x, для ординат – Функция. Перенесем подпись x за диаграмму на уровень оси.

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

8. Изменить размер маркера графика. Двойным кликом активизировать любой из графиков, вызвать контекстное меню и из него выбрать команду Формат ряда данных. В диалоговом окне перейти на вкладку Параметры маркера. Установить переключатель на Встроенный и изменить тип маркера и уменьшить его размер до 3.

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

10. Перенести диаграмму на отдельный лист, пользуясь командами Работа с диаграммами Þ Конструктор Þ Расположение  Þ Переместить диаграмму.

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

Рис. 19 Окончательный вид графика функций.

12. Поэкспериментируйте с другими типами диаграмм, сохраняя их на отдельных листах.

13 . Сохраните книгу Excel на диске.

3.3. Задание. Разработка электронной таблицы для построения платежной ведомости

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

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

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

Вот порядок действий для разработки заданной модели.

 Этап 1. Подготовка исходных данных

1. Создать новую книгу Excel и сохранить ее в файле с именем Вашей фамилии, например, ПЕТРОВА_ВОЛЧЕНКО: кнопка Office→Сохранить как.

2. Используя контекстное меню на вкладке Листа1 дать название Исходные данные.

3. Ввести в него данные, показанные на рис. 20.

Рис. 20 Лист Исходные данные

4. Создать имена для ячеек первого листа. Для этого выделить ячейку или диапазон, например С1. Затем выполнить команду Формулы →Определение имени →Присвоить имя. Или вызвать эту же команду из контекстного меню. В появившемся диалоговом окне Создание имени (рис. 21) в поле Имя ввести имя ячейке – Мин_зарпл (минимальная зарплата). В поле Область указывается область видимости для создаваемого имени – в данном случае Книга. Другой способ создания имени:  выделить ячейку Е1. Перенести курсор мыши в поле ИМЯ экрана Excel (слева от строки формул), и внести в него Кол_сотр (количество сотрудников). Выделить диапазон А3:А9 и дать ему имя Должность. Ячейке D1 установить числовой формат Процентный и присвоить имя проц_кат (процент за категорию).

Если требуется исправить введенное имя, сделать это можно в поле ИМЯ, или через диалоговое окно Формулы → Диспетчер имен. В нем же можно удалить имя, если такая необходимость возникает.

Рис. 21 Диалоговое окно Создание имени.

5. В ячейки Е3:Е9 выбрать категорию каждой должности, используя функцию =СЛУЧМЕЖДУ(C3;D3) (стр. 22), которая назначит случайным образом значение в диапазоне от кат1 до кат2 для каждой должности диапазона А3:А9. Присвоить диапазону Е3:Е9 имя Категории.

Этап 2. Разработка основной ведомости

1. Перейти на лист2 и даем ему имя Ведомость

2. Выделить первые пять ячеек.

а) На вкладке Главная → Ячейки →Формат → Ширина столбца установить ширину столбца 12 пт.

б) Ввести в первую строку заголовки столбцов будущей ведомости.

в) Вызвав диалоговое окно Формат ячеек (на вкладке Главная  или в контекстном меню) и отформатировать заголовки так, как показано на рис. 22.

Рис. 22 Форматирование заголовков столбцов будущей таблицы Ведомость

3. Заполнить первый столбец таблицы согласно рис. 23 и отформатировать диапазон А1:Е11 как таблицу. Для придания ей пропорционального вида еще раз дать команду на установку ширины столбцов 12пт.

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

а) поместите курсор в ячейку В2. На вкладке Данные в группе Работа с данными выберите команду Проверка данных. откроется вкладка Проверка вводимых значений (рис. 23).

Рис. 23 Первый столбец таблицы Ведомость и подготовка раскрывающегося списка

 б) В поле Тип данных, нажав стрелку прокрутки открыть список допустимых значений и выбрать Список. В поле Источник записать ссылку на диапазон: =Должность.

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

5. Переходим к заполнению столбца Табельный номер. Пусть в нашей модельной задаче табельный номер имеет текстовое значение и формируется слиянием (знак &) первых трех символов фамилии и номера, выбранным случайным образом из количества сотрудников (Кол_сотр). Откройте палитру функции =ЛЕВСИМВ и познакомьтесь с ее значением и синтаксисом. Используя прием Автозаполнение формул, ввести в ячейку C2 формулу:

=ЛЕВСИМВ(Таблица1[[#Эта строка];[ФИО]];3)&СЛУЧМЕЖДУ(1;кол_сотр)

Рис. 24 а) Выбор значений из раскрывающегося списка; б) Заполненный второй столбец таблицы с использованием списка

Обратите внимание:

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

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

в) Поскольку в формировании табельного номера участвует функция выбора случайного числа, то при пересчете таблицы значения ячеек столбца будут меняться – проверьте это, нажав клавишу < F9 >. То же самое будет происходить и с диапазоном Категории. В реальной практике таких ситуаций, конечно, не должно быть, так как табельные номера и категории сотрудникам присваивают не случайным образом, а по определенным правилам. Но для учебных целей взятая модель пригодна.

6. Для удобного назначения категории каждому сотруднику, соответствующей его должности, задать ячейке Сообщение для ввода. Для этого снова вызвать диалоговое окно Данные ÞРабота с даннымиÞПроверка данных и перейти на вкладку Сообщение для ввода, где в поле Сообщение ввести список должностей в соответствие с диапазоном Должность, отступив от края поля так, как показано на рис. 25а.

Ввести в ячейку раскрывающийся список из диапазона Категории (см. пункт 4).

Скопировать свойство ячейки на весь столбец Категории. Теперь при вводе категории нужно выбирать ее соответственно должности сотрудника из раскрывающегося списка (рис. 25б). 

Рис. 25 а) Диалоговое окно для создания всплывающего сообщения в ячейке. б) Выбор категории сотрудника из списка соответственно его должности

 7. Последний столбец таблицы должен содержать формулу, по которой вычисляется Основная зарплата сотрудника, исходя из его Категории, минимальной зарплаты (Мин_зарпл) и процента начисления на категорию (катег_проц). В рамках заданной модели, формула будет иметь следующий вид: 

Основная зарплата = Мин_зарпл*(1+(Категория ‑ 1)*катег_проц)

Введите формулу в ячейку Е2, выбирая мышью необходимые ячейки с листов. Внимание!: после выбора именованных ячеек с листа Исходные данные не уходить сразу с этого листа, а перенести курсор мыши в строку формул и в ней продолжать набор арифметических операторов и скобок, затем, при необходимости, можно перейти на лист Ведомость. Формула = Мин_зарпл * ( 1 + Таблица1[[#Эта строка] ; [Категория]]  ‑  1) * катег_проц автоматически будет скопирована на весь столбец.

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

8. Основной макет таблицы Ведомость готов. Поместите курсор на стрелку раскрывающегося списка заголовка ФИО и отсортируйте таблицу по алфавиту. В режиме просмотра (Кнопка Office→Печать→Предварительный просмотр) или при печати документ будет выглядеть так, как на рис. 26

Рис. 26 Заполненная таблица Ведомость

Этап 3. Расширение ведомости дополнительными полями и итоговыми расчетами.

1. Введем столбец для начисления подоходного налога. Для этого на листе Исходные данные ячейке F1 присвоить Процентный формат и ввести в нее число 13. Обратите внимание: знак % появляется автоматически. Дать ячейке имя Подоходный. 

Перейти на лист Ведомость и ввести заголовок Под.налог в ячейку F1.

В ячейку F2 ввести формулу, для вычисления налога:

=Таблица1[[#Эта строка];[Основная зарплата]]*Подоходный

2. Ввести столбец для вычисления суммы К выдаче, которая равна разности Основной зарплаты и Под.налога. Самостоятельно введите требуемую формулу в ячейку G1.

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

а) Ввести между столбцами таблицы F и G  новый столбец и дать ему заголовок Премия.

б) Вызвать Мастер функций (значок f_x ) и среди логических функций выбрать функцию ЕСЛИ.

в) На палитре Аргументы функции, щелкая по ячейкам листов Исходные данные и Ведомость, ввести заданные условия для начисления премии (рис. 27). В строке формул функция выглядит следующим образом:

=ЕСЛИ(Таблица1[[#Эта строка];[Категория]]<=10;Мин_зарпл*2;Мин_зарпл)

Рис. 27 Пример функции ЕСЛИ для вычисления премии
по заданному условию

4. Учесть премию в столбце К выдаче.

5. Присвоить Финансовый формат данным столбцов Под.налог, Премия и К выдаче.

6. Усложните условие начисления премии: для сотрудников категории менее 9 – две Мин_зарпл, для категории, не больше 13 – одна Мин_зарпл, выше 13 – половина Мин_зарпл.

При записи формулы следуйте алгоритму: 

если Категория < 9, то премия равна Мин_зарпл ‑ поле Значение_если_истина; в поле Значение_если_ложь: если Категория < 13, то 2* Мин_зарпл, иначе Мин_зарпл/2.

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

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

б) Используя прокрутку Строки итогов в столбце Основная зарплата, вычислить максимальную зарплату.

в) Обратите внимание, что в строке формул функция имеет =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(104;[Основная зарплата]): вместо имени функции указан ее код ‑ 104. Скопировать формулу из ячейки Е11 в Е12. Установить курсор в ячейку Е12 и клавишей <F2> вызвать формулу на редактирование. Подведите курсор к коду 104 и удалите цифры 04, раскроется список кодов и функций (рис. 28). Выберите курсором функцию МИН и клавишей <Tab> введите ее в формулу.

г) В столбце F12 подсчитать сумму премий, выдаваемых для сотрудников с зарплатой ниже 6000 руб. Использовать функцию СУМЕСЛИ, изучив назначение полей ввода на ее палитре.

Рис. 28 Раскрывающийся список итоговых функций и их кодов.

д) В столбце Должность, выбрать Другие функции, отрыть окно мастера функций и найдя функцию СЧЁТЕСЛИ, подсчитать количество профессоров. В столбце Категории, подсчитать количество сотрудников с категорий, меньше 8.

е) В столбце Табельный номер подсчитать количество сотрудников подразделения – функция Количество.

8. Создать заголовок к созданной Вами Ведомости.

а) Вставить перед таблицей 3 пустых строки, пользуясь командой Вставить из контекстного меню или вкладки Главная →Ячейки. В ячейку А1 ввести текст Ведомость выдачи заработной платы.

б) Выделить диапазон А1:Н1, вызвать диалоговое окно Формат ячеек и установить Выравнивание →по горизонтали → по центру выделения, затем установив шрифт Аrial полужирный 12. Название растянется над всей таблицей.

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

9. Задание выполнено. Таблица должна выглядеть так, как показано на рис. 29.

Рис. 29 Окончательный вариант таблицы Ведомость