Ошибка #ЗНАЧ! в таблице заказов столовой
В школьной столовой ввели предварительный заказ через электронную таблицу. Ученик дежурного класса заполняет лист: A - дата, B - класс, C - блюдо, D - цена, E - количество. В ячейки D2:D6 цены введены как текст: «550 тг», «420 тг», «380 тг», «600 тг», «250 тг». В F2 стоит формула =D2*E2, и во всех строках появляется #ЗНАЧ!. Классы заказали: 9А - 12 порций по 550, 9Б - 8 по 420, 9В - 15 по 380, 9Г - 10 по 600, 9Д - 6 по 250. Дежурный хочет исправить таблицу к завтрашнему дню, посчитать выручку по классам и построить диаграмму для отчёта. Учитель информатики запрещает вручную переписывать цены в каждой строке: нужно предложить решение, которое не сломается при следующем заказе. Всего в заказе пять строк, но список классов может расшириться. Время ограничено - 40 минут.
1. Объясните причину ошибки #ЗНАЧ! и предложите два способа исправления. Какой из них лучше для повторяющегося отчёта?
Read the worked answer
Ошибка не в умножении, а в типе данных: «550 тг» - текст, поэтому Excel не может умножить его на число. Первый способ - быстрый: извлечь число формулой =VALUE(SUBSTITUTE(D2;" тг";""))E2 и протянуть её вниз. Он работает, но хрупок: лишний пробел, «тг.» или другое написание снова дадут ошибку. Второй способ - правильный: хранить в D только число, а единицу измерения вынести в заголовок «Цена, тг»; тогда F2 = D2E2. Для повторяющегося отчёта лучше второй вариант: он не зависит от текстовых хитростей и позволяет использовать SUMIF. Выручка при исправленных данных: 12×550 = 6600; 8×420 = 3360; 15×380 = 5700; 10×600 = 6000; 6×250 = 1500; итого 23 160 тенге.
2. Напишите формулу для выручки класса 9А и определите, какой класс заказал больше всех. Что изменится, если F ещё содержит #ЗНАЧ!?
Read the worked answer
После исправления типа данных формула: =SUMIF(B2:B6;"9А";F2:F6). Она суммирует только строки класса 9А. Сравнение: 9А - 6600, 9Б - 3360, 9В - 5700, 9Г - 6000, 9Д - 1500; больше всех у 9А. Если F содержит #ЗНАЧ!, SUMIF вернёт ошибку, потому что диапазон суммирования содержит ошибки. Сначала нужно исправить D/F, а уже потом строить отчёт. Альтернатива без вспомогательного столбца: =SUMPRODUCT((B2:B6="9А")D2:D6E2:E6), но она также требует числовых D. В формуле с SUMIF важно зафиксировать диапазоны, если её копируют, но не блокировать критерий.
3. Какую диаграмму выбрать для отчёта по классам и какие ошибки оформления нужно предотвратить?
Read the worked answer
Для сравнения классов по выручке лучше столбчатая диаграмма: категории - классы, значения - тенге. Она показывает различия между 9А, 9В и 9Г и не требует, чтобы сумма равнялась 100%. Круговая диаграмма уместна, только если нужно показать долю каждого класса в общей выручке; при пяти близких категориях её читать труднее. Перед построением нужно исключить итоговую строку, иначе она станет шестым столбцом и исказит масштаб. Подписи должны содержать единицы (тенге), а не только числа; нельзя обрезать ось так, чтобы маленькие значения выглядели почти равными большим. Заголовок должен указывать дату и источник данных.