• Главная
• Контакты
• Нашёл ошибку
• Прислать материал
• Добавить в избранное

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

МУЛЬТИМЕДИЙНЫЙ КУРС

Смотрите главу «Программирование на рабочем листе. Формулы и имена», а также «Функции рабочего листа».

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

Создадим первую формулу. Допустим, вы хотите вычислить площадь круга. Конечно, можно взять в руки калькулятор и сделать это. А если вам требуется вычислить площадь круга сто раз при разных значениях радиуса? Представляете, сколько раз вам нужно нажать кнопки калькулятора, при этом записывая куда-то все результаты? Программа Excel может сделать это быстрее.

Итак, всем известно, что площадь круга равняется ?r2, где r – это радиус круга, а ? – константа.

1. Создайте новую книгу Excel или перейдите на чистый лист.

2. В ячейку R1C1 введите текст Радиус, а в ячейку R1C2 – Площадь круга.

3. Щелкните кнопкой мыши на ячейке R2C2. В этой ячейке мы создадим формулу.

4. Введите знак =. Это сообщает программе Excel, что в этой ячейке создается формула.

5. Введите число 3,14. Это число ?, округленное до двух знаков после запятой.

6. Введите знак *. В программе Excel это – знак умножения.

7. Щелкните кнопкой мыши на ячейке R2C1.

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

8. Введите знак ^. Это знак возведения числа в степень.

9. Введите число 2. Это степень, в которую нужно возвести число (рис. 6.1).

Рис. 6.1. Создание формулы для вычисления площади круга

ПРИМЕЧАНИЕ

Текст в ячейках R1C1 и R1C2 нам нужен только в качестве заголовков столбцов для наглядности. В столбец 1 мы будем вводить значение радиуса круга, а в столбце 2 получать результат (площадь).

ПРИМЕЧАНИЕ

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

10. Нажмите клавишу Enter. Формула будет создана, а в ячейке R2C2 появится значение 0.

Мы создали формулу в ячейке R2C2. Щелкните кнопкой мыши на этой ячейке и посмотрите на строку формул. Вы должны увидеть следующую формулу: =3,14*RC[-1]^2. В нашей формуле число 3,14 (?) умножается на значение ячейки, стоящей слева (в этой ячейке мы позже укажем радиус), которое, в свою очередь, возводится в квадрат. В данный момент результат вычисления равен нулю, так как ячейка R2C1 пуста (формула воспринимает это как 0).

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

1. В ячейку R2C1 введите 10 и нажмите клавишу Enter. В ячейке R2C2 появится число 314.

2. В ячейку R2C1 введите число 15 и нажмите клавишу Enter. В ячейке R2C2 появится число 706,5.

Как видите, результат вычисления в ячейке R2C2 автоматически обновляется, как только вы вводите новое значение в ячейку R2C1. Иными словами, вы указываете только радиус, все остальное делает Excel по созданной вами формуле. И это, пожалуй, самый простой пример.

В столбце 3 создадим формулу вычисления длины окружности, которая также будет использовать значение радиуса из столбца 1. Формула длины окружности – 2?r.

1. В ячейку R1C3 введите текст Длина окружности.

2. Щелкните кнопкой мыши на ячейке R2C3.

3. Введите знак =.

4. Введите цифру 2.

5. Введите знак умножения (*).

6. Введите число ? (3,14).

7. Введите знак умножения (*).

8. Щелкните кнопкой мыши на ячейке R2C1. В формуле появится относительный адрес первой ячейки, в которой мы указываем радиус. У вас должна получиться формула следующего вида: =2*3,14*RC[-2].

9. Нажмите клавишу Enter. Если в ячейке R2C1 указано какое-либо число, вы немедленно получите результат вычисления длины окружности в ячейке R2C3.

10. Введите новое значение радиуса в ячейку R2C1 и нажмите клавишу Enter. Обратите внимание, что в ячейках R2C2 и R2C3 значения изменились одновременно. Сколько бы вы ни создали формул, использующих значение ячейки R2C1, результат их вычисления будет обновляться при изменении значения в этой ячейке (рис. 6.2).

Рис. 6.2. Формулы в ячейках R2C2 и R2C3 используют значение ячейки R2C1

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

+ – оператор сложения;

– – оператор вычитания;

* – оператор умножения;

/ – оператор деления;

^ – оператор возведения в степень. Этот оператор ставится после числа (или переменной), которое возводится в степень. После этого оператора указывается значение степени, которое может быть как числом, так и переменной (переменной в данном случае мы считаем ссылку на ячейку со значением);

% – оператор вычисления процента.

Вы заметили, что при создании формул мы указывали ячейку R2C1, но в формулах появлялись значения RC[-1] и RC[-2]? Ранее упоминалось, что ячейка может иметь абсолютный и относительный адреса. Относительный адрес указывает путь к ячейке относительно ячейки с формулой. Так, в формуле вычисления площади круга значение RC[-1] показывает, что данные нужно взять из ячейки той же строки, но на один столбец левее. В формуле длины окружности значение RC[-2] сообщает, что данные необходимо взять из ячейки той же строки, но на два столбца левее. Конечно, вы можете в формулах указать и абсолютный адрес ячейки.

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

1. Выделите ячейку R2C2, то есть ячейку с формулой площади круга.

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

3. Нажав и удерживая кнопку мыши, перетащите рамку выделения в ячейку R6C3 и отпустите кнопку мыши. Результат вычисления площади круга появится в ячейке R6C3.

4. Попробуйте изменить значения радиуса круга в ячейке R2C2. В ячейке R6C3 обновится значение площади.

5. Выделите ячейку R6C3 и посмотрите на строку формул. Формула несколько изменилась, и теперь она выглядит так: =3,14*R[-4]C[-2]^2. Выражение R[-4]C[-2] означает, что данные (радиус) следует искать в ячейке, расположенной на четыре строки выше и два столбца левее.

Куда бы вы ни перетаскивали ячейку с формулой, относительный адрес ячейки радиуса будет меняться и указывать именно на ячейку R2C1. Точно такой же результат вы получите, изменив положение ячейки R2C1 (ячейки со значением радиуса). Куда бы вы ни переместили эту ячейку, формулы по-прежнему будут возвращать верный результат вычислений, но относительный адрес ячейки со значением радиуса в них будет меняться. Относительный адрес в формуле как бы «цепляется» к ячейке, на которую он ссылается, и не позволяет разорвать эту связь при любом перемещении ячейки формулы или ячеек данных.

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

1. Верните данные с радиусом и формулой на свои места (соответственно в ячейки R2C1 и R2C2).

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

3. Скопируйте ее содержимое в буфер обмена. Для этого нажмите кнопку Копировать на вкладке Главная или сочетание клавиш Ctrl+Insert.

4. Выделите ячейку R3C2.

5. Нажав и удерживая клавишу Shift, нажимайте клавишу Стрелка вниз, пока не выделите диапазон до ячейки R10C2.

6. Вставьте содержимое буфера обмена в выделенный диапазон – нажмите кнопку Вставить на вкладке Главная или сочетание клавиш Shift+Insert. В ячейках R3C2?R10C2 появятся нули.

7. Если вы щелкнете кнопкой мыши на любой из ячеек диапазона R3C2?R10C2 и посмотрите на строку формул, то увидите одну и ту же формулу =3,14*RC[-1]^2.

8. В ячейки R2C1?R10C1 введите различные числа, например от 1 до 9 (можете использовать инструмент автозаполнения).

В ячейках R2C2?R10C2 появились разные значения площади круга (рис. 6.3).

Рис. 6.3. В ячейках R2C2?R10C2 содержится одна и та же формула

Каждая формула возвращает результат вычисления в зависимости от радиуса, указанного в ячейке слева. Почему? Ответ прост: в формуле используется относительный адрес, который ссылается не на ячейку R2C1, а на ячейку RC[-1], то есть ячейку, расположенную в той же строке столбцом левее. Если бы в формулах диапазона R2C2?R10C2 использовался абсолютный адрес ячейки R2C1, мы бы получили одинаковые результаты во всех ячейках, поскольку значение радиуса для всех формул бралось бы из одной и той же ячейки. В то же время, если бы понадобилось «размножить» такие же формулы с прямым адресом на целый диапазон, вам пришлось бы в каждой формуле править абсолютный адрес, чтобы указать на ячейку слева.

СОВЕТ

Чтобы изменить относительный адрес в формуле на абсолютный, поместите курсор в конец адреса в строке формул и нажмите клавишу F4, затем клавишу Enter, чтобы записать изменения в формуле. Таким же образом вы можете поменять абсолютный адрес на относительный.

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

Для следующего примера создайте таблицу, изображенную на рис. 6.4.

Рис. 6.4. Таблица для примера порядка вычисления

Допустим, нам нужно сумму содержимого ячеек R1C1 и R1C2 умножить на содержимое ячейки R1C3 и вывести результат в ячейку R2C4.

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

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

2. Введите знак =.

3. Щелкните кнопкой мыши на ячейке R1C1, введите знак + и щелкните кнопкой мыши на ячейке R1C2.

4. Введите знак умножения (*) и щелкните кнопкой мыши на ячейке R1C3.

5. Нажмите клавишу Enter. Ячейка R2C4 выдаст вам результат 310. Это неверный результат.

Попробуем разобраться, почему так получилось. Тот, кто хорошо помнит математику со школы, поймет сразу. Дело в том, что программа Excel при совершении арифметических операций использует элементарные математические правила. В математике сначала производятся операции умножения и деления, а затем – сложения и вычитания. Наша формула сначала умножила содержимое ячейки R1C2 на значение ячейки R1C3, а затем сложила этот результат с содержимым ячейки R1C1, что и привело к неверному результату.

Чтобы наша формула считала правильно, необходимо сумму ячеек R1C1 и R1C2 заключить в скобки. Операции в скобках в математике имеют приоритет, то есть сначала выполняются вычисления в скобках, а потом – за их пределами.

1. Внесите коррективы в строку формул. Исправленная формула должна выглядеть следующим образом: =(R[-1]C[-3]+R[-1]C[-2])*R[-1]C[-1].

2. Нажмите клавишу Enter. Изменения в формуле будут применены, а ячейка R2C4 вернет результат 500, что соответствует нашей задаче и верному решению (рис. 6.5).

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

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

Вот некоторые математические операторы, перечисленные в порядке приоритета выполнения: % (вычисление процента), ^ (возведение в степень), * и / (умножение и деление), + и – (сложение и вычитание). Иными словами, если в формуле производится возведение в степень какого-то числа или переменной, это делается раньше умножения или деления и тем более – сложения и вычитания. Если часть формулы заключена в скобки, то сначала производится вычисление в скобках. В свою очередь, если в скобках также присутствует несколько операторов, то они выполняются в порядке приоритета, о котором мы только что говорили.

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

Для следующего примера используйте таблицу, которую мы создали в предыдущем разделе. В ячейке R2C4 содержится формула, возвращающая произведение ячейки R1C3 и суммы ячеек R1C1 и R1C2. Мы создадим формулу в ячейке R2C5, которая будет делить содержимое ячейки R2C4 на 2.

1. Щелкните кнопкой мыши на ячейке R2C4.

2. Введите знак =.

3. Щелкните кнопкой мыши на ячейке R2C4. В формуле появится относительный адрес этой ячейки.

4. Ведите знак деления (/) и затем цифру 2.

5. Нажмите Enter. В ячейке R2C5 появится результат вычисления 250.

6. Измените значение в любой из ячеек: R1C2 или R1C3. Результат вычисления изменится и в ячейке R2C5, и в ячейке R2C4.

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

В некоторых случаях в ячейке с формулой вы можете вместо результатов увидеть одно из следующих значений: ######, #ЗНАЧ! или #ДЕЛ/0!.

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

• #ЗНАЧ! возникает при вводе некорректного значения в одну из ячеек, ссылка на которую присутствует в формуле. Например, если вы попытаетесь умножить число на текст, получите результат #ЗНАЧ!.

• #ДЕЛ/0! появляется при попытке деления на 0 или на пустое значение ячейки (которое программой Excel расценивается как 0).

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

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

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

Наиболее часто используемая функция в математических расчетах – это КОРЕНЬ.

1. Выделите ячейку R2C2. В эту ячейку мы будем вставлять функцию.

2. Нажмите кнопку Вставить функцию, расположенную в левой части строки формул. Появится диалоговое окно Мастер функций ? шаг 1 из 2 (рис. 6.6).

Рис. 6.6. Диалоговое окно Мастер функций – шаг 1 из 2

3. В раскрывающемся списке Категория выберите пункт Математические.

4. В списке Выберите функцию выберите пункт КОРЕНЬ. Обратите внимание, что в нижней части диалогового окна появляется краткое сообщение о назначении выбранной функции.

5. Нажмите кнопку ОК. Появится диалоговое окно Аргументы функции. В поле Число следует указать число или адрес ячейки, из значения которой следует извлечь корень (рис. 6.7).

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

ПРИМЕЧАНИЕ

Вы также можете воспользоваться кнопкой, расположенной в правой части поля Число, чтобы указать ячейку R1C1 мышью.

ПРИМЕЧАНИЕ

Вы можете не пользоваться диалоговым окном Мастер функций – шаг 1 из 2, а просто ввести в ячейку R2C2 следующий текст: =КОРЕНЬ(R1C1). Со временем, скорее всего, вы запомните часто используемые функции и в некоторых случаях будете вводить их вручную.

6. Введите в поле Число адрес ячейки R1C1. В эту ячейку мы будем подставлять числовые значения, из которых следует извлечь корень.

7. Нажмите кнопку ОК. Диалоговое окно закроется, а в ячейке R2C2 появится значение 0. Функция вернула значение 0, потому что ячейка R1C1 пуста.

8. Введите число 81 в ячейку R1C1 и нажмите клавишу Enter. В ячейке R2C2 появится число 9, которое является корнем числа 81.

Какое бы число вы ни подставляли в ячейку R1C1, ячейка R2C2 вернет вам корень этого числа. Если вы введете отрицательное число в ячейку R1C1, получите результат #ЧИСЛО!. Корень не может быть извлечен из отрицательного числа. Кроме того, сообщение об ошибке (#ЗНАЧ!) вы получите при попытке извлечь корень из текста.

В следующем примере мы переведем величину угла из градусов в радианы.

1. Очистите ячейку R2C2.

2. Нажмите кнопку Вставить функцию. В появившемся диалоговом окне в списке Выберите функцию щелкните кнопкой мыши на пункте РАДИАНЫ и нажмите кнопку ОК (убедитесь, что в диалоговом окне выбрана категория Математические).

3. В поле Число появившегося диалогового окна укажите адрес ячейки R1C1.

4. Нажмите кнопку ОК. Диалоговое окно будет закрыто, а в ячейке R2C2 появится значение ячейки R1C1, переведенное в радианы.

5. Введите в ячейку R1C1 число 180 и нажмите Enter. В ячейке R2C2 появится число 3,141593. Это – число ?. 180 градусов равняются числу ?.

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

1. Очистите ячейку R2C2.

2. Нажмите кнопку Вставить функцию и выберите функцию ОКРУГЛ.

3. Нажмите кнопку ОК. Обратите внимание, что диалоговое окно Аргументы функции на этот раз содержит два поля (рис. 6.8). В первом поле следует указать число, которое требуется округлить (или адрес ячейки), а во втором (Число_разрядов) – количество знаков после запятой, до которого нужно округлить число.

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

ПРИМЕЧАНИЕ

Функция ГРАДУСЫ действует наоборот: переводит значение угла, указанное в радианах, в градусы.

4. В поле Число укажите адрес ячейки R1C1.

5. В поле Число_разрядов введите цифру 2. Мы округлим число до двух знаков после запятой.

6. Нажмите кнопку ОК. Диалоговое окно закроется.

7. В ячейку R1C1 введите число 3,151593 и нажмите клавишу Enter. В ячейке R2C2 появится число 3,15, то есть значение ячейки R1C1, округленное до двух знаков после запятой (рис. 6.9).

Рис. 6.9. Функция возвращает число ячейки R1C1, округленное до сотой доли

Вы можете править аргументы функции, не прибегая к диалоговому окну Мастер функций ? шаг 1 из 2. Изменим аргументы функции, чтобы она округляла значение ячейки R1C1 до четырех знаков после запятой.

1. Выделите ячейку R2C2 и посмотрите на строку формул. После адреса ячейки R1C1 (или ее относительного адреса) через точку с запятой указана цифра 2. Это и есть аргумент функции, указывающий количество знаков после запятой в результате округления.

2. Исправьте аргумент 2 в строке формул на 4 и нажмите клавишу Enter. В ячейке R2C2 появится значение 3,1516.

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

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

1. Очистите ячейку R2C2.

2. Откройте диалоговое окно Мастер функций ? шаг 1 из 2 и выберите функцию РИМСКОЕ.

3. Нажмите клавишу Enter, в появившемся диалоговом окне укажите адрес ячейки R1C1 и нажмите кнопку ОК. В ячейке R2C2 появилась римская цифра III. Римские цифры не имеют дробной части, а поскольку в ячейке R1C1 было указано число 3,151593, функция вернула нам целую часть этого числа в римском формате.

4. В ячейку R1C1 введите число 15 и нажмите клавишу Enter. В ячейке R2C2 появится число XV.

5. Усложним задачу. Введите в ячейку R1C1 число 1145 и нажмите клавишу Enter. Как видите, функция справилась и с этой задачей и вернула число MCXLV.

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

1. Очистите ячейку R2C2, откройте диалоговое окно Мастер функций ? шаг 1 из 2 и выберите функцию СЛУЧМЕЖДУ.

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

3. Закройте диалоговое окно и в ячейках R1C1 и R1C2 укажите два разных числа, например 1 и 6. В ячейке R2C2 появится случайное число от 1 до 6.

Вышеуказанная функция может заменить, например, игральные кубики.

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

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

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

1. Очистите ячейку R2C2.

2. Откройте диалоговое окно Мастер функций ? шаг 1 из 2, перейдите в категорию Логические и выберите функцию ЕСЛИ.

3. В диалоговом окне Аргументы функции вам предлагается ввести аргументы в три поля.

4. В поле Лог_выражение введите следующее выражение: R1C1>R1C2.

Таким образом, мы как бы утверждаем, что число в ячейке R1C1 больше числа R1C2.

В поле Значение_если_истина следует ввести число или текст, который будет возвращать ячейка R2C2 при справедливости указанного нами логического выражения.

1. В поле Значение_если_истина введите текст Да, это правда.

2. В поле Значение_если_ложь введите текст Нет, это неправда. Данный текст будет выводиться в ячейке R2C2, если логическое выражение окажется неверным, то есть число ячейки R1C1 на самом деле окажется меньше числа ячейки R2C2.

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

4. В ячейку R1C1 введите число 5, а в R1C2 – 10. Ячейка R2C2 выдаст сообщение Нет, это неправда. Действительно, число 5 не может быть больше числа 10 (рис. 6.10).

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

5. В ячейку R1C1 введите число 10, а в R1C2 – 5. Ячейка R2C2 выдаст сообщение Да, это правда. Теперь условие логического выражения выполняется: первое число больше второго.

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

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

Мы использовали текстовые сообщения в этом примере для наглядности, хотя в сложных инженерных или математических расчетах может понадобиться подставить числовые значения. Например, если какое-то условие выполняется, то ячейка возвращает число 5 (или, к примеру, синус угла), если не выполняется – другое число (или косинус угла). В свою очередь, другая формула может использовать данные ячейки R2C2 и производить вычисление в зависимости от результатов проверки логического выражения. Например, при расчете температуры подачи воды в систему центрального отопления: если температура воздуха меньше определенного значения – повысить температуру воды до такого-то значения, если температура воздуха выше – понизить температуру воды. Примеров использования функции ЕСЛИ можно привести множество. Вы сами найдете применение этой функции.

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

Функция ГОД возвращает значение года из даты. К примеру, в какой-либо ячейке вашей таблицы указана дата (текущая или нет, неважно). При использовании функции ГОД, где в качестве аргумента указана ячейка с датой, из даты выделяется только значение года.

Проверим это на практике.

1. Очистите ячейку R1C1.

2. Вставьте в ячейку R2C2 функцию ГОД из категории Дата и время.

3. В качестве аргумента функции укажите ячейку R1C1.

4. В ячейку R1C1 введите какую-либо дату, например 12/01/2006, и нажмите клавишу Enter. В ячейке R2C2 появится значение 2006.

Функция ТДАТА возвращает текущую дату и время. Данная функция не имеет аргументов, вы просто вставляете эту функцию в ячейку, в которой появляется значение текущей даты и времени, например 08.01.2006 14:22. Позже (при помощи других функций категории Дата и время) вы можете выделить из этой ячейки отдельно дату или время.

Функция ДЕНЬНЕД возвращает порядковый номер дня недели из даты. Для изучения этой функции выполните следующие практические шаги.

1. Используя функцию ТДАТА, добавьте в ячейку R1C1 текущую дату.

2. Выделите ячейку R2C2 и добавьте в нее функцию ДЕНЬНЕД из категории Дата и время.

3. В диалоговом окне Аргументы функции в поле Дата_в_числовом_формате укажите абсолютный или относительный адрес ячейки R1C1.

4. В поле Тип необходимо указать одно из следующих значений.

• 1 – неделя будет начинаться с воскресенья (как в некоторых западных странах). При этом возвращенное значение 1 будет соответствовать воскресенью, а 7 – субботе.

• 2 – неделя будет начинаться с понедельника. Возвращенное значение 1 будет соответствовать понедельнику, а 7 – воскресенью.

• 3 – неделя также будет начинаться с понедельника, но отсчет дней недели будет вестись с нуля, то есть возвращенное значение 0 будет соответствовать понедельнику, а 6 – воскресенью.

Укажите значение 2.

5. Закройте диалоговое окно кнопкой ОК. В ячейке R1C1 будет указан порядковый номер текущего дня недели.

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

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

Функция ЛЕВСИМВ возвращает заданное количество символов от начала текстовой строки, заданной в качестве аргумента. Рассмотрим действие этой функции на примере.

1. Очистите ячейку R1C1 и введите в нее текст Фрагмент текста.

2. Добавьте в ячейку R2C2 функцию ЛЕВСИМВ из категории Текстовые.

3. В поле Текст диалогового окна Аргументы функции укажите адрес ячейки R1C1.

4. В поле Количество_знаков диалогового окна Аргументы функции нужно ввести количество знаков от начала строки, которые следует возвратить. Мы возвратим слово Фрагмент, которое содержит восемь символов (букв), поэтому укажем в качестве аргумента Количество_символов цифру 8.

5. Закройте диалоговое окно. В ячейке R2C2 появится слово Фрагмент.

Функция СЦЕПИТЬ объединяет текст из нескольких ячеек в одну строку.

1. В ячейку R1C1 введите текст Фрагмент.

2. В ячейку R1C2 введите текст текста.

3. В ячейку R2C2 добавьте функцию СЦЕПИТЬ из категории Текстовые.

4. Откройте диалоговое окно Аргументы функции и в поле Текст1 укажите адрес ячейки R1C1, а в поле Текст2 – адрес ячейки R2C2.

5. Закройте диалоговое окно. В ячейке R2C2 появится текст Фрагменттекста (рис. 6.11). Обратите внимание, что оба слова были соединены без пробела. Если бы мы добавили пробел в конце текста ячейки R1C1 или в начале R1C2, мы бы получили текст Фрагмент текста.

Рис. 6.11. Результат выполнения функции СЦЕПИТЬ

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

Рис. 6.12. Справка программы Excel по функции LOG

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

Как мы уже говорили, функции могут существовать как самостоятельно, так и в составе формул. Например, если вы введете в ячейку формулу =R[-2]C-(КОРЕНЬ(R1C1)*КОРЕНЬ(R1C1)), то получите значение 0 при любом числовом значении ячейки R1C1 (кроме отрицательного). Как видите, в этой формуле используются как арифметические операторы (умножение и вычитание), так и функции (КОРЕНЬ). В процессе создания сложных таблиц вам часто придется использовать в формулах функции.

ПРИМЕЧАНИЕ

Обратите внимание, что по мере добавления аргументов в диалоговом окне Аргументы функции появляются новые поля (Текст3, Текст4 и т. д.). Это дает возможность объединять текст из большего количества ячеек.

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

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

Рис. 6.13. Стрелками показаны пути влияния ячеек

На рис. 6.13 показано, что ячейка R1C2 влияет на результат ячейки R3C2 (по всей видимости, ячейка R3C2 использует данные ячейки R1C2), а ячейка R3C2 влияет на ячейку R5C1 (ячейка R5C1, в свою очередь, использует для расчетов данные ячейки R3C2). Таким образом, вы можете проследить пути влияния ячеек и быстро найти ошибку в формулах в случае неверного итогового результата.

Чтобы скрыть стрелки влияния, нужно просто нажать кнопку Убрать стрелки, расположенную в группе Зависимости формул.

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

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