Лаборатораторная работа № 5

 

Функции в 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%))