Клавиши для правки содержимого ячеек или строки формул

Выполняемая операция	Клавиши
Начать формулу	Знак равенства
Перейти в режим правки содержимого ячейки	F2
Очистить строку формул после указания ячейки или удалить в строке формул символ слева от курсора	Backspace
Скопировать формулу (значение) верхней ячейки в текущую ячейку или в строку формул	Ctrl + ‘ (апостроф)

Вычисление процентов

Введем в ячейку В2 число 50. В ячейке С2 вычислим 5 процентов от этого числа. Для этого в ячейке С2 напишем =5%*В2 и нажмем клавишу Enter. В ячейке появится результат 2.5.

Ссылки на ячейку

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

=С7*9

Формулы могут ссылаться на ячейки или на диапазоны ячеек, а так же на имена ил заголовки, представляющие ячейки или диапазоны ячеек. Например, если вы хотите определить количество компьютеров, продаваемых в различных магазинах, данные по которым находятся в ячейках А1, А2 и А3, то в ячейку, где должен находиться результат следует ввести формулу: =А1+А2+А3.

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

Чтобы сослаться на диапазон ячеек, введите ссылку на верхний левый угол диапазона, поставьте двоеточие (:), а затем – ссылку на правый нижний угол диапазона. Например, ссылка на все ячейки между 6-й и 15-й строками включительно имеет вид 6:15, на все ячейки в столбце С - С:С.

Можно ссылаться на ячейки, находящиеся на других листах книги или в другой книге, или на данные другого приложения. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками. Имя файла рабочей книги заключается в квадратные скобки, а имя листа отделяется от имени ячейки восклицательным знаком, например, [Book2]Sheet1!$C$3.

Отображение формул в ячейках

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

Копирование формул

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

Приведем пример копирования данных мышью. Предположим, нам надо вычислить значения нескольких чисел, возведенных в третью степень: 3, 4, 5. Введем цифры 3, 4, 5 в ячейки А3, А4, А5. Выделим ячейку В3 и запишем в ней =А3^ (символ « ^ » используется как оператор возведения в степень). Нажмем клавишу Enter. В ячейке появится результат 27. Выделим еще раз ячейку В3. Установим указатель мыши на маленький черный квадратик – маркер заполнения. Нажмем кнопку мыши и растянем рамку еще на две ячейки вниз. В выделенных ячейках отобразятся результаты вычислений: 64 и 125. Щелкнем ячейку В4 – в строке формул увидим А4^3, то есть относительный адрес ячейки изменился.

Если при копировании ячейки необходимо оставить ее адрес неизменным, то используется абсолютная ссылка. Напишем формулу в виде =$А$3^3. При копировании этой формулы в любое место таблицы всегда будут возведены в третью степень данные, находящиеся в ячейке А3.

При копировании формулы относительная ссылка автоматически меняется, абсолютная ссылка не меняется.

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

Проведем суммирование данных по столбцам в ячейках В10 и В11, С10 и С11, D10 и D11, используя возможность повторных вычислений по одним и тем же формулам.

• Выделим ячейку В12 и нажмем кнопку Автосумма.

• Появится результат суммирования – число 40.

• Выделим ячейки В12, С12 и D12.

• Выберем в меню Правка команду Заполнить/Вправо. После этого формула из ячейки В12 скопируется в ячейки С12 и D12. Причем, если в ячейке В12 находилась формула СУММ(В10:В11), то в ячейках С12 и D12 она примет соответственно вид СУММ(С10:С11) и СУММ(D10:D11), так как при переносе формулы автоматически меняются адреса ячеек.

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

Использование стандартных функций

Выполнить вычисления на листах книги позволяют стандартные функции Microsoft Excel. Для вычисления значений функций используются величины, которые называются аргументами. Аргументы функций записываются в скобки. Скобки должны быть парными, пробелы перед скобками или после них не допускаются. Аргументы располагаются в последовательности, определяемой синтаксисом функции. Список аргументов может состоять из чисел, текста, логических величин, массивов, значений ошибок или ссылок. Задаваемые аргументы должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константы, так и формулы. Константами являются даты, числа или текст. В качестве аргумента могут использоваться адреса ячеек. Аргументы разделяются точкой с запятой. Например: =СУММ(Е2;Е3;Е4;Е5;49).

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

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

Вставка функций

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

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

• Выбрать команду Функция в меню Вставка.

• Нажать кнопку Вставка функции на панели инструментов Стандартная.

• Нажать сочетание клавиш Shift+F3.

В окне Вставка функции представлены два списка. Левый список Категория содержит такие элементы, как:

• Функции управления базами данных и списками

• Функции даты и времени

• Информационные функции

• Логические функции

• Функции просмотра и ссылок

• Математические и тригонометрические функции

• Статистические функции

• Текстовые функции

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

Если вам нужна для работы какая-нибудь функция, выберите ее в списке Функция одним из следующих способов:

• Дважды щелкните имя функции

• Выделите функцию и нажмите кнопку ОК

• Выделите функцию и нажмите клавишу Enter

Если окно функции позволяет вводить несколько аргументов, то переход от одного поля аргумента к другому можно выполнять клавишей Tab.

Математические функции

Каждая функция в Excel записывается определенной последовательностью символов. Например, чтобы просуммировать несколько чисел, в списке Категория выберем математические и тригонометрические. В списке Функция выберем СУММ. Как показывает надпись под списком Категория, эта функция суммирует аргументы.

Функция времени

В текстовом формате время содержит часы, минуты, секунды. Например, 8 часов 10 минут 30 секунд. Дата в числовом формате, возвращаемая функцией Время – это десятичная дробь, принимающая значения от 0 до 0,99999999. Нулю соответствуют 12:00:00 ночи. Часы изменяются от 0 до 23, минуты и секунды от 0 до 59.

Для нахождения значения времени в виде десятичной дроби выберем в списке Категория Дата и время, в списке функций ВРЕМЯЗНАЧ. Нажмем кнопку ОК.

В поле Время_как_текст введем 8:53:14 и нажмем кнопку ОК. В ячейке получим значение 0,370301.

Итоговая функция

В качестве примера функций назовем итоговые функции, часто используемые для обработки списков, например, СУММ или СРЗНАЧ. Первая функция складывает величины и подсчитывает суммы всех чисел, находящихся в указанных ячейках: =СУММ(А1:А2;С5).

Вторая функция вычисляет среднее арифметическое нескольких чисел: =СРЗНАЧ(С3:С6;С9).

Отслеживание ячеек, участвующих в вычислениях

Панель инструментов Зависимости позволяет проследить, какие ячейки используются для вычисления значений, приведенных в ячейке. Для отображения панели на экране надо выбрать в меню Сервис команду Зависимости/Панель зависимостей. После того, как ячейка В4 стала текущей, была нажата кнопка Влияющие ячейки панели инструментов Зависимости. Чтобы убрать стрелки, следует нажать кнопку Убрать стрелки к влияющим ячейкам.

Перед тем, как отобразить панель инструментов Зависимости следует убедиться, что на вкладке Вид в окне команды Параметры из меню Сервис переключатель в группе Объекты не стоит в положении Не отображать. Кнопка Убрать все стрелки удаляет все стрелки на листе. Кнопка Источник ошибки указывает стрелками, какие ячейки могут являться источником ошибки для указанной ячейки.

Функция Сегодня вводит в ячейку текущую дату.

Функция ТДАТА вводит сразу текущую дату и время.

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

1. Как осуществляется ввод формул в Microsoft Excel?

2. Какие основные операторы используются в Microsoft Excel?

3. Арифметические операторы, основные способы вычисления.

4. Что такое абсолютная и относительная ссылки?

5. Копирование формул в рабочей таблице.

6. Использование стандартных функций (вставка функций).

УПРАЖНЕНИЯ

Задание 1. Создать таблицу в EXCEL:

	По категории застрахованного лица		Справочно (в целом по организации)
Месяц	Заработок (вознаграждение), доход	в т.ч. пособие по временной нетрудоспособности, стипендия	Налогооблагаемый доход (заработок-пособие)	Начислено в ФСС 4%	Начислено взносов в ПФР 28%	Начислено взносов в ФМС 3,4%
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь
Итого:

Задание 2. Создать таблицу «Отчет»

	Март	Апрель	Май	Июнь	Июль	Август	Всего	Среднее	Макс	Мин
Приход								Х
Затраты на товар
Полная выручка								Х
Статьи расходов
Реклама								Х
Аренда помещений								Х
Налоги и выплаты								Х
Проценты на кредит								Х
Расходы всего								Х
Прибыль								Х

Задание 3

Имеется таблица.

	A	B	C	D
1	12	-14,5	23	42
2	36	17	9	-3,76
3	64	39	25	-1
4

1. Создать новую рабочую книгу.

2. Внести элементы данной таблицы в ячейки.

3. Установить курсор в ячейку С4, ввести запись «максимальное».

4. Перейти в ячейку D4, выполнить команду Вставка/Функция или щелкнуть на кнопку Вставка_функции на панели инструментов.

5. В появившемся диалоговом окне выбрать функции Статистические/МАКС.

6. В следующем диалоговом окне необходимо в строке Число 1 ввести диапазон A1:D3. Для этого выделите его в таблице.

7. Аналогично пунктами 3, 4 выполнить действия по нахождению минимального в строке 5.

8. Переименовать лист как МаксМин.