Задание № 3 по Excel на самостоятельную работу
На 3 листе разработать Вспомогательную таблицу.
Разряд
Тарифная ставка
1
250,00р.
2
350,00р.
3
450,00р.
4
500,00р.
5
650,00р.
Назвать лист Вспомогательную таблицу.
На 4-ом листе разработать Основную таблицу.
Основная таблица
Наименова-ние работ
Разряд
Тарифная ставка
Количество деталей
Начисленно
Всего начислено
по норме
сделано
принято
брак
по норме
за перевы-полнение
всего
удержано за брак
1
2
3
4
5
6
7
8
9
10
11
12
Пайка
1
Монтаж
5
Ремонт
2
Сварка
4
Фрезеровка
3
Используя функцию ВПр на основании Вспомогательной таблицы заполнить столбец Тарифная ставка (Это на листе «Функция ВПР). Раздел «Ссылки и массивы».
=ВПР('Вспомогательная таблица'!A2;'Вспомогательная таблица'!A2:B6;2;1)
или
=ВПР('Функция ВПР'!B$6:B$10;'Вспомогательная таблица'!A$2:B$6;2;1)
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение - значение, которое должно быть найдено в первом столбце табличного
Таблица - два или более столбцов данных.
Номер_столбца - номер столбца в аргументе «таблица», из которого возвращается соответствующее значение. Если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 - значение из второго столбца таблицы и т.д.
Интервальный_просмотр - логическое значение, определяющее, какое соответствие должна найти функция ВПР - точное или приблизительное.
|
Формула |
Описание (результат) |
|
=ВПР(1;A2:C10;2) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке (2,17) |
|
=ВПР(1;A2:C10;3;ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке (100) |
|
=ВПР(0,7;A2:C10;3;ЛОЖЬ) |
Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке (#Н/Д) |
|
=ВПР(0,1;A2:C10;2;ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке (#Н/Д) |
|
=ВПР(2;A2:C10;2;ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равно 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке (1,7 |
Используя функцию Просмотр на основании Вспомогательной таблицы заполнить столбец Тарифная ставка (Это на листе Функция ПРОСМОТР). Раздел «Ссылки и массивы».
=ПРОСМОТР(B$6:B$10;'Вспомогательная таблица'!A$2:A$6;'Вспомогательная таблица'!B$2:B$6)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
Искомое_значение - значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, указывающими на значение.
Просматриваемый_вектор - интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
|
Формула |
Описание (результат) |
|
=ПРОСМОТР(4,19;A2:A6;B2:B6) |
Поиск значения 4,19 в столбце A и возврат значения из столбца B, находящегося в той же строке (оранжевый) |
|
=ПРОСМОТР(5,00;A2:A6;B2:B6) |
Поиск значения 5,00 в столбце A, соотнесение со следующим наименьшим значением (4,19) и возврат значения из столбца B, находящегося в той же строке (оранжевый) |
|
=ПРОСМОТР(7,66;A2:A6;B2:B6) |
Поиск значения 7,66 в столбце A, соотнесение со следующим наименьшим значением (6,39) и возврат значения из столбца B, находящегося в той же строке (синий) |
|
=ПРОСМОТР(0;A2:A6;B2:B6) |
Поиск значения 0 в столбце A и возврат сообщения об ошибке, т. к. ноль меньше наименьшего значения в просматриваемом векторе A2:A7 (#Н/Д) |
Внести данные в столбцы 4, 5 и 6.
Рассчитать столбцы по следующим формулам:
Брак=если сделано>чем принято, то сделано-принято, иначе 0
=ЕСЛИ(E6>F6;E6-F6;0)
По норме 2-е первые строки рассчитать через операцию *, Тарифная ставка*по норме
=C6*D6
3-и последние строчки рассчитать через функцию ПРОИЗВЕД
=ПРОИЗВЕД(C8;D8)
За перевыполнение: если сделано>по норме и если (сделано-по норме-брак>=0), то тарифная ставка*(abs(норма-сделано-брак)), иначе 0; 0
=ЕСЛИ(E6>D6;ЕСЛИ((E6-F6-G6)>=0;C6*(ABS(D6-E6-G6));0);0)
Всего: 2-е первые строки рассчитать через операцию +, По норме+За перевыполнение
=H6+I6
3-и последние строчки рассчитать через функцию СУММ
=СУММ(H8;I8)
Удержано за брак: 2-е первые строки рассчитать через операцию *, тарифная ставка*брак
=H6+I6
3-и последние строчки рассчитать через функцию если: если брак>0,то тарифная ставка*брак, иначе 0
=ЕСЛИ(G8>0;(C8*G8);0)
Всего начислено: 2-е первые строки рассчитать через операцию -, всего-удержано за брак
=J6-K6
3-и последние строчки рассчитать через функцию СУММ: всего+(-удержано за брак)
=СУММ(J8;(-K8))
В книге 2 сделать такую же работу (Таблица совсем другая, но с теми же операциями (формулы и функции) те же самые, что и на данном листе.