Функции в Excel
Задание 1. Работа со статистическими функциями
Для знакомства со статистическими функциями предлагается составить следующую таблицу:
| A | B | C | D | E | F | G | |
| Ведомость начисления заработной платы | |||||||
| Рабочих дней = | |||||||
| N п/п | Ф.И.О. | Оклад (тенге) | Отработа но дней | Начислено (тенге) | Удержано (тенге) | К выдаче (тенге) | |
| 1. | Амантаев С.Е. | 8200,00 | =((C4/$D$2)*D4) | =E4*0,12 | =E4-F4 | ||
| 2. | Бартоев Г.А. | 10000,00 | =((C5/$D$2)*D5) | =E5*0,12 | =E5-F5 | ||
| 3. | Вильсон С.А. | 9700,00 | =((C6/$D$2)*D6) | =E6*0,12 | =E6-F6 | ||
| 4. | Ильясов А.Ж. | 11500,00 | =((C7/$D$2)*D7) | =E7*0,12 | =E7-F7 | ||
| 5. | Исмаилов К.К. | 8500,00 | =((C8/$D$2)*D8) | =E8*0,12 | =E8-F8 | ||
| 6. | Иманов Р.К. | 8200,00 | =((C9/$D$2)*D9) | =E9*0,12 | =E9-F9 | ||
| 7. | Карцев Б.К. | 9700,00 | =((C10/$D$2)*D10) | =E10*0,12 | =E10-F10 | ||
| 8. | Локшин Г.А. | 9700,00 | =((C11/$D$2)*D11) | =E11*0,12 | =E11-F11 | ||
| 9. | Липов Б.Б. | 10100,00 | =((C12/$D$2)*D12) | =E12*0,12 | =E12-F12 | ||
| 10. | Цой А.Г. | 10000,00 | =((C13/$D$2)*D13) | =E13*0,12 | =E13-F13 | ||
| Итого: | =СУММ(E4:E13) | =СУММ(F4:F13) | =СУММ(G4:G13) |
Примечание:
При копировании формулы из ячейки E4 (расчет начисленной суммы в зависимости от отработанных рабочих дней, оклада работника и количества рабочих дней в месяце) в ячейки Е4:Е13 необходимо, чтобы число рабочих дней в месяце было неизменным, для чего адрес ячейки D2 в формуле необходимо сделать абсолютным. При этом удобнее всего использовать функциональную клавишу F4, предварительно поместив в ячейку табличный курсор, тогда адрес этой ячейки будет иметь вид: $D$2.
Следует также обратить внимание, что необходим единый стандарт установки разделителя между целой и дробной частью числа "," и разделителя элементов списка ";". Эти установки производятся через главное меню Windows: Пуск - Настройка, Панель управления, Язык и стандарты, Числа…. Если же разделителем между целой и дробной частью числа выбрана ".", то разделителем элементов списка может быть "," или "; ".
При выполнении КОНТРОЛЬНОГО ПРИМЕРА 1 необходимо получить две таблицы: таблица 1 – с формулами, таблица 2 - с числовыми значениями (переход в режим формул и обратно: ФОРМУЛЫ-ЗАВИСИМОСТИ ФОРМУЛ-ПОКАЗАТЬ ФОРМУЛЫ).
Фрагмент Таблицы 2 в режиме отображения формул
| СЧЕТ= | =СЧЁТ (D2:D13) | ||||||
| СЧЕТ3= | =СЧЁТЗ (D2:D13) | ||||||
| MAХ= | =МАКС(E4:E13) | ||||||
| МИН= | =МИН(E4:E13) | ||||||
| РАНГуб. = | =РАНГ(7937,6;G4:G13;0) | ||||||
| РАНГвозр. = | =РАНГ(7937,6;G4:G13;1) | ||||||
| СРЗНАЧ= | =СРЗНАЧ (F4:F13) | ||||||
| КВАДР ОТКЛ= | =КВАДРОТКЛ (F4:F13) | ||||||
| МЕДИАНА= | =МЕДИАНА (F4:F13) |
Задание 2. Работа с логическим функциями
Для знакомства с логическими функциями создадим следующую таблицу:
Таблица 3
| А | B | C | D | E | ||||
| Ведомость удержаний по кредитам | ||||||||
| Ф.И.О. | Начислено | Задолженность по видам кредитов | Удержано | |||||
| (тенге) | Кредит за товары | Кредит на строи-тельство | (тенге) | |||||
| Ашитов Г.Е. | 12800,00 | 6400,00 | =ЕСЛИ(C4>0;B4*10%;"-") | |||||
| Бредун Э.Я. | 10300,00 | =ЕСЛИ(C5>0;B5*10%;"-") | ||||||
| Валиев С.Р. | 13100,00 | 5000,00 | 95000,00 | =ЕСЛИ(C6>0;B6*10%;"-") | ||||
| Ким Н.Н. | 12500,00 | 26000,00 | =ЕСЛИ(C7>0;B7*10%;"-") | |||||
| Стамбеков Б.С. | 9800,00 | 100000,00 | =ЕСЛИ(C8>0;B8*10%;"-") | |||||
Скопировав Таблицу 3 на новый лист ЕХСЕL, очистим ячейку Е4:Е8 и запишем в них (вначале в ячейку Е4, а затем скопировав в Е5:Е8) новую логическую функцию, которая позволит сделать выборку работников с задолженностью и по кредиту за товары, и по кредиту за строительство.
Таблица 6
| А | B | C | D | E | ||||
| Ведомость удержаний по кредитам | ||||||||
| Ф.И.О. | Начислено | Задолженность по видам кредитов | Удержано | |||||
| (тенге) | Кредит за товары | Кредит на строи-тельство | (тенге) | |||||
| Ашитов Г.Е. | 12800,00 | 6400,00 | =ЕСЛИ(И(C4>0;D4>0);B4*20%;"-") | |||||
| Бредун Э.Я. | 10300,00 | =ЕСЛИ(И(C5>0;D5>0);B5*20%;"-") | ||||||
| Валиев С.Р. | 13100,00 | 5000,00 | 95000,00 | =ЕСЛИ(И(C6>0;D6>0);B6*20%;"-") | ||||
| Ким Н.Н. | 12500,00 | 26000,00 | =ЕСЛИ(И(C7>0;D7>0);B7*20%;"-") | |||||
| Стамбеков Б.С. | 9800,00 | 100000,00 | =ЕСЛИ(И(C8>0;D8>0);B8*20%;"-") | |||||
Скопировав Таблицу 3 на новый лист ЕХСЕL, очистим ячейки Е4:Е8 и запишем в них (вначале в ячейку Е4, а затем скопировав в Е5:Е8) новую логическую функцию, позволяющую сделать выборку работников, имеющих долг или по одному виду кредита или по двум видам кредита одновременно.
Таблица 9
| А | B | C | D | E | ||||
| Ведомость удержаний по кредитам | ||||||||
| Ф.И.О. | Начислено | Задолженность по видам кредитов | Удержано | |||||
| (тенге) | Кредит за товары | Кредит на строи-тельство | (тенге) | |||||
| Ашитов Г.Е. | 12800,00 | 6400,00 | =ЕСЛИ(ИЛИ(C4>0;D4>0);B4*10%;"-") | |||||
| Бредун Э.Я. | 10300,00 | =ЕСЛИ(ИЛИ(C5>0;D5>0);B5*10%;"-") | ||||||
| Валиев С.Р. | 13100,00 | 5000,00 | 95000,00 | =ЕСЛИ(ИЛИ(C6>0;D6>0);B6*10%;"-") | ||||
| Ким Н.Н. | 12500,00 | 26000,00 | =ЕСЛИ(ИЛИ(C7>0;D7>0);B7*10%;"-") | |||||
| Стамбеков Б.С. | 9800,00 | 100000,00 | =ЕСЛИ(ИЛИ(C8>0;D8>0);B8*10%;"-") | |||||
Скопируем Таблицу 3 на новый лист ЕХСЕL, очистим ячейки Е4:Е8, запишем в ячейку Е4 вложенную логическую функцию, а затем скопируем ее ячейки Е5:Е8. Полученная таблица будет иметь вид:
Таблица 11
| А | B | C | D | E | |||
| Ведомость удержаний по кредитам | |||||||
| Ф.И.О. | Начислено | Задолженность по видам кредитов | Удержано | ||||
| (тенге) | Кредит за товары | Кредит на строи-тельство | (тенге) | ||||
| Ашитов Г.Е. | 12800,00 | 6400,00 | =ЕСЛИ(И(C4>0;D4>0);B4*20%;ЕСЛИ(И(C4=0;D4=0);"нет кредита";B4*10%)) | ||||
| Бредун Э.Я. | 10300,00 | =ЕСЛИ(И(C5>0;D5>0);B5*20%;ЕСЛИ(И(C5=0;D5=0);"нет кредита";B5*10%)) | |||||
| Валиев С.Р. | 13100,00 | 5000,00 | 95000,00 | =ЕСЛИ(И(C6>0;D6>0);B6*20%;ЕСЛИ(И(C6=0;D6=0);"нет кредита";B6*10%)) | |||
| Ким Н.Н. | 12500,00 | 26000,00 | =ЕСЛИ(И(C7>0;D7>0);B7*20%;ЕСЛИ(И(C7=0;D7=0);"нет кредита";B7*10%)) | ||||
| Стамбеков Б.С. | 9800,00 | 100000,00 | =ЕСЛИ(И(C8>0;D8>0);B8*20%;ЕСЛИ(И(C8=0;D8=0);"нет кредита";B8*10%)) | ||||
