Работа с функциями в excel

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

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

Любую функцию можно вызвать тремя способами:

§ написав в строку ввода после знака «=» имя функции и список аргументов (список аргументов заключается в круглые скобки);

§ с помощью кнопки мастера функций в начале строки ввода;

§ командой Формулы/Библиотека функций/ , которая также вызывает мастер функций.

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

Статистические функции. Excelсодержит около 70 статистических функций. Основными из них являются: простейшие статистические функции, функции для дисперсионного и корреляционного анализа, статистические функции для целей прогнозирования.

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

Задача 1.

Рассчитайте выручку от проката машин на 4 часа, 1 день, одну неделю.

1. Составьте таблицу следующего вида. Введите в таблицу заголовок и сохраните в папке Мои документы под именем Функции.xlsx.

2. Выделите ячейки B5:G5.

3. Выполните щелчок на кнопке Автосумма на вкладке Главная/
Редактирование/ .

4. Скопируйте формулу из ячейки G5 в ячейки G6:G7.

5. Рассчитайте выручку от проката машин за май.

6. Скопируйте формулу из ячейки B8 в ячейки C8:G8.

7. Рассчитайте выручку от проката машин в процентах. Для этого выполните:

· установите курсор на ячейку H5;

· наберите формулу =G5/G8 и, не нажимая клавишу Enter, нажмите клавишу F4. Формула примет вид =G5/$G$8, то есть относительная ссылка на ячейку G8 заменится на абсолютную $G$8 (не меняющуюся при копировании формулы в другие ячейки);

· нажмите клавишу Enter;

· скопируйте эту формулу до ячейки H8.

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

· Относительные ссылки, например А2 или С2З, которые всегда изменяются при копировании или перемещении формулы в соответствии с её новым местоположением (при копировании в строку в ссылке на ячейку изменяется имя колонки, при копировании в столбец - номер строки, при копировании по диагонали – и имя колонки и номер строки). При копировании формулы в новую книгу и лист перед ссылкой, входящей в скопированную формулу, появляется имя книги и лист, откуда производилось копирование (STAR! лист 5!А4).

· Абсолютные ссылки, которые перед именем столбца и номером строки содержат символ $. Назначение абсолютной ссылки производится следующим образом: в строке ввода перед ссылкой устанавливается курсор и нажимается клавиша <F4>, например $А$4. Можно сделать то же самое, вводя символ $ с клавиатуры. При копировании абсолютные ссылки остаются неизменными.

· Частично абсолютные (смешанные) ссылки, которые при копировании корректируются частично. Символ $ стоит или перед именем столбца, или перед номером строки ($R2, F$5).Например, при копировании формулы, содержащей частично абсолютную ссылку $F5, сохраняется имя столбца F, а номер строки 5 будет изменён.

8. Графу Процент представьте в процентах, используя диалогГлавная/Число/ ,числовой формат Процентный.

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

Задача 2.

Определите выручку от продажи товаров на лотках и тенденцию роста доходов.

1. Составьте таблицу следующей формы на новом листе файла Функции.xlsx.

2. Рассчитайте сумму дохода за 2006, 2005 и 2004гг. Для этого:

· щелкните на ячейке E6, и в строку формул введите формулу для расчета суммы дохода за 2006г. =СУММ(D4:D6);

Сумму дохода за 2005г. найдите с помощью мастера функций. Для этого:

· установите курсор на ячейку E9;

· введите формулу используя Мастер функций: щелкните на кнопке в начале строки ввода (или командой Формулы/Библиотека функций/ ), в поле Функция щелкните на имени функции СУММ(категория Математические), нажмите кнопку <OK>, в появившемся диалогом окне в поле Число1 введите диапазон суммируемых чисел D7:D9, щелкните по кнопке <OK>;

· сумму дохода за 2004г. самостоятельно подсчитайте в ячейке E12 любым способом.

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

3. Рассчитайте среднегодовой доход, максимальные и минимальные доходы. Можно ввести в поле ячейки формулу с описанием операции, которую требуется выполнить (СРЗНАЧ(E6;E9;E12); МАКС(D4:D12); МИН(D4:D12)), или использовать окно мастера функций, выбрав соответствующую функцию в меню Формулы/Библиотека функций/ ,.

4. Определите ранг (номер) элемента в общей совокупности. Для этого в ячейку C4 введите формулу =РАНГ($D4;$D$4:$D$12), где D4 – содержит число, для которого определяется ранг, а D4:D12 – массив чисел, среди которых определяется ранг.

5. Скопируйте формулу в ячейки C5:C12. Сохраните таблицу.

6. Рабочему листу на котором находится таблица, присвойте имя
Задача2.

Задача 3.

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

1. Составьте таблицу следующей формы.

2. В ячейку B9 введите формулу =ОКРУГЛ(СРЗНАЧ(B5:B7);0).

3. В ячейку C5 введите формулу =ABS(B5-$B$9). Скопируйте формулу из ячейки C5 в ячейки C6:C7.

4. В ячейку D5 введите формулу =СТЕПЕНЬ(С5;2). Скопируйте формулу из ячейки D5 в ячейки D6:D7.

5. В ячейку D10 введите формулу =ОКРУГЛ(СРЗНАЧ(D5:D7);0).

6. В ячейку D11 введите формулу =ОКРУГЛ(КОРЕНЬ(D10);0).

7. Очистите ячейки D10 и D11, чтобы произвести расчет дисперсии и стандартного отклонения, используя соответствующие статистические функции.

8. В ячейку D10 введите формулу =ДИСПР(B5:B7).

9. В ячейку D11 введите формулу =СТАНДОТКЛОНП(B5:B7).

10. Сохраните таблицу. Рабочему листу, на котором находится таблица, присвойте имя Задача3.

Задача 4. (Простая функция ЕСЛИ)

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

1. Разработайте таблицу Задолженность по кредиту на новом листе рабочей книги Функции.xlsx в соответствие с рисунком (при необходимости добавьте в книгу новый лист командой Главная/ Ячейки/ /Вставить лист).

2. В ячейку E3 введите логическую функцию, которая будет иметь следующий вид:

= ЕСЛИ (С3 > 0; В3 * 0,1;" - ")

Если задолженность по потребительскому кредиту больше нуля, то необходимо удержать 10% (0,1) с начисленной суммы, в противном случае в ячейке вывести прочерк.

3. Скопируйте формулу на ячейки E4:E8.

4. Проанализируйте результаты в диапазоне E3:E8.

5. Сохраните лист с таблицей под именем Задача4.

Задача 5. (Логическая функция И)

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

1. Скопируйте таблицу Задолженность по кредитус листа Задача4 на новый лист текущей книги.

2. Очистите диапазон ячеек Е3:Е8.

3. В ячейке Е3 рассчитайте удержания по кредитам по формуле:

= ЕСЛИ (И (С3 > 0; D3 >0); В3 * 0,2; " - ")

Если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20% (0,2) с начисленной суммы, в противном случае вывести прочерки.

4. Скопируйте формулу на ячейки E4:E8.

5. Проанализируйте результаты в колонке Удержано.

6. Сохраните лист с таблицей под именем Задача5.

Задача 6. (Логическая функция ИЛИ)

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

1. Скопируйте таблицу Задолженность по кредитус листа Задача4 или с листа Задача5 на новый лист текущей книги.

2. Очистите диапазон ячеек Е3:Е8.

3. В ячейке Е3 рассчитайте удержания по кредитам по формуле:

= ЕСЛИ (ИЛИ (С3 > 0; D3 >0); В3 * 0,1; " - ")

Если одновременно задолженности по потребительскому кредиту и кредиту на жилищное строительство больше нуля, то необходимо удержать 20% (0,2) с начисленной суммы, в противном случае вывести прочерки.

4. Скопируйте формулу на ячейки E4:E8.

5. Проанализируйте результаты в колонке Удержано.

6. Сохраните лист с таблицей под именем Задача6.

Задача 7. (Вложенные логические функции ЕСЛИ)

Найдите в списке сотрудников, у которых имеются одновременно задолженности по обоим видам кредита, и удержите от начисленной суммы 20% в счет погашения кредитов. С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от начисленной суммы. Работникам, не имеющим задолженности по кредиту, в графе Удержано проставьте «нет».

1. Скопируйте таблицу Задолженность по кредитус листа Задача4, Задача5 или Задача6 на новый лист текущей книги.

2. Очистите диапазон ячеек Е3:Е8.

3. В ячейке Е3 рассчитайте удержания по кредитам по формуле:

= ЕСЛИ (И (С3 > 0; D3 >0); В3 * 0,2; ЕСЛИ (И (С3 = 0; D3 =0); "нет"; В3 * 0,1))

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

4. Скопируйте формулу на ячейки E4:E8.

5. Проанализируйте результаты в колонке Удержано.

6. Сохраните лист с таблицей под именем Задача7.

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

Задача 8.

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

1. Добавьте в файл Функции.xlsx новый лист.

2. Составьте таблицу следующего вида.

3. Столбец Порядковый номер заполните числами с 1 по 14, используя автозаполнение.

4. В ячейках D5:D18 создайте формулу для вычисления возраста студентов, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ.

Чтобы вычислить возраст, достаточно вычесть из текущей даты дату рождения и затем полученный результат разделить на число 365 (число дней в году). Деление на 365 необходимо, чтобы перевести результат в годы. Для того, чтобы округлить возраст до целых чисел, рекомендуется использовать в качестве внешней функции функцию ЦЕЛОЕ из категории математические. Другими словами, в ячейке D5 нужно создать следующую формулу:

=ЦЕЛОЕ((СЕГОДНЯ()-C5)/365)

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

5. Скопируйте формулу из ячейки D5 в ячейки D6:D18.

6. В ячейках столбца Юбилей должен содержаться либо текст «юбилей», либо символ «–», в зависимости от того, какое число содержится в соседней ячейке слева. Если в ячейках D5:D18 содержится число кратное 5, будем считать возраст юбилейным.

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

= ЕСЛИ (ОСТАТ (D5;5)=0;"юбилей"; "– ")

В логическом выражении приведенной формулы проверяется условие кратности 5 числа в ячейке D5. Для этого используется математическая функция ОСТАТ.

7. Скопируйте формулу из ячейки E5 в ячейки E6:E18.

8. С помощью условного форматирования ячейки с текстом «юбилей» оформите шрифтом - курсив полужирный, синего цвета.

9. Заполните ячейки F5:F18 формулами для расчета премии юбилярам. Сумма премии равна 50$. Для решения этой задачи используйте функцию ЕСЛИ. Правильно созданная формула в ячейке F5 должна иметь следующий вид:

= ЕСЛИ (E5="юбилей"; 50; " – ").

10. Примените любой из финансовых долларовых форматов для оформления ячеек F5:F18.

11. Вычислите, сколько сотрудников-юбиляров в вашей таблице. Используйте функцию СЧЕТЕСЛИ.

12. Вычислите, сколько сотрудников в вашей таблице моложе 24 лет. Используйте функцию СЧЕТЕСЛИ.

13. Вычислите, сколько сотрудников в вашей таблице старше 25 лет. Используйте функцию СЧЕТЕСЛИ.

14. Проанализируйте полученные результаты.

15. Отформатируйте таблицу: добавьте заголовок, внешние и внутренние границы к ячейкам, заливку.

16. Сохраните таблицу, рабочему листу присвойте имя Дата-Время.