Построить электронную таблицу.
Внести в таблицу 12-15 записей и выполнить необходимые расчеты.
Правила формирования столбцов:
- Столбцы с заголовками № секции, Товар, Единица измерения, количество, Дата выпуска, Срок годности, Цена, Продано заполняются произвольно.
- Также произвольно заполняется ячейка с текущей датой.
- В следующей ячейке записывается процент уценки равный 13.
- Премиальные составляют 3% (выбрать самостоятельно).
Вывести на печать с использованием фильтра: Пять товаров с минимальной Датой годности.
Используя имеющиеся или специально созданные таблицы (таблицы создаются с использованием фильтров или итоговых таблиц), построить кольцевую диаграмму, отображающую долю каждого товара, в потенциальной выручке и выручке.
Расчет ячейки:
- При выполнении расчетов использовала относительные и абсолютные ссылки.
- В графе Дата годности значения равны сумме ячеек из столбцов Дата производства и Срок годности.
- Столбы Потенциальная выручка и Выручка рассчитывала, как произведение Количества товара на его цену и данных столбца Продано на данные столбца Новая цена.
- Столбец Уценка заполняла с использованием функции Если. Если Текущая дата больше Даты годности менее чем на 3 дня пишется уценка, если Текущая дата больше Даты годности пишется ликвидация.
- В столбце Новая цена значение зависит от Цены и Уценки. Если в соответствующем столбце стоит слово уценка новая цена меньше исходной на процент, указанный в ячейке с процентом уценки, в случае ликвидации цена равна 0, иначе цена не изменяется.
- В столбце Заказ пишется слово заказ, если количество проданного товара совпадает со значением в столбце Количество.
- Премия начисляется, если выручка совпадает с потенциальной выручкой и ее величина больше 20000. При этом величина премии составляет процент от выручки, указанный в ячейке премиальные.
- В строке Итого в столбцах Потенциальная выручка, Выручка, Премия значения равны сумме вышестоящих ячеек. В столбце Уценка вычисляла количество наименований товара, подлежащих уценке, в ячейке ниже количество наименований, подлежащих ликвидации. В столбце Заказ количество наименований, подлежащих заказу. В остальных столбцах эту строку не заполняла.
Таблица 1 – Исходная таблица
текущая дата | 12.12.2015 | ||||||||||||||
процент уценки | |||||||||||||||
премиальные | 3% | ||||||||||||||
№п/п | № секции | товар | ед. измерения | кол-во | дата производ | срок годности | дата годности | цена | потенциальная выручка | уценка | новая цена | продано | заказ | выручка | премия |
Творог "Бежин луг" | гр | 28.11.2015 | 13.12.2015 | 41,00р. | 1 025,00р. | уценка | 35,67р. | Заказ | 891,75р. | ||||||
Творог "Домик в деревне" | гр | 04.12.2015 | 19.12.2015 | 43,50р. | 913,50р. | ликвидация | 43,50р. | 652,50р. | |||||||
Йогурт "Чудо" | мл | 01.12.2015 | 15.01.2016 | 32,00р. | 480,00р. | ликвидация | 32,00р. | 384,00р. | |||||||
Йогурт "Летний день" | мл | 03.12.2015 | 17.01.2016 | 30,00р. | 300,00р. | ликвидация | 30,00р. | 240,00р. | |||||||
Йогурт "Данисимо" | мл | 18.11.2015 | 02.01.2016 | 45,00р. | 630,00р. | ликвидация | 45,00р. | Заказ | 630,00р. | ||||||
Молоко "Простоквашино" | литр | 14.11.2015 | 14.12.2015 | 53,00р. | 371,00р. | уценка | 46,11р. | 184,44р. | |||||||
Молоко "33 коровы" | литр | 15.11.2015 | 15.12.2015 | 51,00р. | 459,00р. | уценка | 44,37р. | 310,59р. | |||||||
Молоко "Домик в деревне" | литр | 18.11.2015 | 18.12.2015 | 57,00р. | 228,00р. | ликвидация | 57,00р. | 114,00р. | |||||||
Майонез "Махеев" | мл | 20.10.2015 | 17.02.2016 | 64,00р. | 2 048,00р. | ликвидация | 64,00р. | 1 728,00р. | |||||||
Майонез "Столичный" | мл | 05.10.2015 | 02.02.2016 | 49,50р. | 1 485,00р. | ликвидация | 49,50р. | 1 188,00р. | |||||||
Сливки "Домик в деревне" | мл | 12.10.2015 | 19.10.2015 | 34,60р. | 968,80р. | уценка | 30,10р. | 602,04р. | |||||||
Сметана "Летний день" | гр | 30.11.2015 | 20.12.2015 | 60,50р. | 847,00р. | ликвидация | 60,50р. | 605,00р. | |||||||
Сметана "Простоквашино" | гр | 27.11.2015 | 17.12.2015 | 55,00р. | 935,00р. | ликвидация | 55,00р. | 825,00р. | |||||||
Сметана "Домик в деревне" | гр | 04.12.2015 | 24.12.2015 | 69,50р. | 1 390,00р. | ликвидация | 69,50р. | Заказ | 1 390,00р. | ||||||
Сметана "Бежин луг" | гр | 07.12.2015 | 27.12.2015 | 53,00р. | 636,00р. | ликвидация | 53,00р. | 477,00р. | |||||||
ИТОГО | 12 716,30р. | 10 222,32р. | 0,00 | ||||||||||||
Таблица 2 – Формулы
текущая дата | |||||||||||||||
процент уценки | |||||||||||||||
премиальные | 3% | ||||||||||||||
№п/п | № секции | товар | ед. измерения | кол-во | дата производ | срок годности | дата годности | цена | потенциальная выручка | уценка | новая цена | продано | заказ | выручка | премия |
Творог "Бежин луг" | гр | 28.11.2015 | =F5+G5 | 41,00р. | =E5*I5 | =ЕСЛИ(C$1+3>=H5; "уценка"; "ликвидация") | =ЕСЛИ(K5="уценка";I5-(I5*0,13);I5) | =ЕСЛИ(M5=E5; "Заказ"; " ") | =M5*L5 | =ЕСЛИ(O5=J5;ЕСЛИ(O5>=20000;$C$3*O5;);"0") | |||||
Творог "Домик в деревне" | гр | 04.12.2015 | =F6+G6 | 43,50р. | =E6*I6 | =ЕСЛИ(C$1+3>=H6; "уценка"; "ликвидация") | =ЕСЛИ(K6="уценка";I6-(I6*0,13);I6) | =ЕСЛИ(M6=E6; "Заказ"; " ") | =L6*M6 | =ЕСЛИ(O6=J6;ЕСЛИ(O6>=20000;$C$3*O6;);"0") | |||||
Йогурт "Чудо" | мл | 01.12.2015 | =F7+G7 | 32,00р. | =E7*I7 | =ЕСЛИ(C$1+3>=H7; "уценка"; "ликвидация") | =ЕСЛИ(K7="уценка";I7-(I7*0,13);I7) | =ЕСЛИ(M7=E7; "Заказ"; " ") | =L7*M7 | =ЕСЛИ(O7=J7;ЕСЛИ(O7>=20000;$C$3*O7;);"0") | |||||
Йогурт "Летний день" | мл | 03.12.2015 | =F8+G8 | 30,00р. | =E8*I8 | =ЕСЛИ(C$1+3>=H8; "уценка"; "ликвидация") | =ЕСЛИ(K8="уценка";I8-(I8*0,13);I8) | =ЕСЛИ(M8=E8; "Заказ"; " ") | =L8*M8 | =ЕСЛИ(O8=J8;ЕСЛИ(O8>=20000;$C$3*O8;);"0") | |||||
Йогурт "Данисимо" | мл | 18.11.2015 | =F9+G9 | 45,00р. | =E9*I9 | =ЕСЛИ(C$1+3>=H9; "уценка"; "ликвидация") | =ЕСЛИ(K9="уценка";I9-(I9*0,13);I9) | =ЕСЛИ(M9=E9; "Заказ"; " ") | =L9*M9 | =ЕСЛИ(O9=J9;ЕСЛИ(O9>=20000;$C$3*O9;);"0") | |||||
Молоко "Простоквашино" | литр | 14.11.2015 | =F10+G10 | 53,00р. | =E10*I10 | =ЕСЛИ(C$1+3>=H10; "уценка"; "ликвидация") | =ЕСЛИ(K10="уценка";I10-(I10*0,13);I10) | =ЕСЛИ(M10=E10; "Заказ"; " ") | =L10*M10 | =ЕСЛИ(O10=J10;ЕСЛИ(O10>=20000;$C$3*O10;);"0") | |||||
Молоко "33 коровы" | литр | 15.11.2015 | =F11+G11 | 51,00р. | =E11*I11 | =ЕСЛИ(C$1+3>=H11; "уценка"; "ликвидация") | =ЕСЛИ(K11="уценка";I11-(I11*0,13);I11) | =ЕСЛИ(M11=E11; "Заказ"; " ") | =L11*M11 | =ЕСЛИ(O11=J11;ЕСЛИ(O11>=20000;$C$3*O11;);"0") | |||||
Молоко "Домик в деревне" | литр | 18.11.2015 | =F12+G12 | 57,00р. | =E12*I12 | =ЕСЛИ(C$1+3>=H12; "уценка"; "ликвидация") | =ЕСЛИ(K12="уценка";I12-(I12*0,13);I12) | =ЕСЛИ(M12=E12; "Заказ"; " ") | =L12*M12 | =ЕСЛИ(O12=J12;ЕСЛИ(O12>=20000;$C$3*O12;);"0") |
Продолжение таблицы 2
Майонез "Махеев" | мл | 20.10.2015 | =F13+G13 | 64,00р. | =E13*I13 | =ЕСЛИ(C$1+3>=H13; "уценка"; "ликвидация") | =ЕСЛИ(K13="уценка";I13-(I13*0,13);I13) | =ЕСЛИ(M13=E13; "Заказ"; " ") | =L13*M13 | =ЕСЛИ(O13=J13;ЕСЛИ(O13>=20000;$C$3*O13;);"0") | |||||
Майонез "Столичный" | мл | 05.10.2015 | =F14+G14 | 49,50р. | =E14*I14 | =ЕСЛИ(C$1+3>=H14; "уценка"; "ликвидация") | =ЕСЛИ(K14="уценка";I14-(I14*0,13);I14) | =ЕСЛИ(M14=E14; "Заказ"; " ") | =L14*M14 | =ЕСЛИ(O14=J14;ЕСЛИ(O14>=20000;$C$3*O14;);"0") | |||||
Сливки "Домик в деревне" | мл | 12.10.2015 | =F15+G15 | 34,60р. | =E15*I15 | =ЕСЛИ(C$1+3>=H15; "уценка"; "ликвидация") | =ЕСЛИ(K15="уценка";I15-(I15*0,13);I15) | =ЕСЛИ(M15=E15; "Заказ"; " ") | =L15*M15 | =ЕСЛИ(O15=J15;ЕСЛИ(O15>=20000;$C$3*O15;);"0") | |||||
Сметана "Летний день" | гр | 30.11.2015 | =F16+G16 | 60,50р. | =E16*I16 | =ЕСЛИ(C$1+3>=H16; "уценка"; "ликвидация") | =ЕСЛИ(K16="уценка";I16-(I16*0,13);I16) | =ЕСЛИ(M16=E16; "Заказ"; " ") | =L16*M16 | =ЕСЛИ(O16=J16;ЕСЛИ(O16>=20000;$C$3*O16;);"0") | |||||
Сметана "Простоквашино" | гр | 27.11.2015 | =F17+G17 | 55,00р. | =E17*I17 | =ЕСЛИ(C$1+3>=H17; "уценка"; "ликвидация") | =ЕСЛИ(K17="уценка";I17-(I17*0,13);I17) | =ЕСЛИ(M17=E17; "Заказ"; " ") | =L17*M17 | =ЕСЛИ(O17=J17;ЕСЛИ(O17>=20000;$C$3*O17;);"0") | |||||
Сметана "Домик в деревне" | гр | 04.12.2015 | =F18+G18 | 69,50р. | =E18*I18 | =ЕСЛИ(C$1+3>=H18; "уценка"; "ликвидация") | =ЕСЛИ(K18="уценка";I18-(I18*0,13);I18) | =ЕСЛИ(M18=E18; "Заказ"; " ") | =L18*M18 | =ЕСЛИ(O18=J18;ЕСЛИ(O18>=20000;$C$3*O18;);"0") | |||||
Сметана "Бежин луг" | гр | 07.12.2015 | =F19+G19 | 53,00р. | =E19*I19 | =ЕСЛИ(C$1+3>=H19; "уценка"; "ликвидация") | =ЕСЛИ(K19="уценка";I19-(I19*0,13);I19) | =ЕСЛИ(M19=E19; "Заказ"; " ") | =L19*M19 | =ЕСЛИ(O19=J19;ЕСЛИ(O19>=20000;$C$3*O19;);"0") | |||||
ИТОГО | =СУММ(J5:J19) | =СЧЁТЕСЛИ(K5:K19;K6) | =СЧЁТЕСЛИ(N5:N19; "Заказ") | =СУММ(O5:O19) | =СУММ(P5:P19) | ||||||||||
=СЧЁТЕСЛИ(K5:K19;K5) |
Таблица 3 - Пять товаров с минимальной Датой годности.
текущая дата | 12.12.2015 | ||||||||||||||
Премиальные | 3% | ||||||||||||||
№п/п | № секции | товар | ед. измерения | кол-во | дата производ | срок годности | дата годности | цена | потенциальная выручка | уценка | новая цена | продано | заказ | выручка | премия |
Молоко "Простоквашино" | литр | 14.11.2015 | 14.12.2015 | 53,00р. | 371,00р. | уценка | 46,11р. | 184,44р. | =ЕСЛИ(O5=J5;ЕСЛИ(O5>=20000;$C$3*O5;);"0") | ||||||
Молоко "33 коровы" | литр | 15.11.2015 | 15.12.2015 | 51,00р. | 459,00р. | уценка | 44,37р. | 310,59р. | =ЕСЛИ(O6=J6;ЕСЛИ(O6>=20000;$C$3*O6;);"0") | ||||||
Молоко "Домик в деревне" | литр | 18.11.2015 | 18.12.2015 | 57,00р. | 228,00р. | ликвидация | 57,00р. | 114,00р. | =ЕСЛИ(O7=J7;ЕСЛИ(O7>=20000;$C$3*O7;);"0") | ||||||
Сливки "Домик в деревне" | мл | 12.10.2015 | 19.10.2015 | 34,60р. | 968,80р. | уценка | 30,10р. | 602,04р. | =ЕСЛИ(O8=J8;ЕСЛИ(O8>=20000;$C$3*O8;);"0") | ||||||
Сметана "Простоквашино" | гр | 27.11.2015 | 17.12.2015 | 55,00р. | 935,00р. | ликвидация | 55,00р. | 825,00р. | =ЕСЛИ(O9=J9;ЕСЛИ(O9>=20000;$C$3*O9;);"0") |
Рисунок 1 - Кольцевая диаграмма, отображающая долю каждого товара, в потенциальной выручке и выручке.