Как научиться пользоваться экселем: день в мастерской
Как научиться пользоваться экселем на своих запасах: от первой записи до списка покупок. Формулы, фильтры и одна сумма, из-за которой легко купить лишнее.
Денис
Продакт LibraChat
8 мин чтения
В моей таблице вышло -6, а докупить всё равно требовалось 16 деталей. Я собирал стеллаж и по виду полной коробки решил, что крепежа хватит. Лист заставил пересчитать именно нужные позиции. На этой домашней задаче я разобрался, как научиться пользоваться экселем: от первой записи до списка покупок, который могу обновить сам.
До этого я обращался с Excel как с тетрадью в клетку. Записывал числа, раскрашивал заголовки, итог считал отдельно. Выглядело прилично. Пользоваться было неудобно: исправишь одну цифру, а потом ищешь, где ещё она участвовала. В этот раз я решил довести один список от пересчёта ящика до похода в магазин.
09:10. Как научиться пользоваться экселем на одном ящике
Я высыпал крепёж на стол и приготовил пустую книгу. Книга — весь файл, лист — отдельная вкладка внутри него. Мне хватило одной вкладки, которую я назвал «Стеллаж». Сам файл сохранил как «Мастерская.xlsx» в папку, где потом смогу его найти. Уже полезно: список перестал жить в безымянном окне.
Сверху на листе стоят буквы столбцов, слева — номера строк. Пересечение B и 2 называется ячейкой B2. Когда выбираешь её мышкой, адрес видно слева от строки формул. Я несколько раз переходил между B2 и C2, пока перестал путаться, какое число где лежит.
Для первого листа я оставил четыре колонки. В A записал деталь, в B количество в ящике, в C потребность для стеллажа, а в D разницу между потребностью и запасом. Заголовки заняли первую строку, записи начались со второй. Вот что получилось после пересчёта:
Деталь · В ящике, шт. · На стеллаж, шт. · Разница, шт.
Последнюю колонку пока оставь пустой. Её значения получим вычислением. В B2 введи 24, в C2 — 36; следующая деталь займёт строку 3. Название пиши целиком в одну ячейку, без пустых строк между записями.
Мне пришлось отказаться от привычки добавлять к числам пояснения. Рука тянулась написать «24 штуки», но единица уже есть в заголовке. В ячейке остаётся 24. Так дальше можно считать, а не извлекать число из фразы.
Если текст обрезается, потяни границу между буквами столбцов, чтобы расширить колонку. Для длинного названия это удобнее, чем придумывать сокращение, которое завтра сам же не узнаешь. Красоту пока отложим. Сначала записи.
10:00. Одна разность освободила меня от калькулятора
Я выбрал D2 и ввёл `=C2-B2`. После Enter появилось 12. Знак равенства начинает формулу, минус вычитает запас из потребности. Excel берёт значения по адресам, а не хранит отдельное число, которое нужно каждый раз поправлять вручную.
Здесь легко выбрать обратный порядок: `=B2-C2` тоже даст результат, только смысл знака изменится. Поэтому я написал себе правило чтения: положительное число означает нехватку, отрицательное — запас сверх текущей задачи. Сначала смысл. Потом вычисление.
Я проверил формулу, временно заменив 24 винта на 30. В D2 осталось 6. Вернул исходное количество, снова увидел 12. Эта короткая перестановка убедила меня больше, чем сам факт появления числа: формула действительно зависела от нужной ячейки.
Теперь выдели D2. В углу рамки есть маленький квадрат; протяни его вниз до D5. При таком копировании относительные ссылки меняются: строка с гайками получит `=C3-B3`, строка с крючками — `=C4-B4`. Набивать четыре похожих выражения вручную уже не требуется.
Я всё равно посмотрел формулы в последней строке. Там должны участвовать C5 и B5. Если после копирования везде получилось 12, проверь, не скопировал ли ты готовое число вместо формулы. Выбери ячейку и прочитай строку формул над листом.
С гайками появился минус. Меня это сначала смутило: будто программа ругалась на мои запасы. Но 12 нужных гаек минус 18 имеющихся дают -6. Шесть останется. Ошибки тут нет, нужно лишь договориться о значении результата.
12:30. На экране остались только покупки
Пока я ходил за чаем, список уже успел обрасти заливкой. Остановился на нейтральной. Главное улучшение оказалось в другом: я превратил диапазон в таблицу Excel, чтобы получить фильтры в заголовках.
Для настольного Excel в Windows последовательность такая:
Выдели A1:D5 вместе с названиями колонок.
На вкладке «Главная» выбери «Форматировать как таблицу».
Выбери оформление и проверь указанный диапазон.
Отметь, что таблица содержит заголовки, и подтверди создание.
В некоторых версиях расположение элементов немного отличается. Ориентируйся на названия команд. Цвет полос не меняет арифметику, зато стрелки у заголовков позволяют отбирать записи по условию.
В колонке «Разница» мне нужны числа больше нуля. Открой её стрелку, выбери «Числовые фильтры», затем условие «Больше» и введи 0. В списке останутся винты и крючки. Наконец-то он отвечает на вопрос магазина: что покупать?
Гайки с шайбами исчезли только с экрана. Из таблицы они никуда не делись. Чтобы вернуть их, очисти фильтр этой колонки. Мне понадобилось сделать это пару раз, прежде чем я перестал воспринимать скрытую строку как потерянную запись.
Сортировка решает другую задачу: меняет порядок. Если хочется видеть самые большие нехватки сверху, используй сортировку по убыванию в той же колонке. Для таблицы Excel перемещается вся запись. Название детали должно остаться связано со своими количествами; проверь эту связь после первого опыта.
15:00. Внизу вышло -6, а купить надо было шестнадцать
И тут я чуть не испортил хороший список одной лишней уверенностью. Снял фильтр, сложил четыре разницы и получил -6. Всего крепежа в ящике было больше, чем требовалось. Можно идти собирать стеллаж?
Нельзя. Шестнадцать лишних шайб не заменят четыре недостающих крючка. Запас одной детали скомпенсировал нехватку другой только в арифметике. В мастерской такой обмен не работает.
Я специально оставил этот расчёт отдельно от таблицы: в D7 набрал `=D2+D3+D4+D5`. Он верно считает 12 минус 6 плюс 4 минус 16. Просто задаёт неподходящий вопрос. Для закупки я вернулся к положительным строкам: 12 винтов и 4 крючка.
Общую сумму стоит делать там, где складываемые значения действительно можно объединить для нужного решения. Например, стоимость покупок в рублях. Как устроена сама операция, разобрано в статье о формуле суммы в Excel. А прежде чем суммировать остатки, спроси себя, сможешь ли заменить одну позицию другой.
Я убрал D7 из рабочего вида, чтобы она не выглядела командой «ничего не покупать». Стало спокойнее. Оказалось, пользоваться таблицей — ещё и уметь отказаться от бесполезного итога.
18:00. Когда застреваю, спрашиваю про свою ячейку
К вечеру я перестал искать уроки сразу обо всём Excel. Теперь мог назвать затруднение: «Почему после копирования формулы меняются адреса?» или «Почему фильтр показывает только две строки?» С таким вопросом гораздо легче получить объяснение и проверить его на собственном листе.
С LibraChat можно обсудить, почему выбранные ячейки участвуют в вычитании. Здесь я использую чат как помощника в освоении конкретного действия. Кнопки в Excel нажимаю сам: так вижу, какое изменение вызвало результат, и могу повторить его завтра.
Для своего первого листа открой LibraChat и вставь в чат вопрос с адресами. Например, такой:
Помоги понять мою таблицу Excel. В B2 запас винтов — 24, в C2 потребность — 36, в D2 формула =C2-B2. Я копирую её в D3, где записаны гайки: B3 равно 18, C3 равно 12. Объясни изменение адресов и смысл отрицательной разницы. Затем дай мне одну похожую задачу без ответа, чтобы я проверил понимание сам.
Мне нравится последняя часть просьбы. Прочитать объяснение и решить новое маленькое задание — разные усилия. Если можешь предсказать результат ещё до Enter, значит связь между адресами и числами уже становится понятной.
Не нужен весь рабочий архив, чтобы задать такой вопрос. Достаточно маленького фрагмента с расположением данных; личные сведения и чужие документы сюда не относятся. Если планируешь разбирать большие файлы, доступные возможности и условия смотри в тарифах LibraChat. Для первого занятия я бы оставил ровно одну трудность.
Завтра: как научиться пользоваться экселем без моего списка
Утром я снова открыл «Мастерская.xlsx» и сначала снял фильтр. Проверил, что вижу все четыре детали, а затем пересчитал принесённый крепёж. Винтов стало 36, крючков — 6. Разница у обеих позиций обнулилась. Вот это уже пригодилось для сборки.
На своём листе попробуй продолжить задачу:
Измени потребность в винтах с 36 на 42 и заранее назови новую нехватку.
Найди ячейку, которая вычисляет разницу для крючков.
Покажи только позиции для покупки, затем верни полный список.
Сохрани книгу и найди её в своей папке после закрытия Excel.
Получилось? Добавь ещё одну деталь. Убедись, что новая запись вошла в таблицу, а в колонке разницы стоит формула именно для её строки. После изменения чисел при необходимости примени фильтр заново, чтобы на экране остался актуальный список закупки.
Я не выучил за этот день все функции. Зато теперь полная коробка не убеждает меня, что крепежа достаточно. Я могу открыть лист и назвать, каких именно деталей не хватает. Для первого дня в Excel это результат, с которым хочется продолжать.
