Функции Excel - знакомство. Как работать с функциями в R: от использования чужих, до создания своих

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

Для чего нужны функции в R?

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

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

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

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

Использование существующих функций в R

К счастью существует множество готовых функций в R. Так например, mean() , summary() , read.table() , lm() являются базовыми функциями, которыми можно пользоваться без применения сторонних пакетов. Чтобы узнать какие именно аргументы требуется ввести для использования функции введите в консоль знак вопроса и имя функции, например: ?read.table . На открывшейся странице в разделах Usage и Arguments будет перечень аргументов для использования данной функции. Как правило, критически важные аргументы для работы функции находятся в начале списка. Аргументы, которые не будут заполнены примут значения по умолчанию:

read.table("D:/Folder/mytable.txt", sep = "\t", header = T)

Так как официальный архив R пакетов (CRAN) насчитывает более 11 тысяч , то количество доступных функций для анализа данных стремительно приближается к 1 млн!!! Как же нам проводить поиск нужной функции во всех этих пакетах? Для этого существует сайт rdocumentation.org : удобный сервис поиска функций и пакетов по искомому слову или сочетанию слов.

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

Создание функций: три наглядных примера!

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

Пример 1: сколько будет 2+2×2?

Создадим простейшую функцию, основанную исключительно на арифметических действиях. Например, вычислим знакомый нам с начальной школы пример: сколько будет 2+2*2? Усложним немного: 12+12*12? Ну и в завершении арифметических упражнений 42+42*42? Как Вы могли заметить, все эти примеры основаны на одной и той же формуле: a+a*a . Создание функции в R будет идеальным решением для подобного рода задач.

school <- function(a){ b = a+a*a print(b) } school(2) school(12) school(22)

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

Пример 2: от горшка два вершка

В детстве мы все читали русские народные сказки. Меня, например, всегда интересовало что значит фраза "от горшка два вершка", а точнее сколько это в сантиметрах. Думаю, пришло время получить ответ: создадим конвертер вершков в R при помощи новой функции convershok().

Один вершок равен 4.445 см. Пусть программа выводит на экран предложение от том, что столько вершков равняется столько-то сантиметров, используя базовую функцию для объединения текстовых и числовых объектов paste() . Также мы хотим, чтобы полученное значение сохранялись как отдельный объект, для чего в конце функции добавим return(vershok) .

convershok <- function(n){ vershok <- n * 4.445 print(paste(n, "vershok", "=", vershok, "cm")) return(vershok) } x <- convershok(2)

Отлично, мы получили заветное сообщение и сохранили 8.89 (см), как объект x. Вот только о каком именно горшке идет речь в поговорке не сказано, поэтому измерьте высоту первого попавшегося горшка, добавьте это значение к переменной x и напишите в комментариях, сколько у Вас получилось;-)

Пример 3: ноутбук в кредит, сколько придется переплатить?

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

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

sp = p * (t + 1) / 24 , где:

sp - сумма процента
p - годовая процентная ставка
t - срок кредита (месяцев).

Реализуем эту формулу в R, после чего добавим к цене ноутбука (n ) высчитанный суммарный процент (sp ), помноженный на цену ноутбука товара (n ):

result <- function(n, p, t){ sp <- p*(t + 1)/24 total <- n + n*sp/100 print(paste("sp =", round(sp, 2), "%; ", "total price =", total, "rubles")) } result(30000, 35, 1) result(30000, 35, 3) result(30000, 35, 12)

Как Вы видите, 35% годовых отнють не означает, что студент будет платить за кредит 35% от текущей стоимости ноутбука: в реальности за год он переплатит на 19%. Другой интересный вывод в том, что кредитный процент на один месяц гораздо выше, чем усредненный месячный процент на три месяца и тем более на год. То есть брать кредиты на долгий срок "выгодно" :-)

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

Заключение

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

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

Варианты записи функции ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

Форма массива

Форма массива очень похожа на функции ВПР и ГПР . Основная разница в том, что ГПР ищет значение в первой строке диапазона, ВПР в первом столбце, а функция ПРОСМОТР либо в первом столбце, либо в первой строке, в зависимости от размерности массива. Есть и другие отличия, но они менее существенны.

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

Векторная форма

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

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

Пример 1

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

В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР . Формула будет выглядеть следующим образом:

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор. Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter , убеждаемся, что все верно.

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter , мы получим требуемый результат:

При использовании функции ПРОСМОТР в Excel значения в просматриваемом векторе должны быть отсортированы в порядке возрастания, иначе она может вернуть неверный результат.

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

Которые позволяют оптимизировать работу в MS Excel. А сегодня хотим предложить вашему вниманию новую порцию советов для ускорения действий в этой программе. О них расскажет Николай Павлов - автор проекта «Планета Excel», меняющего представление людей о том, что на самом деле можно сделать с помощью этой замечательной программы и всего пакета Office. Николай является IT-тренером, разработчиком и экспертом по продуктам Microsoft Office, Microsoft Office Master, Microsoft Most Valuable Professional. Вот проверенные им лично приёмы для ускоренной работы в Excel. ↓

Быстрое добавление новых данных в диаграмму

Если для вашей уже построенной диаграммы на листе появились новые данные, которые нужно добавить, то можно просто выделить диапазон с новой информацией, скопировать его (Ctrl + C) и потом вставить прямо в диаграмму (Ctrl + V).

Эта функция появилась только в последней версии Excel 2013, но она стоит того, чтобы обновиться до новой версии досрочно. Предположим, что у вас есть список полных ФИО (Иванов Иван Иванович), которые вам надо превратить в сокращённые (Иванов И. И.). Чтобы выполнить такое преобразование, нужно просто начать писать желаемый текст в соседнем столбце вручную. На второй или третьей строке Excel попытается предугадать наши действия и выполнит дальнейшую обработку автоматически. Останется только нажать клавишу Enter для подтверждения, и все имена будут преобразованы мгновенно.

Подобным образом можно извлекать имена из email’ов, склеивать ФИО из фрагментов и т. д.

Копирование без нарушения форматов

Вы, скорее всего, знаете про «волшебный» маркер автозаполнения - тонкий чёрный крест в правом нижнем углу ячейки, потянув за который можно скопировать содержимое ячейки или формулу сразу на несколько ячеек. Однако есть один неприятный нюанс: такое копирование часто нарушает дизайн таблицы, т. к. копируется не только формула, но и формат ячейки. Этого можно избежать, если сразу после протягивания чёрным крестом нажать на смарт-тег - специальный значок, появляющийся в правом нижнем углу скопированной области.

Если выбрать опцию «Копировать только значения» (Fill Without Formatting), то Microsoft Excel скопирует вашу формулу без формата и не будет портить оформление.

В последней версии Excel 2013 появилась возможность быстро отобразить на интерактивной карте ваши геоданные, например продажи по городам и т. п. Для этого нужно перейти в «Магазин приложений» (Office Store) на вкладке «Вставка» (Insert) и установить оттуда плагин Bing Maps. Это можно сделать и по прямой ссылке с сайта , нажав кнопку Add. После добавления модуля его можно выбрать в выпадающем списке «Мои приложения» (My Apps) на вкладке «Вставка» (Insert) и поместить на ваш рабочий лист. Останется выделить ваши ячейки с данными и нажать на кнопку Show Locations в модуле карты, чтобы увидеть наши данные на ней.

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

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

Вы когда-нибудь подбирали входные значения в вашем расчёте Excel, чтобы получить на выходе нужный результат? В такие моменты чувствуешь себя матёрым артиллеристом, правда? Всего-то пара десятков итераций «недолёт - перелёт», и вот оно, долгожданное «попадание»!

Microsoft Excel сможет сделать такую подгонку за вас, причём быстрее и точнее. Для этого нажмите на вкладке «Вставка» кнопку «Анализ „что если“» и выберите команду «Подбор параметра» (Insert - What If Analysis - Goal Seek). В появившемся окне задайте ячейку, где хотите подобрать нужное значение, желаемый результат и входную ячейку, которая должна измениться. После нажатия на «ОК» Excel выполнит до 100 «выстрелов», чтобы подобрать требуемый вами итог с точностью до 0,001.

Если этот подробный обзор охватил не все полезные фишки MS Excel, о которых вы знаете, делитесь ими в комментариях!

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

• таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
• таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
• в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) - значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

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

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


Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля "Дерево" Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле "Возраст" больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , 0 (не равно нулю), >=7 , "Яблоня" .

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных - ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле - в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках ("Дерево", "Возраст" или "Урожай"), так и число, задающее положение столбца в таблице: 1 - для первого поля(столбца) в таблице "Дерево", 2 - для второго поля "Высота", 3 - для третьего поля "Возраст" и так далее.
критерий - ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

• ДСРЗНАЧ (DAVERAGE) - Вычисляет среднее значение выбранных записей базы данных:
  =ДСРЗНАЧ(A6:E12 ;5; A1:F3)
  =ДСРЗНАЧ(A6:E12 ;"Прибыль"; A1:F3)
  =DAVERAGE(A6:E12 ,5, A1:F3) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .
• БСЧЁТ (DCOUNT) - Подсчитывает количество числовых ячеек в базе данных:
  =БСЧЁТ(A6:E12 ;5; A1:F3)
  =БСЧЁТ(A6:E12 ;"Прибыль"; A1:F3)
  =DCOUNT(A6:E12 ,5, A1:F3) вернет число 2 , т.к. только две строки в таблице отвечают критериям
• БСЧЁТА (DCOUNTA) - Подсчитывает количество непустых ячеек в базе данных:
  =БСЧЁТА(A6:E12 ;4; A1:F3)
  =БСЧЁТА(A6:E12 ;"Прибыль"; A1:F3)
  =DCOUNTA(A6:E12 ,4, A1:F3) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце "Прибыль"
• БИЗВЛЕЧЬ (DGET) - Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
  =БИЗВЛЕЧЬ(A6:E12 ;5; A1:F3)
  =БИЗВЛЕЧЬ(A6:E12 ;"Прибыль"; A1:F3)
  =DGET(A6:E12 ,5, A1:F3) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
  =БИЗВЛЕЧЬ(A6:E12 ;5; A1:F2) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 - Яблона, высота 5)
• ДМАКС (DMAX) - Находит максимальное значение среди выделенных записей базы данных:
  =ДМАКС(A6:E12 ;5; A1:F3)
  =ДМАКС(A6:E12 ;"Прибыль"; A1:F3)
  =DMAX(A6:E12 ,5, A1:F3) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк.
• ДМИН (DMIN) - Находит минимальное значение среди выделенных записей базы данных:
  =ДМИН(A6:E12 ;5; A1:F3)
  =ДМИН(A6:E12 ;"Прибыль"; A1:F3)
  =DMIN(A6:E12 ,5, A1:F3) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям
• БДПРОИЗВЕД (DPRODUCT) - Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
  =БДПРОИЗВЕД(A6:E12 ;3; A1:F3)
  =БДПРОИЗВЕД(A6:E12 ;"Возраст"; A1:F3)
  =DPRODUCT(A6:E12 ,3, A1:F3) вернет 210 , т.к. будут перемножены все значения столбца "Возраст", отвечающие критериям(14*15=210)
• ДСТАНДОТКЛ (DSTDEV) - Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
  =ДСТАНДОТКЛ(A6:E12 ;4; A1:F3)
  =ДСТАНДОТКЛ(A6:E12 ;"Урожайность"; A1:F3)
  =DSTDEV(A6:E12 ,4, A1:F3) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям.
• ДСТАНДОТКЛП (DSTDEVP) - Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
  =ДСТАНДОТКЛП(A6:E12 ;4; A1:F3)
  =ДСТАНДОТКЛП(A6:E12 ;"Урожайность"; A1:F3)
  =DSTDEVP(A6:E12 ,4, A1:F3) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
• БДСУММ (DSUM) - Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
  =БДСУММ(A6:E12 ;5; A1:F3)
  =БДСУММ(A6:E12 ;"Прибыль"; A1:F3)
  =DSUM(A6:E12 ,5, A1:F3) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
  =БДСУММ(A6:E12 ;5; A1:A2)
  =DSUM(A6:E12 ,5, A1:A2) вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
• БДДИСП (DVAR) - Оценивает дисперсию по выборке из выделенных записей базы данных:
  =БДДИСП(A6:E12 ;4; A1:A2)
  =БДДИСП(A6:E12 ;"Урожайность"; A1:A2)
  =DVAR(A6:E12 ,4, A1:A2) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
• БДДИСПП (DVARP) - Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
  =БДДИСПП(A6:E12 ;4; A1:A2)
  =БДДИСПП(A6:E12 ;"Урожайность"; A1:A2)
  =DVARP(A6:E12 ,4, A1:A2) вернет 10,66667 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

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

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

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

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

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

Математические функции 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).

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

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

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

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

Задача 2.

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

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

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

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

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

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

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

· сумму дохода за 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 на новый лист текущей книги.

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

Удержано .

Задача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 создайте формулу для вычисления возраста студентов, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ.