БухСофт 2020     8 (800) 222-18-27 Задать вопрос по программам Помощь
Бухсофт

Как вести учет материалов в таблице Эксель

22 ноября 2019
180
Средний балл: 0 из 5

Учет материалов в Excel ведут фирмы с небольшой номенклатурой. Как правило, это индивидуальные предприниматели и предприятия малого бизнеса. В нашей статье читайте инструкцию, как самому сделать таблицу для учета материалов в Эксель.

Гость, получите бесплатный доступ к программе БухСофт
Полный доступ на месяц! - Формируйте документы, тестируйте отчеты, пользуйтесь уникальным сервисом экспертной поддержки "Системы Главбух".

Позвоните нам по телефону 8 800 222-18-27 (бесплатно).

Организации на упрощенке 6% с небольшим номенклатурным ассортиментом для учета материалов могут использовать электронную таблицу Эксель. Вы можете самостоятельно сделать таблицу, в которую нужно вводить приходы и выбытия, и сразу видеть обороты и остатки по каждой позиции. Мы разработали простой шаблон для учета материалов, вы можете использовать его для своих задач. Структура нашей таблицы такая:

  1. Номенклатура. Здесь добавляют новые материалы и смотрят по ним обороты и остатки;
  2. Приходы. Здесь делаю поступления;
  3. Выбытие. Здесь отражают списание материалов;

В нашей статье читайте подробную инструкцию, как вести учет материалов в таблице Эксель.



Быстрый перенос бухгалтерии в БухСофт

Создайте номенклатуру

На первом листе таблицы создайте справочник материалов. В нем укажите наименования (1). При необходимости можете добавить поля с дополнительными характеристиками, например, цвет, размер и т.д.

Важно!!! Номенклатура в этом листе не должна дублироваться.

Справа создайте четыре поля (2):

  • Остаток на начало;
  • Приход;
  • Расход;
  • Остаток на конец.

В этих полях напишем формулы, которые будут брать информацию из приходов и выбытий. Но сначала создадим приходы и выбытия.

Создайте справочник материалов в таблице Эксель

Гость, для Вас открыт бесплатный доступ к чату с бухгалтером-экспертом
Закажите обратный звонок на подключение или позвоните:
8 (800) 222-18-27 (бесплатно по РФ).

Создайте приходы

На этом листе сделайте таблицу приходов. В нашем шаблоне есть поля:

  • Номенклатура;
  • Кол-во;
  • Цена;
  • Сумма;
  • Поставщик;
  • Номер документа;
  • Дата документа.

Таблица приходов в Эксель

Важно!!! Подбирать материалы для приходов нужно из таблицы с номенклатурой. Делают это с помощью специальной функции – «Проверка данных».

Сначала выделите мышкой ячейки в поле «Номенклатура» (1). Желательно выделить как можно большее количество ячеек вниз, можно даже до конца – сделайте задел на несколько лет вперед.  Затем в разделе «Данные» (2) нажмите на кнопку «Проверка данных» (3). В открывшемся меню выберете тип данных «Список» (4).

Проверка данных в таблице Эксель

Далее кликните на кнопку «Источник» (5), перейдите в лист с номенклатурой и выделите мышкой поле с материалами (6), с как можно большим заделом вниз. Для завершения нажмите кнопку «ОК» (7). Теперь в приходах можно выбирать номенклатуру из списка.

Кнопка «Источник» в таблице Эксель

Поле с материалами в таблице Эксель

После настройки функции «Проверка данных», выбирать материалы для прихода нужно из списка. Встаньте в ячейку с номенклатурой, нажмите на стрелочку справа (8) и выберете нужную позицию.

Функция «Проверка данных» в таблице Эксель

После выбора материала, укажите:

  • Количество и цену (9);
  • Поставщика, дату и номер документа (10).

Укажите количество и цену в таблице Эксель

В поле «Сумма» впишите формулу умножения «=RC[-2]*RC[-1]» (11) и скопируйте ее вниз до конца таблицы (12). Выделите разным цветом накладные поступления, так удобнее искать нужный документ.

Впишите формулу умножения в таблице Эксель

Создайте расходы

На этом листе сделайте таблицу расходов. В нашем шаблоне есть поля:

  • Номенклатура;
  • Кол-во;
  • Куда переданы материалы;
  • Номер накладной;
  • Дата.

Важно!!! Подбирать материалы для выбытия нужно из таблицы с номенклатурой. Делают это с помощью специальной функции – «Проверка данных». Чтобы упростить создание списка, просто скопируйте ячейки с номенклатурой из поступлений и вставьте в ячейки с номенклатурой (1) в расходах.

Материалы для выбытия в таблице Эксель

В нашем примере мы заполнили таблицу расходов тремя накладными на выбытие материалов (2). Мы разделили списания разными цветами, чтобы было просто искать нужный документ.

Заполнили таблицу расходов в таблице Эксель

Сделайте расчет оборотов и остатков материалов

Добавьте в таблицу с номенклатурой нужные формулы. Но сначала, для упрощения формул, выделим определенные области в приходах и выбытиях и присвоим им имена.

Шаг 1

Выделите в поступлениях ячейки с количеством (1), встаньте мышкой в поле слева вверху (2), напишите название для этого диапазона, например, «ПОСТУПЛЕНИЯ_КОЛВО» и нажмите «Ввод». Мы будем использовать это название в формуле суммирования поступлений. Чем больший диапазон вниз выделите – тем лучше. Чтобы выделить диапазон до конца таблицы, встаньте в верхнюю ячейку и нажмите одновременно Ctrl + Shift + Стрелка вниз.

В поступлениях ячейки с количеством в таблице Эксель

Далее выделите в поступлениях ячейки с материалами (3), встаньте мышкой в поле слева вверху (4), напишите название для этого диапазона, например, «ПОСТУПЛЕНИЯ_НОМЕНКЛАТУРА» и нажмите «Ввод». Мы будем использовать это название в формуле суммирования поступлений.

В поступлениях ячейки с материалами в таблице Эксель

Шаг 2

Выделите в выбытиях ячейки с количеством (1), встаньте мышкой в поле слева вверху (2), напишите название для этого диапазона, например, «ВЫБЫТИЕ_КОЛВО» и нажмите «Ввод».

В выбытиях ячейки с количеством в таблице Эксель

Далее выделите в выбытиях ячейки с материалами (3), встаньте мышкой в поле слева вверху (4), напишите название для этого диапазона, например, «ВЫБЫТИЕ_НОМЕНКЛАТУРА» и нажмите «Ввод».

В выбытиях ячейки с материалами в таблице Эксель

Шаг 3

В таблице с номенклатурой в ячейке «Приход» (1) напишите формулу =СУММЕСЛИМН(ПОСТУПЛЕНИЯ_КОЛВО;ПОСТУПЛЕНИЯ_НОМЕНКЛАТУРА;RC[-2]).

В этой формуле есть ссылки на диапазоны, которые мы делали в предыдущем шаге.

Эта формула посчитает количество поступившего материала, который указан в поле слева (2). Далее скопируйте эту формулу вниз (3). Теперь в таблице появились данные по количеству поступлений по всей номенклатуре.

СУММЕСЛИМН в таблице Эксель

Далее в таблице с номенклатурой в ячейке «Расходы» (4) напишите формулу =СУММЕСЛИМН(ВЫБЫТИЕ_КОЛВО;ВЫБЫТИЕ_НОМЕНКЛАТУРА;RC[-3]).

В этой формуле ссылки на диапазоны, которые мы делали в предыдущем шаге.

Эта формула посчитает количество отпущенного со склада материала, который указан в поле слева (5). Далее скопируйте эту формулу вниз (6). Теперь в таблице появились данные по количеству выбытий по всей номенклатуре.

СУММЕСЛИМН в таблице Эксель в таблице Эксель 

Шаг 4

В ячейках «Остаток на конец» посчитайте по формуле складского учета остатки на конец периода.

Формула складского учета в таблице Эксель

В верхней ячейке (1) напишите формулу =RC[-3]+RC[-2]-RC[-1] и скопируйте ее вниз (2).

Напишите формулу в таблице Эксель

Шаг 5

Введите вручную остатки на начало периода (1). Теперь в таблице видна полная картина по движению материалов.

Добавьте внизу итоговые суммы с помощью формулы СУММ (2). Оборотная ведомость по материалам готова.

Введите остатки на начало в таблице Эксель

У таблицы Эксель, которую мы сделали в этом примере, есть существенные недостатки перед профессиональными системами складского учета.

На начальном этапе бизнеса можно использовать таблицу Эксель для учета материалов, но для дальнейшего развития возможностей таблицы не хватит. Используйте программу БухСофт для учета ТМЦ с неограниченным количеством номенклатуры.



logo
×
Для Вас скидка на программы «БухСофт»
Гость!
Для Вас есть специальное предложение по подключению
к программам и сервисам «БухСофт»
Выберите программу или сервис, Вам позвонит
персональный менеджер и предложит специальные условия.
Чтобы скачать файл, зарегистрируйтесь!

Этот документ спасет от обидных штрафов и защитит от ошибок. Актуальность подтверждена экспертами программы БухСофт. Зарегистрируйтесь, скачайте и сразу используйте в работе!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
Войти через социальную сеть
Зарегистрироваться
×
Чтобы скачать файл, зарегистрируйтесь!

Этот документ спасет от обидных штрафов и защитит от ошибок. Актуальность подтверждена экспертами программы БухСофт. Зарегистрируйтесь, скачайте и сразу используйте в работе!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
Войти через социальную сеть
Зарегистрироваться
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.
Заказать звонок