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

Лабораторная работа «Использование встроенных функций в расчетах MS Excel» Цель работы. Приобретение основных навыков использования функций в расчетных таблицах МS Excel  Краткие теоретические сведения. 1. Ввод формулы Формула является основным средством для анализа данных. С помощью формул можно складывать,  умножать и сравнивать данные, а также объединять значения. Формулы могут ссылаться на ячейки текущего  листа, листов той же книги или других книг.  Формула может включать  значения констант и ссылки на другие ячейки или на диапазоны ячеек, а  также на имена или заголовки, представляющие ячейки или диапазоны ячеек, функции.  Ссылки различают ОТНОСИТЕЛЬНЫЕ (А2, В5,  Е10) и АБСОЛЮТНЫЕ ($A$2, $В$5,  $Е$10). Арифметические  операции  + ­ * / ^ % сложение Вычитание Умножение Деление Возведение в степень проценты = А1 + 35 = Е10 – H4 = F2 * E6 = 24 / L2 = 3^2 = B2*20% F5. В   примере   складывается   значение   ячейки  B4  с   числом  25. Полученный результат делится на сумму ячеек  из диапазона D5: Правила ввода: 1. Укажите ячейку, в которую необходимо ввести формулу. 2. Введите = (знак равенства).  Если нажать кнопку Вставка функции, автоматически вставляется знак равенства.  3. Введите формулу. 4. Нажмите клавишу ENTER. 2. Ввод функции MS Excel позволяет работать  с логическими, математическими, статистическими, финансовыми   функциями,  ссылками и массивами и др. функциями. Функции могут быть использованы для выполнения  простых или сложных вычислений. Самой часто используемой функцией является функция СУММ, которая используется для сложения чисел в диапазоне ячеек. Функция СУММ складывает значения в диапазоне D5:F5. Для ввода функций используется Мастер функций ( fx ).  Формула может быть введена в диапазон ячеек  с помощью операций вставки­копирования.   2.1. Стандартные функции SIN(аргумент) COS(аргумент) TAN(аргумент) LN(аргумент) КОРЕНЬ(аргумент) СУММ(аргумент) СРЗНАЧ(аргумент) МИН(аргумент) МАКС(аргумент) ПИ() синус косинус тангенс натуральный  логарифм квадратный  корень числа суммирование  среднее  арифметическое минимум максимум  В качестве аргумента функции может выступать числовая константа, адрес ячейки таблицы или диапазон ячеек 2.2. Условная функция Общий вид условной функции следующий: ЕСЛИ(<условие>;<выражение1>;<выражение2>) Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.  <выражение1> и <выражение2> могут быть числами, формулами или текстами. Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то  значение данной функции определит <выражение1>, в противном случае ­ <выражение2>. ЗАДАНИЕ 1. Создайте таблицу по образцу 1. Введите Таблицу. Установите нужные шрифты и переносы по словам. А  В  С  Покупка  1 Курс доллара в городе  2 Название  3 Нефте6анк  4 Мост­Банк  5 Инвестбанк  6 Асток  7 Олби  8 МАКС/МИН  9 10 11 Покупаем  12 Продаем 26,50р.  26,40р.  26,60р. 26,10р. 26,20р. Сумма  2598,00р.  $121,00 2. В Ячейку В8 введите формулу  вычисления максимальной цены покупки  доллара. В ячейку С8 введите формулу  вычисления минимальной цены продажи  доллара.  3. Введите в ячейку C11 формулу  вычисления суммы (в долларах),  выдаваемой на руки при покупке  долларов: =В11/С8 4. В ячейку С12 введите формулу  вычисления суммы (в рублях),  выдаваемой на руки при продаже  долларов: =B12*B8 Продажа  26,90р.  27,00р.  26,80р.  26,90р.  26,85р.  На руки  5. Сохраните таблицу под именем Tab1.xls ЗАДАНИЕ 2. Составьте таблицу «Прейскурант» 1. Заполните  таблицу по образцу, соблюдая шрифты и переносы по словам. 2.  Название таблицы расположено в трех  объединенных ячейках и выровнено по  центру, как по горизонтали, так и по  вертикали:  Установите на закладке   Для этого выделите ячейки А1:С1   и выполните Формат  Ячейки ...   Выравнивание выравнивание по гори­ зонтали: По центру, выравнивание по  вертикали: По центру.  А  1 Курс пересчета 2 3 Наименование  4 5 Карандаш 6 Мел 7 8 Бумага Тетрадь Ручка С  26,00р. Цена (руб) В  Прейскурант  1 у.е.= Цена (у.е.) 0,50 0,10 1,40 4,00 0,91 3. Названия столбцов Цена(у.е.), Цена(руб) выровнены по центру, как по горизонтали, так и по вертикали. 4. В клетки С4:С8 занесите формулы для вычисления цены товара в рублях. Для этого в ячейку С4 введите  формулу для расчета цены товара в рублях: =B4*C2 и измените в формуле относительный адрес ячейки С2  на абсолютный $C$2. Такое значение позволит правильно копировать его в другие ячейки. Для получения  абсолютного значения нажмите клавишу «F4». 3. Скопируйте значение вниз по столбцу в ячейки С5:С8. 4. Сохраните таблицу под именем Tab2.xls ЗАДАНИЕ 3. Составьте таблицу «Ведомость» 1. Заполните  таблицу по образцу, соблюдая шрифты и переносы по словам. 2. Введите фамилии и инициалы служащих.  З. В ячейку С4 введите произведение минимальной зарплаты ($С$2) на соответствующий коэффициент В4. В формуле должна использоваться абсолютная ссылка на клетку, содержащую минимальную зарплату.  4. 5.  Скопируйте формулу в ячейки С5:СI0.   В ячейку D4 введите налог 16% от зарплаты: =16*С4/100. Скопируйте формулу в ячейки D5:D10.  6. В ячейку Е4 введите сумму к выдаче: =С4­D4. Скопируйте формулу в ячейки Е5:Е10.  7. Задайте в ячейке С11 формулу для суммы всех зарплат C4:C10 и скопируйте ее в ячейки D11, Е11 для вычисления суммы налога и суммы к выдаче.  8. Сохраните таблицу под именем Tab3.xls  ЗАДАНИЕ 4. Составьте таблицу "Прибыль предприятия" 1. Заполните таблицу по образцу, соблюдая шрифты и переносы по словам.  2. Введите формулу расчета чистого дохода в ячейку B4: =B1­B2­B3 3. В ячейки B7, B8, B9, В10 введите формулы для расчета отчислений  соответственно в пенсионный фонд ­ 41,6%, в фонд социальной защиты  ­  5,4%, в фонд заработной платы ­ 1%, в фонд милиции ­ 1 % от чистого дохода. 4. В ячейке В11 задайте формулу для суммирования всех отчислений В7:В10.  D E F 5. В ячейке В13 задайте формулу вычисления прибыли предприятия   =В4­В11 (чистый доход минус сумма отчислений).  6. В ячейке В14 задайте формулу для вычисления налога с прибыли,  который составляет 32%.  5 7. В ячейку В15 введите формулу вычисления чистой прибыли  5 4 =В13 ­ В14.  5 3 4 8. Сохраните таблицу под именем Tab4.xls А  В Заработная плата  3 500 000р. С  300 000р. ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ 1 Доход  2 Материалы  А  В  3 1 4 Чистый доход  5 2 6 Отчисления  7 Пенсионный фонд  Проходной балл: 3 8 Фонд социальной  4 Фамилия Математ Русский Иностранн Сумм Зачисле защиты  5 5 Иванов 3 6 Петров 9 Фонд заработной  5 7 Сидоров 3 8 Смирнов платы  9 Орлова 4 10 Фонд милиции  3 10 Соколов 11 Итого  11 12 13 Прибыль  14 Налог с прибыли  15 Чистая прибыль  1 Ведомость  2 Минимум 5 4 4 3 3 4 83р. А  В  С  D E Фамилия  Имя 3 4 5 6 7 8 9 10 11 ИТОГ Коэффициент Зарплата Налог К выдаче 6,11 4,51 8,45 6,11 8,45 6,11 4,51 ЗАДАНИЕ 5. Составьте таблицу "Абитуриент” 1. Заполните таблицу по образцу, соблюдая шрифты и переносы по словам. 2. В ячейку С3 введите значение проходного балла: 13 3. В ячейке Е5 введите формулу для суммирования баллов за 3 экзамена: = СУММ(B5:D5) 4. Формула в ячейке F5 задается с помощью условной функции: =ЕСЛИ(E5>=$C$3;"Да";"Нет") баллу  отображаться текст –  используется  балл  абитуриентов. Если сумма баллов (E5) ≥ проходному  (С1), то в ячейке F5 будет  Да, в противном случает – Нет. Для проходного балла в формуле  абсолютный адрес $C$3, т.к. проходной  является одинаковым для всех  5. После заполнения 5­ой строки формулами, произведите копирование соответствующих   формул в нижние строки 6. Сохраните таблицу под именем Tab5.xls ЗАДАНИЕ 6. Составьте таблицу "Сальдо” (Сальдо ­ разность между финансовыми поступлениями и расходами за определенный  промежуток времени) 1. Заполните таблицу по образцу, соблюдая шрифты и переносы по словам. А  1 Фирма «ИРИТ» 2 3 Прибыль  4 Продажа  5 Банковский заем  б Общая прибыль  7 Платежи 8 Материалы  9 Услуги  10 Телефоны  11 Зарплата  Письменные  12 принадлежности 13 Почтовый сбор  14 Реклама  15 Выплата займа  16 Всего платежи  17 Сальдо  B С  D  Е  Янва Феврал Март  Всего  2.   Введите   формулы   расчета   общей прибыли в ячейки B6:D6.  100 500 700 450 120 50 30 700 136 2200 28 12 12 40 33 13 1050 1200 40 25 3.   В   ячейке  Е4  задайте   формулу   для суммирования данных в ячейках B4:D4 и скопируйте ее в ячейки E5: E6.  4.   В   ячейке  В16  суммирования платежей и скопируйте ее в задайте   формулу ячейки С16, D16.  5.   В   ячейке  Е8  задайте   формулу   для суммы данных в ячейках B8:D8 и скопируйте ее в ячейки Е9:Е15.  6. В ячейку E16 введите формулу суммы всех платежей.  7. Задайте в ячейке В17 формулу расчета сальдо =B6­B16 и скопируйте ее в ячейки С17:Е17.  8.  Сохраните таблицу под именем Tab6.xls