Как выглядит маркер автозаполнения?

A. Это кнопка с изображением кисти на панели инструментов "Форматирование".

B. Это область ячейки или диапазона, где мышка выглядит белым крестиком.

C. Это чёрный квадратик в правом нижнем углу ячейки или диапазона.

ГЛАВА 5. ПРИСВОЕНИЕ ИМЕН

Правила создания имен

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

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

Задание имен имеет несколько преимуществ:

- имена носят описательный характер и проще в понимании, чем адреса ячеек;

- когда ячейка перемещается, имя остается с ней;

- имена ячеек или диапазонов можно использовать в формулах или аргументах функций;

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

В имени может быть больше одного слова. В качестве разделителей слов могут быть использованы знаки подчеркивания и точки, например: Год_2007 или Год.2007.

Правила создания имен:

- имя должно начинаться с буквы, обратной косой черты (\) или символа подчеркивания (_);

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

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

- имена не должны совпадать с адресами ячеек, т.е. нельзя использовать имена, которые могут трактоваться как ссылки на ячейки;

- имена не зависят от регистра символов, т.е. имя может состоять из строчных и прописных букв, но Excel их не различает;

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

Способы создания имен

В Excel существуют четыре способа задания имен ячейкам и диапазонам. Самый простой – использовать поле Имя.

Второй способ – с помощью команды Присвоить имя, третий – с помощью Диспетчера имен и четвертый с помощью команды Создать из выделенного.

Задание 1. Присвоить имя отдельной ячейке с помощью поля Имя.

Поле Имя расположено слева от строки формул, и в нем отображается адрес текущей активной ячейки (рис. 5.1).

Рис. 5.1. Поле Имя

Ваши действия:

1. Откройте файл Упражнения.xlsx из своей личной папки.

2. Создайте лист с именем Упр.5.

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

4. В ячейке В3 введите формулу =В1*В2.

Рис. 5.2. Исходные данные – пример 1

5. Выделите ячейку B1.

6. Сделайте щелчок в поле Имя. Excel превратит это поле в стандартное поле редактирования и выделит помещенный в него адрес ячейки (это адрес текущей активной ячейки).

7. Введите присваиваемое выделенной ячейке или диапазону имя, как показано на рис. 5.2. и нажмите на клавишу Enter.

Задание 2. Присвоить имя с помощью команды Присвоить имя.

Ваши действия:

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

2. Выполните команду Формулы, Определенные имена, Присвоить имя. Появится диалоговое окно Создание имени (рис. 5.3).

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

Excel автоматически использует заголовки строк и столбцов для ссылок на ячейки. Поэтому в поле Имя уже будет написано слово «Цена».

3. Подтвердите выбор, сделанный Excel, и нажмите на клавишу ОК.

4. Самостоятельно присвойте ячейке В3 соответствующее имя «Продажи», используя один из описанных выше способов.

Задание 3. Применить имена в формулах.

Ваши действия:

1. Сделайте активной ячейку В3.

2. Откройте список команд, нажимая на стрелку, расположенную рядом с командой Присвоить имя и выполните команду Применить имена (рис. 5.4).

Рис. 5.4. Выпадающее меню Присвоить имя

3. В появившемся диалоговом окне Применение имен (рис. 5.5) выделите последовательно все имена и нажмите на клавишу ОК.

Рис. 5.5. Диалоговое окно Применение имен

4. В результате формула примет конечный вид, показанный на рис. 5.6.

Рис. 5.6. Конечный вид формулы после применения имен

Задание 4. Создать несколько имен для диапазона ячеек автоматическим способом.

Ваши действия:

1. Введите исходные данные (рис. 5.7).

2. В ячейку Е4 введите формулу, показанную в строке формул (см. рис. 5.7).

3. Выделите диапазон ячеек D2:E4.

Рис. 5.7. Исходные данные – пример 2

4. Выполните команду Формулы, Определенные имена, Создать из выделенного.

5. В появившемся диалоговом окне (рис. 5.8) нажмите на клавишу ОК, подтверждая выбор, сделанный Excel.

Рис. 5.8. Окно для создания нескольких имен одновременно

6. В результате всем трем ячейкам диапазона Е2:Е4 будут одновременно присвоены имена. Выделяя по очереди ячейки E3 и E4, проверьте правильность присвоенных имен.

7. Выполните команду Применить имена.

8. В появившемся диалоговом окне Применение имен выделите последовательно щелчком левой кнопкой мыши все вновь созданные имена, как показано на рис. 5.9 и нажмите на кнопку ОК.

Рис. 5.9. Выделение имен

9. Теперь в строке формул можно прочитать формулу (рис. 5.10).

Рис. 5.10. Формула с примененными именами

Задание 5. Замена имен в формулах.

Ваши действия:

1. Дополните таблицу, как показано на рис. 5.11, введя данные за второй и третий год.

Рис. 5.11. Применение имен в формулах

2. Выделите все ячейки диапазона D2-G4.

3. С помощью команды Создать из выделенного фрагмента присвойте имена, соответствующие заголовкам строк.

4. На запрос Excel о замене имен ответьте утвердительно.

5. Формулу, находящуюся в ячейке E4, распространите на оставшийся диапазон F4:G4, используя маркер заполнения.

6. Проверьте правильность присвоения имен, выделяя по очереди диапазоны ячеек E2:G2, E3:G3. Имя для всего диапазона выделенных ячеек вы увидите, по-прежнему, в поле Имя.

7. Выделяя по очереди ячейки E4, F4 и G4, обратите внимание, что произошла подстановка имен в формулу произведения двух соседних ячеек.

Задание 6. Удаление имен.

Ненужное или ошибочное имя можно удалить.

Ваши действия:

1. Перейдите на вкладку Формулы и в группе команд Определенные имена выполните команду Диспетчер имен.

2. В появившемся диалоговом окне Диспетчер имен (рис. 5.12) выделите любое имя.

3. Выберите команду Удалить и нажмите на клавишу ОК.

4. В этом же окне также можно присвоить диапазону имя, используя команду Создать, или изменить его, используя команду Изменить.

Рис. 5.12. Диалоговое окно Диспетчер имен

Задание 7. Использование имен в формулах.

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

Ваши действия:

1. Перейдите на лист Упр.3.

2. Выделите диапазон ячеек А35:Е38 (см. рис.3.13).

3. Выполните команду Создать из выделенного фрагмента, затемвыполните команду Применить имена.

Рис. 5.13.

В результате во всех формулах вместо ссылок на ячейки появятся имена (рис. 5.13). Excel редактирует имена таким образом, чтобы сделать их полноценными. Если имена содержат пробелы, Excel заменит пробелы знаками подчеркивания, например, Фирма_1. Если имя начинается с цифры, Excel добавит в его начало символ подчеркивания, например, _ 1_квартал.

4. Сохраните файл Упражнения.xlsx.

Самостоятельная работа 5. Создание имен.

Ваши действия:

1. Откройте файл с именем Лабораторные работы.xlsx и на листе Лаб.5 введите данные за 3 месяца по всем статьям затрат (рис. 5.14).

2. Примените обрамление таблицы.

3. Примените форматирование текста.

4. Примените денежный формат к числовым значениям.

5. В итоговой строке и столбце для подсчета сумм примените функцию СУММ().

Рис. 5.14. Исходные и расчетные данные

6. Присвойте ячейке Е3. имя Износ.

7. Назовите ячейку В12 именем янв_общ.

8. Присвойте имена следующим диапазонам ячеек: данным за Январь (В3:В11), Февраль (С3:С11), Март (D3:D11), 1кв. (E3:E11). Имена диапазонов должны совпадать с заголовками этих столбцов.

9. Присвойте имя Итого ячейкам В12:D12.

10. Перейдите к ячейке с именем Износ, используя функциональную клавишу F5.

11. Используя поле Имя в строке формул переместитесь в диапазон с названием Итого.

12. Таким же образом, перейдите в поименованные области Январь и 1 кв.

13. Установите курсор в свободную ячейку, расположенную ниже исходной таблицы.

14. Выполните команду Формулы, Определенные имена, Использовать в формуле, Вставить имена, Все имена (рис. 5.15).

Рис. 5.15. Диалоговое меню Вставка имени

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

- выделите исходную таблицу и скопируйте ее в буфер обмена;

- установите курсор в свободное место;

- в контекстном меню выберите команду Специальная вставка и в появившемся диалоговом окне установите флажок Транспонировать.

16. Сделайте копию исходной таблицы в книгу Упражнения.xlsx на три листа с именами Отд.1, Отд.2, Отд.3.

17. Сохраните, затем закройте файл Лабораторные работы.xlsx.

Вопросы для самоконтроля

1. Какие преимущества существуют при использовании имен?

2. Назовите правила при создании имен.

3. Перечислите способы создания имен.

4. Что означает термин «транспонировать» таблицу?

5. Что произойдет с формулой, если удалить имя ячейки, участвующее в создании этой формулы?

Тесты

1. На какой вкладке находятся команды для присвоения имени ячейке?

A. Главная.

B. Вставка.

C. Формулы.

D. Данные.

2. Для присвоения имени ячейке необходимо:

A. Сделать ячейку активной, ввести в неё имя и нажать Enter.

B. Сделать ячейку активной, раскрыть поле списка имен, ввести в поле имя ячейки и нажать Enter.

C. Сделать ячейку активной, раскрыть поле списка имен, ввести в поле имя ячейки, сделать однократный щелчок мышкой в любом месте рабочего листа.

3. Сколько способов существует для создания имен?

A. 2

B. 3

C. 4

D. 5

4. Имя можно присвоить …

A. Одной ячейке.

B. Диапазону ячеек.

C. Строке.

D. Столбцу.

E. Листу.

5. В каком диалоговом окне можно создать новое имя, изменить или удалить имя?

A. Диспетчер имен.

B. Создать из выделенного диапазона.

C. Создание имени.

D. Применение имен.

6. Можно ли одновременно создать несколько имен?

A. Да, можно.

B. Нет, нельзя.

7. В каком диалоговом окне можно создать несколько имен одновременно?

A. Диспетчер имен.

B. Создать из выделенного диапазона.

C. Создание имени.

D. Применение имен.

8. В каком диалоговом окне можно применить ячейкам созданные имена?

A. Диспетчер имен.

B. Создать из выделенного диапазона.

C. Создание имени.

D. Применение имен.

ГЛАВА 6. РАБОТА С ФОРМУЛАМИ

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

Структура формулы

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

Если формула не начинается со знака равенства, Excel интерпретирует введенное значение как текстовое и не производит вычислений. Если вы совершили эту довольно распространенную ошибку, нажмите функциональную клавишу F2, чтобы перейти к редактированию ячейки, затем клавишу Home для перемещения курсора в начало формулы, введите знак равенства (=) и нажмите клавишу Enter.

Результатом работы формулы является вычисленное значение. Результат вычислений отображается в ячейке, а формула – в строке формул.

Например, приведенные строки являются формулами:

=5+4/35

=12%*В1

=14*А4-(SIN(В1)^2)

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

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

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

Константой называют постоянное (не вычисляемое) значение. Формула и результат вычисления формулы константами не являются.

Задание 1. Ввод формулы в ячейку рабочего листа.

Ваши действия:

1. Откройте файл Упражнения.xlsx.

2. Создайте лист с именем Упр.6.

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

Рис. 6.1. Ввод формулы в ячейку

4. Выделите ячейку В3 и введите в нее зн ак равенства "=", указывающий Excel, что далее будет следовать текст формулы.

5. Сделайте щелчок на ячейке В1, затем введите знак операции – «*».

6. Сделайте щелчок на ячейке В2, затем введите знак операции – «/», потом константу – 12.

7. Завершите ввод формулы нажатием на клавишу Enter или щелчок на кнопку Ввод, расположенной в строке формул.

Примечание. Не переходите к другой ячейке, пока не нажмете на клавишу Enter. Иначе Excel включит адрес этой ячейки в формулу!

6. Введите в ячейки А5:С5 исходные данные: 100, 55, 25 (рис. 6.2). В ячейках А6:А11 произведите вычисления по следующим формулам:

Рис. 6.2. Использование операторов в формулах

Порядок выполнения действий

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

Таблица 6.1

Арифметические операторы

Операция	Символ
Сложение	+ (знак плюс)
Вычитание	- (знак минус)
Умножение	* (знак звездочка)
Деление	/ (знак косая черта)
Возведение в степень	^ (знак крышка)
Указание приоритета	Заключить в скобки ()

 

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

- формулы вычисляются слева направо, например, 15/3+2 равно 7, а не 3;

- возведение в степень и выражения в скобках выполняются в первую очередь;

- затемвыполняютсяумножение и деление;

- сложение и вычитание выполняются в последнюю очередь.

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

Задание 2. Знакомство с порядком выполнения арифметических операций в формулах.

Ваши действия:

1. В ячейку А13 введите формулу, показанную на рис. 6.3.

2. С помощью маркера заполнения распространите полученную формулу в ячейки А14:А17.

3. Сделайте активной ячейку А14.

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

Рис. 6.3. Приоритет выполнения действий

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

Задание 3. Изменение приоритета выполнения математических действий в формуле.

Ваши действия:

1. Введите в ячейку А19 формулу =1+2-3*4/5^6. Определите порядок выполнения операций при проведении вычислений.

2. Скопируйте формулу в ячейку А20.

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

Задание 4. Самостоятельно в ячейке А22 произведите вычисления по формуле:

7 +	53
6*8

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

Задание 5. Использование в формулах имен.

Ваши действия:

1. В ячейки А25 и А26 введите х и у.

2. В ячейки В25 и В26 введите числа 4 и 3.

3. Присвойте значениям имена из столбца А, соответственно х и у (рис. 6.4).

4. В ячейки С25, С26 и С27 введите следующие формулы:

1+х	;	х-2		-2х +	х3
4у	5 +	2х		3у2+4
		у2+3

Рис. 6.4. Использование имен ячеек в формулах

Задание 6. Самостоятельно в ячейке А30 произведите вычисления по формуле.

Ваши действия:

1. Введите в ячейки А29:D29 числа: 5, 10, 15, 20.

2. В ячейку А30 введите формулу

A21	+	5 + C21	2,4
B21 - 5	25 - D21

3. Сохраните файл Упражнения.xlsx.

Вопросы для самоконтроля

1. Что такое формула?

2. Где отображается формула?

3. Где отображается результат вычислений?

4. Что может входить в состав формулы?

5. Можно ли использовать в формуле имена ячеек?

6. С какого символа должна начинаться формула?

7. Можно ли в формуле использовать пробелы?

8. Перечислите математические операторы, которые можно использовать в формуле?

9. Назовите правила построения формулы?

10. Перечислите порядок выполнения действий?

Тесты

1. С какого символа должна начинаться формула?

A. Не имеет значения.

B. ' (апостроф).

C. ~ (тильда).

D. = (равно).

2. Какой символ следует использовать в формулах в качестве знака умножения?

A. х

B. (

C. *

D. ^

3. Какой символ следует использовать в формулах в качестве знака деления?

A.:

B. \

C. /

D. |

4. Как подтвердить ввод формулы в ячейку?

A. Нажать кнопку 1.

B. Нажать кнопку 2.

C. Нажать кнопку 3.

D. Нажать кнопку 4.

5. Какую клавишу клавиатуры следует нажать для подтверждения ввода формулы в ячейку?

A. F4.

B. F2.

C. ENTER.

D. ESC.

6. Можно ли при вводе формул использовать скобки?

A. Да, всегда.

B. Да, но только при использовании абсолютных ссылок.

C. Да, но только при использовании ссылок на другие листы.

D. Нет, никогда.

7. Для ввода формулы в ячейку С5 необходимо …

A. Сделать ячейку С5 активной, ввести знак = и затем вводить формулу.

B. Сделать ячейку С5 активной, и затем ввести формулу.

C. Сделать ячейку С5 активной, выполнить команду Вставка, Функция, и затем использовать Мастер функций для ввода формулы.

8. Вычисления производятся в формуле …

A. Слева направо.

B. Справа налево.

C. Сверху вниз.

D. Снизу вверх.

9. В первую очередь в формуле выполняются …

A. Умножение и деление.

B. Сложение и вычитание.

C. Возведение в степень и выражения в скобках.

10. В последнюю очередь в формуле выполняются …

A. Умножение и деление.

B. Сложение и вычитание.

C. Возведение в степень и выражения в скобках.

11. В формуле могут присутствовать …

A. Константы.

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

C. Математические операторы.

D. Функции.

E. Круглые скобки.

12. Можно ли в формуле использовать пробелы?

A. Да, можно.

B. Нет, нельзя.

13. Результат вычислений отображается …

A. В строке формул.

B. В ячейке.

C. В поле Имя.

D. В строке состояния.

ГЛАВА 7. ФУНКЦИИ

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

Excel имеет несколько сотен встроенных функций, которые выполняют широкий спектр различных вычислений. Некоторые функции, такие как СУММ() или SIN(), являются эквивалентами длинных математических формул, которые вы можете создавать сами. Другие функции, такие как ЕСЛИ() или ВПР(), в виде формул реализовать невозможно, поэтому они относятся в разряду подпрограмм.

Другими словами функции – это заранее разработанные небольшие вспомогательные программы, выполняющие конкретные задачи. Часто этими задачами являются вычисления, однако иногда они бывают более обобщенными (например, некоторые функции просто возвращают дату и/или время). Фактически функции заменяют одну или несколько формул.

Каждая функция состоит из трех элементов:

- знак равенства (=) собственно указывает на функцию (формулу);

- имя функции (например, СУММ) указывает, какую операцию необходимо провести;

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

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

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

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

Для создания формул с функциями обычно используют группу Библиотека функций вкладки Формулы (рис. 7.1).

Рис. 7.1. Группа команд Библиотека функций вкладки Формулы

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

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

Рассмотрим некоторые математические функции: СУММ(), ПРОИЗВЕД(), СУММЕСЛИ(), ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ(), СТЕПЕНЬ().

Суммирование

Функция СУММ() – складывает все числа в диапазоне ячеек.

Синтаксис функции: СУММ(А),

где A – список от 1 до 255 аргументов, которые требуется суммировать. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Однако есть и некоторые отличия. При использовании функции СУММ()добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Задание 1. Создание с использованием функцииСУММ().

Ваши действия:

1. Откройте файл Упражнения.xlsx из своей личной папки.

2. Создайте новый лист с именем Упр. 7.

3. Установите курсор в ячейку А2.

4. Нажмите на кнопку Вставить функцию, расположенную в строке формул. Появится диалоговое окно Вставка функции (рис. 7.1). На первом шаге работы Мастера функций необходимо выбрать имя функции, например, СУММ() из категории Математические.

Рис. 7.1. Диалоговое окно Вставка функции

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

6. В появившемся окне Аргументы функции (рис. 7.2) введите аргументы функции. В поле Число 1 введите первый аргумент – 3, а в поле Число 2 второй аргумент – 2.

7. Нажмите кнопку ОК.

Рис. 7.2. Диалоговое окно Аргументы функции

В результате в ячейке А2 появится значение, а в строке формул будет видна формула, по которой было произведено вычисление (рис. 7.3).

Рис. 7.3. Примеры функции СУММ()

8. Введите исходные данные в ячейки А3:Е3 (см. рис. 7.3).

9. Установите курсор в ячейку А4 и нажмите на клавишу (=) на клавиатуре.

10. В поле Имя появится список последних 10 использовавшихся функций (рис. 7.4). Так как последней была выполнена функция СУММ(), она в этом списке находится первой.

Рис. 7.4. Выпадающее меню списка функций

11. Выберите функцию СУММ(), вы сразу попадете на второй шаг работы Мастера функций.

12. В диалоговом окне Аргументы функции в поле Число 1 введите аргумент A3:C3.

=СУММ(А3:С3)

13. В результате вычисления в ячейке появится значение 50.

14. В ячейку А5 введите следующую формулу:

=СУММ(B3:E3;15)

Здесь в качестве второго аргумента в поле Число 2 введите константу – 15 (см. рис. 7.3).

Умножение

Для умножения используют функцию ПРОИЗВЕД().

Синтаксис функции: ПРОИЗВЕД(А),

где A – список от 1 до 255 аргументов, которые требуется перемножить. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Фактически данная функция заменяет непосредственное умножение с использованием оператора умножения (*). Так же как и при использовании функции СУММ(), при использовании функции ПРОИЗВЕД() добавление ячеек в диапазон перемножения автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон перемножения. Аналогично формула будет изменяться и при уменьшении диапазона.

Задание 2. Создание формулы с использованием функции ПРОИЗВЕД().

Ваши действия:

1. Введите в ячейку А7 формулу:

=ПРОИЗВЕД(А3:С3)

2. Введите в ячейку А8 формулу:

=ПРОИЗВЕД(B3:D3;2)

3. Введите в ячейку А9 формулу:

=СУММ(ПРОИЗВЕД(A3:B3);ПРОИЗВЕД(C3:D3))

Результаты вычислений можно увидеть на рис. 7.5.

Рис. 7.5. Примеры функции ПРОИЗВЕД()

Выборочная сумма

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

Синтаксис функции: СУММЕСЛИ(А;В;С),

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

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

Задание 3. Создание формулы с использованием функции СУММЕСЛИ().

Ваши действия:

1. Введите исходные данные в ячейки А11:С14, как показано на рис. 7.6.

Рис. 7.6. Примеры функции СУММЕСЛИ()

2. Введите в ячейки А15 и А16 формулы:

=СУММЕСЛИ(В12:В14;>500;С12:С14)

=СУММЕСЛИ(В12:В14;=500; С12:С14)

Округление

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

Для округления чисел можно использовать целую группу функций.

Наиболее часто используют функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ().

Синтаксис функции: ОКРУГЛ(А;В),

где A – округляемое число;

В – число знаков после запятой, до которого округляется число.

Задание 4. Создание формулы с использованием функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ().

Функция ОКРУГЛ() отбрасывает цифры меньшие 5, а цифры большие 5 округляет до следующего разряда.

Синтаксис функций ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ() точно такой же, что и у функции ОКРУГЛ().

Ваши действия:

1. Введите в ячейки А18:А21 исходные данные, показанные на рис. 7.7.

Рис. 7.7. Примеры функции ОКРУГЛ(), ОКРУГЛВВЕРХ() и ОКРУГЛВНИЗ()

2. В столбце В произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛ().

3. В столбце С произведите округление с точностью до 1 знака после запятой с помощью функции ОКРУГЛ().

4. В столбце С произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВВЕРХ() (см. рис. 7.7).

5. В столбце D произведите округление с точностью до 2-х знаков после запятой с помощью функции ОКРУГЛВНИЗ() (см. рис. 7.7).

Функция ОКРУГЛВВЕРХ() при округлении любые цифры округляет до следующего разряда. Функция ОКРУГЛВНИЗ() при округлении отбрасывает любые цифры.

Возведение в степень

Для возведения в степень используют функцию СТЕПЕНЬ().

Синтаксис функции: СТЕПЕНЬ(А;В),

где A – число, возводимое в степень;

В – показатель степени, в которую возводится число.

Задание 5. Создание формулы с использованием функции СТЕПЕНЬ().

Ваши действия:

1. Введите в ячейки А24:А26 исходные данные для вычислений, как показано на рис. 7.8.

Рис. 7.8. Примеры функции СТЕПЕНЬ()

2. В столбце В произведите возведение в 4-ю степень.

3. В столбце С произведите возведение в степень ½.

4. В столбце D произведите возведение в отрицательную степень -2.

Отрицательные числа можно возводить только в степень, значение которой является целым числом. В остальном ограничений на возведение в степень нет.

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

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

Рассмотрим некоторые из них: СРЗНАЧ(), МИН(), МАХ(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ(), СЧЕТ(), СЧЕТЗ(), СЧИТАТЬПУСТОТЫ(), СЧЕТЕСЛИ().

Нахождение крайних значений

Для нахождения крайних (наибольшего или наименьшего) значений в множестве данных используют функции МАКС() и МИН().

Синтаксис функции: МАКС(А),

где A – список от 1 до 255 элементов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.

Задание 6. Создание формулы с использованием функции МАКС().

Ваши действия:

1. Введите в ячейки А28:Е28 числа 10, 7, 9, 27, 2, как показано на рис. 7.9.

2. В ячейки А29 и А30 ведите следующие формулы:

=МИН(А28:Е28)

=МИН(А28:Е28; 0)

Результат вычислений показан на рис. 7.9.

Рис. 7.9. Примеры функции МИН()

Функция МИН() – находит наименьшее значение в диапазоне ячеек. Функция МИН() имеет такой же синтаксис, что и функция МАКС(). Функции МАКС() и МИН() только определяют крайние значения, но не показывают, в какой ячейке эти значения находятся.

3. В ячейки А32 и А33 введите следующие формулы:

=МАКС(А28:Е28)

=МАКС(А28:Е28;30)

Результат вычислений показан на рис. 7.10.

Рис. 7.10. Примеры функции МАКС()

В тех случаях, когда требуется найти не самое большое (самое маленькое) значение, а значение, занимающее определенное положение в множестве данных (например, второе или третье по величине), следует использовать функции НАИБОЛЬШИЙ() или НАИМЕНЬШИЙ().

Синтаксис функции: НАИБОЛЬШИЙ(А;В),

где A – список от 1 до 30 элементов, среди которых требуется найти значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются;

В – позиция (начиная с наибольшей) в множестве данных. Если требуется найти второе значение по величине, то указывается позиция 2, если третье, то позиция 3 и т. д.

4. В ячейки А36, В36 и С36 введите следующие формулы:

=НАИБОЛЬШИЙ(А28:Е28;2)

=НАИБОЛЬШИЙ(А28:Е28;3)

=НАИБОЛЬШИЙ(А28:Е28;4)

Результаты вычислений показаны на рис. 7.11

Рис. 7.11. Примеры функции НАИБОЛЬШИЙ()

Функция НАИМЕНЬШИЙ() имеет такой же синтаксис, что и функция НАИБОЛЬШИЙ().

5. В ячейки А39, В39