Решение нужно срочно экзель файл
Составить таблицу, которая позволяет вычислить стоимость закупленного оборудования трех видов для различных фирм. Несколько фирм (12-14), входящих в объединение, закупают оборудование трех видов. Фирмы могут быть двух типов - совместные и российские. Каждая фирма закупает оборудование одного вида. При закупке оборудования на определенную сумму фирма получает скидку. Стоимость единицы закупленного оборудования 1-го типа - 1000 у.е., 2-го — 500 у.е., 3-го — 250 у.е.. При покупке оборудования на сумму свыше 10000 у.е для российских фирм действует скидка в размере 10% от общей стоимости, а для совместных —5%. Требования к решению: 1. Каждая строка таблицы содержит следующую информацию: · название фирмы, · тип фирмы, · вид закупленного оборудования, · количество единиц оборудования, · стоимость, · скидка, · стоимость с учетом скидки. 2. Подсчеты вести в рублях. 3. Изменение стоимости единицы оборудования, курса доллара и типа фирмы автоматически влечет за собой изменение всех вычисляемых величин. 4. Обеспечить подсчет суммарной стоимости закупленного оборудования с учетом скидки для всех фирм и отдельно для совместных и российских фирм. 5. Построить круговую диаграмму, отражающую долю от общей стоимости совместных и российских фирм. Рекомендации: · хранить курс у.е. в отдельной ячейке; · в отдельных ячейках хранить размер скидки для каждого типа фирмы, · для расчета стоимости закупленного оборудования завести справочник, в котором вход - вид оборудования, выход - стоимость за единицу.
Пояснения к формулам:
• Стоимость в у.е.: =D2*Стоимость_1 (Аналогично для других видов оборудования). Умножает количество единиц оборудования на стоимость одной единицы. Стоимость_1, Стоимость_2 и Стоимость_3 – это именованные диапазоны, указывающие на соответствующие ячейки (J5, J6, J7).
• Скидка (%): =ЕСЛИ(E2>10000;Скидка_Российская;0) (Аналогично для совместных). Проверяет, превышает ли стоимость закупки 10000 у.е. Если да, то применяет соответствующую скидку (из ячеек J3 или J4). Если нет, то скидка равна 0. Скидка_Российская и Скидка_Совместная - именованные диапазоны, указывающие на ячейки J3 и J4 соответственно.
• Стоимость с учетом скидки в у.е.: =E2*(1-F2) Вычитает скидку (в процентах) из общей стоимости.
• Курс доллара: Значение в ячейке J2.
• Стоимость в рублях: =G2*Курс Умножает стоимость с учетом скидки в у.е. на курс доллара (из ячейки J2).
• Сумма для всех фирм: =СУММ(I2:I15) Суммирует столбец "Стоимость в рублях".
• Сумма для Российских: =СУММЕСЛИ(B2:B15;"Российская";I2:I15) Суммирует столбец "Стоимость в рублях" только для тех фирм, у которых в столбце "Тип фирмы" указано "Российская".
• Сумма для Совместных: =СУММЕСЛИ(B2:B15;"Совместная";I2:I15) Суммирует столбец "Стоимость в рублях" только для тех фирм, у которых в столбце "Тип фирмы" указано "Совместная".
Реализация справочника (как правило это не нужно в таком простом случае, но для полноты):
1. Создайте отдельный лист (например, "Справочник").
2. В столбце A перечислите виды оборудования (1, 2, 3).
3. В столбце B укажите стоимость единицы для каждого вида оборудования (1000, 500, 250).
4. В основной таблице, в столбце "Стоимость в у.е.", используйте формулу ВПР(C2;Справочник!A:B;2;ЛОЖЬ), где C2 - это вид оборудования для данной фирмы.
Построение круговой диаграммы:
1. Выделите ячейки с суммарной стоимостью для российских и совместных фирм.
2. Перейдите на вкладку "Вставка" и выберите "Круговая диаграмма".
3. Настройте диаграмму (добавьте подписи данных, названия осей и т.д.).
Дополнительные улучшения:
• Использование выпадающих списков: Для столбцов "Тип фирмы" и "Вид оборудования" можно использовать выпадающие списки, чтобы упростить ввод данных и избежать ошибок. Для этого выделите столбец, перейдите на вкладку "Данные" и выберите "Проверка данных".
• Условное форматирование: Можно использовать условное форматирование, чтобы выделить фирмы, получившие скидку.
• Защита ячеек: Защитите ячейки с формулами, чтобы случайно не изменить их.
Эта таблица позволит вам легко отслеживать стоимость закупленного оборудования, автоматически рассчитывать скидки и видеть суммарные значения как для всего объединения, так и для отдельных типов фирм