Как сделать таблицу в «хл»: учёт материалов в Excel
Как сделать таблицу в хл для материалов: собрать шапку в Excel, выбрать числовые форматы, превратить диапазон в таблицу и проверить формулы, строки и итог.
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
В списке из четырёх материалов сразу видно, на что уходят 1500 рублей и как изменится сумма при другой закупке. Не нужно пересчитывать всё в заметках. Запрос «как сделать таблицу в хл» разберу на примере Microsoft Excel. Возьмём небольшой план материалов для мастер-класса по брелокам и доведём его от шапки до проверяемого итога.
Числа ниже условные, их можно заменить своими. Я показываю устройство списка, а не цены магазина. Работаем в настольном Excel для Windows: сначала вводим данные в клетки, затем преобразуем диапазон в таблицу с заголовками. Цветная обводка без этого преобразования тоже может выглядеть аккуратно, но управлять списком будет менее удобно.
Сначала у каждого материала появляются единица и цена
Я собрала пример из фетра, ленты, клея и колец для брелоков. Ошибка, которую легко принести из бумажного списка, выглядит так: «лента, восемь метров, примерно тридцать рублей». Для человека понятно. Для расчёта лучше разделить название, единицу измерения, количество и цену. Тогда изменится одно число, а стоимость строки можно пересчитать формулой.
В первой строке листа введи пять заголовков, начиная с A1: «Материал», «Ед.», «Количество», «Цена за ед., ₽», «Стоимость, ₽». Не объединяй эти ячейки в большой заголовок. Каждому столбцу нужна собственная подпись. Название мастер-класса можно оставить в имени листа или файла.
Материал · Ед. · Количество · Цена за ед., ₽ · Стоимость для проверки, ₽
Последний столбец здесь содержит контрольные результаты. В свой лист пока перенеси названия и исходные числа, а клетки E2:E5 оставь пустыми для формулы. Если перепечатать готовые стоимости, дальнейшая смена количества их не пересчитает. Таблица будет выглядеть законченной раньше, чем начнёт считать.
Одна строка означает одну позицию закупки. Я не вставляю пустые ряды между видами материалов и не пишу в середине «дальше фурнитура». Такая подпись мешает единому списку. Если группировка нужна постоянно, для неё стоит добавить отдельный столбец, но в нашем коротком примере достаточно названий.
Когда дойдёшь до общей суммы, пригодится отдельный разбор как в Excel сделать формулу суммы. Сейчас важнее, чтобы все четыре позиции имели одинаковую структуру. Я сначала проверяю эту основу, а потом выбираю способ подведения итога.
Как сделать таблицу в «хл» из диапазона с шапкой
Пока перед тобой обычный прямоугольник заполненных ячеек. Чтобы Excel воспринимал его как таблицу, задай диапазон и заголовки явно. Для нашего расположения это A1:E5: шапка плюс четыре материала. Пустой пока столбец стоимости тоже должен войти в выделение.
Выдели A1:E5, включая все пять заголовков.
На «Главной» открой «Форматировать как таблицу» и выбери спокойный стиль.
В появившемся окне сверь границы диапазона.
Сохрани A1:E1 как шапку, установив в окне создания признак таблицы с заголовками, затем подтверди выбор.
Проверь, что названия остались шапкой, а первая строка с фетром считается данными.
Если вместо названий появились стандартные подписи вроде «Столбец1», посмотри, правильно ли был отмечен признак заголовков. Не продолжай вводить формулы, пока шапка не соответствует содержимому. Мне легче остановиться здесь, чем затем объяснять человеку, что в «Столбце4» находится цена.
В шапке таблицы доступны фильтры. Они позволят временно оставить, например, только фетр, сохраняя строку целиком: вместе с количеством и ценой. Для одного вида материала это удобнее, чем искать название в отдельном столбце и случайно смотреть стоимость соседней позиции. Но фильтр не удаляет остальные записи.
Я попробовала бы стиль с чередованием светлых строк, без тяжёлых рамок вокруг каждой цифры. У списка одна задача: дать сопоставить материал с его расходом. Для начала этого достаточно. Ширину столбцов подстрой так, чтобы заголовки и названия читались; украшать таблицу раньше проверки данных не требуется.
Формат Excel-таблицы не проверяет, сколько ленты лежит в шкафу. Пока это план закупки для одного занятия. Остатки, поступления и фактический расход требуют других полей и правил заполнения. Я не смешиваю их с первоначальным списком, иначе одно «Количество» начинает обозначать сразу несколько вещей.
Стоимость партии получается из чисел, а не подписей
В C2 должно стоять число 12, а не запись «12 листов». Единица уже находится в соседнем столбце. В D2 вводи 45 без дописанного слова «рублей». Денежное обозначение задаётся форматом. Так данные сохраняются числами, с которыми можно выполнять расчёт.
Задай диапазону C2:C5 числовое отображение: его параметры открываются сочетанием Ctrl+1 после выделения этих ячеек. Для нашего примера подходит числовой вариант без дробной части. Для цены и стоимости выбери денежное отображение с нужным количеством знаков. Если закупка бывает дробной, например лента по полметра, число десятичных знаков для количества следует увеличить. Нельзя прятать нужную точность ради ровного вида.
Формат меняет отображение, а не исходное значение. Например, округлённая на экране дробь может продолжать участвовать в вычислении полностью. Я проверяю значение в строке формул, когда сумма неожиданно расходится с расчётом по видимым цифрам. Сначала данные. Только затем оформление.
Возможности текстового разбора и работы с задачами собраны на странице тарифов LibraChat. Помощник может пригодиться, чтобы обсудить структуру списка и вопросы к нему. Проверка исходных количеств и смысла единиц остаётся у того, кто ведёт учёт.
Теперь в первой строке стоимости, E2, введи `=C2*D2`. Знак равенства обозначает формулу, звёздочка умножает количество на цену. В таблице Excel такой столбец может стать вычисляемым: формула распространится на другие строки. Проверь каждую стоимость, а не предполагай заполнение по одному результату.
При вводе кликами по ячейкам Excel может показать ссылки с именами столбцов вместо C2 и D2. Это структурированные ссылки таблицы. Их вид отличается, но смысл здесь тот же: количество текущей строки умножается на её цену. Не заменяй их цифрами только потому, что запись выглядит непривычно.
Если формула не заполнила остальной столбец, перенеси её вниз и проверь относительные ссылки в каждой строке. Для первой позиции ожидается 12 × 45 = 540, для второй 8 × 30 = 240. Я сделала бы такую ручную сверку хотя бы на двух разных материалах. Одинаковый результат во всех рядах иногда означает, что везде используются исходные клетки первой строки.
Две новые строки проверяют готовый учёт
Под четырьмя материалами добавь итоговую строку средствами Excel-таблицы: щёлкни внутри неё и отметь «Строка итогов» на конструкторе. В её клетке под стоимостью выбери суммирование. Для исходного списка контрольная сумма равна 1500: 540 + 240 + 480 + 240. Количества суммировать вместе не надо. Листы фетра, метры ленты и флаконы клея не превращаются в одну осмысленную единицу.
Функции строки итогов могут учитывать фильтрацию. Если ты оставишь только фетр, сумма видимых строк будет другой. Поэтому перед проверкой всей закупки сбрось фильтры и убедись, что видны все четыре материала. Выбранная функция важнее самого наличия строки со словом «Итого».
Теперь добавь в таблицу две позиции: 10 бумажных пакетов по 8 рублей и 5 метров шнура по 20 рублей. Их стоимости составляют 80 и 100 рублей, новый контрольный итог — 1680. Вставляй именно строки таблицы перед итогом и проверь, что они получили формулы. Запись за пределами табличной области может остаться отдельным текстом на листе.
Новые позиции находятся внутри таблицы и доступны её фильтрам.
Стоимость пакетов равна 80, стоимость шнура равна 100.
Общий итог без фильтра составляет 1680 по условиям примера.
При изменении количества фетра стоимость его строки меняется.
В формулах используются данные соответствующего материала.
Единицы измерения отделены от чисел, а названия столбцов понятны другому человеку.
Если хочешь проверить структуру перед переносом своего списка, открой чат LibraChat и задай конкретную задачу: «Проверь схему таблицы для плановой закупки: материал, единица, количество, цена за единицу, стоимость. Какие ошибки в единицах и итогах стоит искать? Дай контрольный список, не придумывай мои остатки и цены». Попробуй сначала на условных строках из примера.
Я считаю такой список пригодным к работе после изменения данных. Добавь одну позицию, поменяй количество, посмотри на итог и верни пробные изменения, если они не относятся к закупке. Когда таблица переживает эти действия, её уже можно вести дальше — с понятной шапкой и числами, которым ты знаешь проверку.
