Складской учет в Excel подходит для любой торговой или производственной организации, где важно учитывать количество сырья и материалов, готовой продукции. С этой целью предприятие ведет складской учет. Крупные фирмы, как правило, закупают готовые решения для ведения учета в электронном виде. Вариантов сегодня предлагается масса, для различных направлений деятельности.
На малых предприятиях движение товаров контролируют своими силами. С этой целью можно использовать таблицы Excel. Функционала данного инструмента вполне достаточно. Ознакомимся с некоторыми возможностями и самостоятельно составим свою программу складского учета в Excel.
В конце статьи можно скачать программу бесплатно, которая здесь разобрана и описана.
Как вести складской учет в Excel?
Любое специализированное решение для складского учета, созданное самостоятельно или приобретенное, будет хорошо работать только при соблюдении основных правил. Если пренебречь этими принципами вначале, то впоследствии работа усложнится.
- Заполнять справочники максимально точно и подробно. Если это номенклатура товаров, то необходимо вносить не только названия и количество. Для корректного учета понадобятся коды, артикулы, сроки годности (для отдельных производств и предприятий торговли) и т.п.
- Начальные остатки вводятся в количественном и денежном выражении. Имеет смысл перед заполнением соответствующих таблиц провести инвентаризацию.
- Соблюдать хронологию в регистрации операций. Вносить данные о поступлении продукции на склад следует раньше, чем об отгрузке товара покупателю.
- Не брезговать дополнительной информацией. Для составления маршрутного листа водителю нужна дата отгрузки и имя заказчика. Для бухгалтерии – способ оплаты. В каждой организации – свои особенности. Ряд данных, внесенных в программу складского учета в Excel, пригодится для статистических отчетов, начисления заработной платы специалистам и т.п.
Однозначно ответить на вопрос, как вести складской учет в Excel, невозможно. Необходимо учесть специфику конкретного предприятия, склада, товаров. Но можно вывести общие рекомендации:
- Для корректного ведения складского учета в Excel нужно составить справочники. Они могут занять 1-3 листа. Это справочник «Поставщики», «Покупатели», «Точки учета товаров». В небольшой организации, где не так много контрагентов, справочники не нужны. Не нужно и составлять перечень точек учета товаров, если на предприятии только один склад и/или один магазин.
- При относительно постоянном перечне продукции имеет смысл сделать номенклатуру товаров в виде базы данных. Впоследствии приход, расход и отчеты заполнять со ссылками на номенклатуру. Лист «Номенклатура» может содержать наименование товара, товарные группы, коды продукции, единицы измерения и т.п.
- Поступление товаров на склад учитывается на листе «Приход». Выбытие – «Расход». Текущее состояние – «Остатки» («Резерв»).
- Итоги, отчет формируется с помощью инструмента «Сводная таблица».
Чтобы заголовки каждой таблицы складского учета не убегали, имеет смысл их закрепить. Делается это на вкладке «Вид» с помощью кнопки «Закрепить области».
Теперь независимо от количества записей пользователь будет видеть заголовки столбцов.
Таблица Excel «Складской учет»
Рассмотрим на примере, как должна работать программа складского учета в Excel.
Делаем «Справочники».
Для данных о поставщиках:
* Форма может быть и другой.
Для данных о покупателях:
* Обратите внимание: строка заголовков закреплена. Поэтому можно вносить сколько угодно данных. Названия столбцов будут видны.
Для аудита пунктов отпуска товаров:
Еще раз повторимся: имеет смысл создавать такие справочники, если предприятие крупное или среднее.
Можно сделать на отдельном листе номенклатуру товаров:
В данном примере в таблице для складского учета будем использовать выпадающие списки. Поэтому нужны Справочники и Номенклатура: на них сделаем ссылки.
Диапазону таблицы «Номенклатура» присвоим имя: «Таблица1». Для этого выделяем диапазон таблицы и в поле имя (напротив строки формул) вводим соответствующие значение. Также нужно присвоить имя: «Таблица2» диапазону таблицы «Поставщики». Это позволит удобно ссылаться на их значения.
Для фиксации приходных и расходных операций заполняем два отдельных листа.
Делаем шапку для «Прихода»:
Следующий этап – автоматизация заполнения таблицы! Нужно сделать так, чтобы пользователь выбирал из готового списка наименование товара, поставщика, точку учета. Код поставщика и единица измерения должны отображаться автоматически. Дата, номер накладной, количество и цена вносятся вручную. Программа Excel считает стоимость.
Приступим к решению задачи. Сначала все справочники отформатируем как таблицы. Это нужно для того, чтобы впоследствии можно было что-то добавлять, менять.
Создаем выпадающий список для столбца «Наименование». Выделяем столбец (без шапки). Переходим на вкладку «Данные» — инструмент «Проверка данных».
В поле «Тип данных» выбираем «Список». Сразу появляется дополнительное поле «Источник». Чтобы значения для выпадающего списка брались с другого листа, используем функцию: =ДВССЫЛ(«номенклатура!$A$4:$A$8»).
Теперь при заполнении первого столбца таблицы можно выбирать название товара из списка.
Автоматически в столбце «Ед. изм.» должно появляться соответствующее значение. Сделаем с помощью функции ВПР и ЕНД (она будет подавлять ошибку в результате работы функции ВПР при ссылке на пустую ячейку первого столбца). Формула: .
По такому же принципу делаем выпадающий список и автозаполнение для столбцов «Поставщик» и «Код».
Также формируем выпадающий список для «Точки учета» — куда отправили поступивший товар. Для заполнения графы «Стоимость» применяем формулу умножения (= цена * количество).
Формируем таблицу «Расход товаров».
Выпадающие списки применены в столбцах «Наименование», «Точка учета отгрузки, поставки», «Покупатель». Единицы измерения и стоимость заполняются автоматически с помощью формул.
Делаем «Оборотную ведомость» («Итоги»).
На начало периода выставляем нули, т.к. складской учет только начинает вестись. Если ранее велся, то в этой графе будут остатки. Наименования и единицы измерения берутся из номенклатуры товаров.
Столбцы «Поступление» и «Отгрузки» заполняется с помощью функции СУММЕСЛИМН. Остатки считаем посредством математических операторов.
Скачать программу складского учета (готовый пример составленный по выше описанной схеме).
Вот и готова самостоятельно составленная программа.
Привет! Мы «Нескучные финансы», помогаем бизнесу навести порядок в учете и управлять бизнесом на основе цифр. Для этого мы или берем финучет на аутсорс, или предоставляем удобные бесплатные шаблоны. В этой статье как раз второе.
Мы подготовили барсетку. Это 16 шаблонов финансовых таблиц, в которых вы можете вести управленческий учет в своем бизнесе. Для каждой таблицы мы записали видеоинструкцию, ссылка на которую лежит на последней вкладке таблицы.
Внутри: отчет о движении денег, баланс, отчет о прибылях и убытках, учет основных средств, управление запасами, учет логистики, учет финансовой деятельности, учет сделок, финмодель, платежный календарь, зарплатная ведомость, отчет по маркетингу, калькулятор рентабельности, калькулятор финансового рычага, калькулятор скидок, отчет отдела продаж.
Чтобы воспользоваться таблицей, нужно сделать ее копию на свой Гугл-диск. Можно скачать в Экселе, но не рекомендуем: какие-то формулы могут не работать.
Можете просто перейти по ссылке и пользоваться таблицами на свое здоровье. А можете читать дальше: мы вкратце расскажем, зачем нужна каждая таблица из барсетки.
ДДС (отчет о движении денежных средств)
Это документ, с которого начинается внедрение финучета в компании. Из этого отчета видно, откуда пришли деньги и куда ушли. Так или иначе его ведут многие собственники ― в блокноте, Эксельке или голове, а вот анализируют не все. Однако нужно сравнивать суммы за разные периоды и по одинаковым статьям. Например, в этом месяце мы тратим на содержание офиса больше, чем в том.
Баланс
Самый важный отчет для собственника. Он показывает, сколько денег владельца компании работает в бизнесе. Собственный капитал увеличивается за счет получаемых прибылей, и уменьшается за счет полученных убытков, выплаты дивидендов.
Баланс состоит из активов и пассивов. В активах показывается, что есть у компании, а в пассивах ― кому это принадлежит.
Отчет о прибылях и убытках
Он помогает увидеть, сколько чистой прибыли заработала компания. Кроме того, отчет показывает детализацию по нескольким видам прибыли: маржинальной, валовой, операционной. Можно проанализировать и понять, как улучшить результат.
Учет основных средств
Нужен для того, чтобы систематизировать все данные, которые нужны для расчета амортизации и последующей продажи. В этом документе указывается стоимость и дата покупки, срок полезного использования и другие детали. Здесь может быть указано торговое, складское и производственное оборудование, оргтехника, недвижимость, мебель, транспорт ― все, что предназначено для основной деятельности организации и прослужит дольше года.
Управление запасами
Помогает навести порядок на складе и знать, сколько товара есть в наличии. Еще управлять запасами ― это держать ходовой товар в достаточном количестве. Чтобы определить спрос на товар, нужно анализировать фактические данные.ABC-анализ дает понять, какие товары занимали наибольшую долю в выручке компании, а XYZ-анализ помогает понять, как часто покупают конкретный товар. Можно анализировать не только конкретные продукты, но и категории товаров.
Чтобы не было товарного разрыва ― ситуации, когда клиенты приходят и хотят купить товар, а его нет ― нужно высчитать точку заказа. Так мы узнаем, сколько и когда нужно купить товара.
Учет логистики
С помощью этого инструмента можно анализировать работу с разными транспортными компаниями, учитывать расходы на доставку, если посылку не выкупили.
Учет финансовой деятельности
Позволяет собирать информацию по расчету с кредиторами и собственниками. В этом документе учитываются вклады и займы от владельца компании и информация о дивидендах. Также здесь систематизируются данные о сторонних кредитах, займах и лизинге оборудования.
Учет сделок
Позволяет не растерять информацию по проектам. В одном документе будут собраны сведения по контрагентам и проджект-менеджерам, сумма выплат и расходов, данные о расчетах и сроках. Еще в нем подсчитаны прибыль и рентабельность проекта.
Финансовая модель
В финмодели можно запланировать чистую прибыль и выручку, проанализировать ключевые показатели, которые влияют на финансовый результат бизнеса. Ее можно составить как для планируемых направлений бизнеса и компаний, так и для уже существующих. С помощью финмодели можно проверить, как то или иное управленческое решение повлияет на финансовый результат компании.
Платежный календарь
Один из инструментов планирования. Сначала планируют на месяц-два-три бюджет, а на его основе делают платежный календарь. В нем по датам видно, сколько и когда ожидается поступлений и затрат. Это помогает предвосхищать кассовые разрывы: в платежном календаре видны «дырки», которые можно залатать предоплатой от другого клиента, переносом сроков оплаты, заемными средствами и другими способами.
Зарплатная ведомость
Один из самых актуальных инструментов. Помогает вести учет выплат сотрудникам. Будет полезна компаниям, в которых сложная система мотивации, которая зависит от выполнения показателей эффективности. Помогает собрать информацию об отпускных, премиях, бонусах и отчислениях в пенсионный фонд, налоговую и соцстрах.
Маркетинговый отчет
Сводный документ, в котором учитываются рекламные активности на разных онлайн площадках и привлеченный с них трафик. С помощью такого инструмента можно собрать данные для анализа эффективности рекламы из разных источников: в отчете наглядно видно, что эффективней для продвижения вашего продукта ― контекстная реклама в Яндекс.Директе или таркетинговая во Вконтакте. Также здесь можно вести учет рекламных расходов и выручки, которая получена от клиентов из онлайна.
Калькулятор рентабельности проектов
Посчитает, выгодно заниматься проектом или нет. С его помощью можно определить маржинальность проекта и сравнить ее с плановой. Калькулятор рентабельности проектов даст оценить прибыль проекта с учетом разных систем налогообложения.
Калькулятор финансового рычага
Поможет рассчитать, нужен кредит или нет. Калькулятор финансового рычага сделает расчет эффективности кредита ― сможет компания взлететь на новые деньги или заемные средства потянут ее ко дну. Подробнее об эффекте финансового рычага мы рассказали в другой статье →
Калькулятор влияния скидки на прибыль
Рассчитает, как повлияет скидка на прибыль компании. Теперь вы сможете делать скидку не на глаз, а учитывая расчет по цене реализации, марже или наценке.
Отчет отдела продаж
Таблица, в которой удобно следить за работой всего отдела продаж и отдельных менеджеров — сразу увидите, если случится какое-то отклонение по выручке, сможете оперативно принять решение, как исправить ситуацию. А самим менеджерам отчет помогает понимать, сколько они заработают в этом месяце. Единственный отчет, к которому не записали видеоинструкцию.
Пользуйтесь на здоровье! Если есть вопросы по шаблонам, задавайте их в комментариях.
Время на прочтение
5 мин
Количество просмотров 172K
Я работаю обычным аналитиком и, так получилось, что летом 2014 года, участвуя в одном e-commerce проекте, на коленке за 3 недели сделал управленческий учет в MS Excel. Давно планировал и наконец-то решил выложить на Хабр. Думаю, будет полезно малым предпринимателям, понимающим важность управления финансовыми потоками, но не желающим тратить значительное количество времени и средств на ведение управленческого учета. Не претендую на истину в последней инстанции и буду рад иным решениям, предложенным участниками сообщества.
Бизнес, к которому я летом имел отношение, был обычным интернет-магазином одежды премиум и выше сегмента с оборотом около 1 млн рублей в месяц. Бизнес работал, не сказать, чтобы очень успешно, но работал и продолжает работать. Собственник понимал необходимость ведения управленческого учета и, с этим пониманием, взял меня в качестве финансового директора (аналитика/менеджера …), так как предыдущий ушел из бизнеса за 3 месяца до моего прихода. Собственно, дыра такой же продолжительности была и в ведении управленческого учета. Забегая вперед скажу, что дыру не устранил (решили не ворошить прошлое), но создал систему, которая успешно работает при минимальных трудозатратах и по сей день.
Мой предшественник вёл управленку в Финграде, который оказался весьма мощным инструментом. Например, он позволял автоматически грузить информацию из 1С и выписок разных банк-клиентов, создавая проводки по заранее сформулированным правилам. Вещь, безусловно полезная, однако, при соблюдении системы двойной записи увеличивала время работы в разы. Чтобы избежать увеличения работы этот инструмент позволял генерировать «зависимые проводки». В создании этих дополнительных проводок и была зарыта собака. И тут выяснилось, что за всей мощью Финграда крылась уникальность, обусловившая полное отсутствие экспертизы в свободном доступе. Обычным пользователям (платившим, кстати, 3000 рублей в месяц за доступ к системе) были доступны лишь «Руководство пользователя» на официальном сайте, да 6 видео-уроков там же. Youtube, дававший доступ к ещё паре десятков видеоуроков, также не сильно помогал. Форумов с информацией «how to…» не было в принципе. Поддержка, на конкретные вопросы о правилах создания «зависимых проводок» и просьбах помочь именно в моем случае — морозилась фразами «у нас с вами не заключен договор на поддержку, поэтому на такие специфические вопросы мы не готовы отвечать». Хотя казалось бы — чего специфического в таких просьбах, да ещё и со скриншотами с моей стороны? Понятно, что все можно бить руками, но спрашивается, а зачем тогда вообще платить за инструмент, который сильно увеличивает время, необходимое на ведение управленки и не дает никаких преимуществ для малого бизнеса?
Убедив собственника в нецелесообразности использования «Финграда» при таких объемах бизнеса и выгрузив всю информацию из системы, я поставил на нем БОЛЬШОЙ и жирный крест. При этом решение уйти именно в MS Excel было не спонтанным. Хорошенько загуглив на тему ведения управленческого учета находил монстров, похожих на «Финград», либо ссылки на веб-приложения для ведения личных финансов, в то время как основными требованиями к системе были:
— возможность ведения БДДС и БДР на основе изменяемого плана счетов;
— простота в дальнейшем ведении управленческого учета (в том числе силами «финансово-неграмотных» пользователей);
— гибкость (возможность на ходу расширять/убирать функционал);
— отсутствие перегруженности инструмента/интерфейса.
Для начала проясним термины: будучи не финансистом, под БДДС понимаю «Баланс Движения Денежных Средств», БДР — «Бюджет Доходов и Расходов». БДДС считаем кассовым методом (днем совершения операции — колонка «Дата операции») и используем для операционного day-to-day планирования, а БДР методом начисления (колонка «Период начисления») для стратегического, в рамках года и более.
Итак, как все устроено и как оно работает (в идеале):
1. Управленческий учет собирается на основе информации вводимой конечными пользователями при помощи формы в Google Docs. Красным помечены названия полей и кодировки вариантов в конечном файле управленческого учета — своего рода мапинг полей.
2. В итоге выглядит оно так (зеленым залито то, что перенесено в итоговый файл управленки).
3. Управленческий учет построен на базе .xls выгрузки из Финграда (отсюда странные для сторонних пользователей названия и, в целом избыточное количество колонок). Убедительная просьба не воспринимать всерьез значения колонок «Приход», «Расход» — многое рандомно изменено.
Механизм заполнения прост: аккуратно переносим во вкладку «Общая книга» из формы Google Docs и банковских выписок. Красным выделены строки, используемые для формирования БДР, зеленым — БДДС., которые представляют собой сводные таблицы и строятся на основе промежуточных вкладок с говорящими названиями. Единственные колонки, информация в которых не связана с иными источниками: «Исходный ID» (уникальные значения строк) и «Дата создания» (=ТДАТА(), а затем копируем и вставляем как значение)
4. Статьи ДДС (движения денежных средств) располагаются на отдельной вкладке «ПС_служебный» и вполне могут регулярно пересматриваться в зависимости от конкретных потребностей (не забываем обновлять формулы на листах «Данные_БДДС», «Данные_БДР»).
5. На картинке образец БДДС, в формате по умолчанию, свернутый до понедельной «актуальности».
6. Образец БДС (помесячный). Обратите внимание на уже упоминавшийся выше тезис об использовании строк из «Общей книги»: Бюджет и Факт для БДР, План и Факт — для БДДС.
7. Работа с БДДС подразумевает поддержание строк «План» в максимально актуальном состоянии. Я достаточно педантичен в работе с первичной информацией и комментарии сделанные мной сохраняли всю историю изменений. Как будет у Вас — вопрос к Вам. Мой подход позволил мне отлавливать примерно 1 существенную ошибку в неделю, грозившую расхождениями на десятки-сотни тысяч рублей. Время, кстати, съедалось немного.
8. Собственно сам файл управленческого учета.
PS: Долго думал над тем, как автоматизировать процесс «перелива» информации из формы Google Docs, пока не пришел к мысли о необходимом ручном контроле вводимой разнородной информации (много людей заполняет формы + наличие минимум одного банк-клиента + 1С). Тем более не знаю VBA… Отдаю на суд хабрасообщества как есть, надеюсь, кому-нибудь поможет или просто будет интересно.
UPD от 30.04.2020: ввиду текущей экономической ситуации решил вспомнить Excel и бесплатно помочь малому бизнесу созданием подобных инструментов. Все интеллектуальные права останутся за мной и будут опубликованы в виде статей (без данных, содержащих коммерческую тайну).
Критерии попадания в выборку кому хочу помочь:
Обязательный:
Вы не МММ, продавцы алкоголя возле школ, микрофинансовые организации
и прочие лохотронщики
Опциональные(достаточно соответствовать одному из):
Ваше дело имеет очевидную ценность для общества (благотворительные фонды, волонтерские движения и т.д.)
или
Вы платите белые зарплаты и приемлемый для оборота объем налогов (придется доказать выписками перечислений в фонды и на счета ФНС)
Нужно быстро сортировать и сопоставить данные из разных таблиц? Отыскать ошибку в уже заполненных ячейках? Сформировать сводную таблицу с итоговыми показателями? Мы собрали полезные инструменты, приемы и функции из программы Excel, которые можно выполнить за пару кликов.
Бухгалтерия в Excel становится намного проще, если использовать несколько полезных приемов, которые смогут ускорить работу с данными. Узнайте, чем может быть полезен Excel для бухгалтера в примерах, которые мы подготовили.
1.Автозаполнение формул в таблице Excel
Кроме непосредственных обязанностей бухгалтеру могут добавить функции по подготовке коммерческих предложений, расчета договорных цен и прочее. Для выполнения расчетов необходимо применять различные коэффициенты и поправки, а также конвертировать цены. И главное, выполнять все действия быстро и без ущерба обязанностям. Эксель для бухгалтера поможет в подготовке основного документа, который можно выполнять на рабочем листе, а дополнительные расчеты на отдельных. Так, пересчитывая курс, в одной ячейке можно указать цену, во второй курс валюты, а в третьей задать формулу пересчета (= первая ячейка * вторая ячейка), далее нажать Enter и получить цену. В первом листе в нужной ячейке можно поставить “=”, перейти на второй лист и указать третью ячейку с итогом. Опять нажать Enter и получить результат. Если необходимо провести такие расчеты по большому количеству пунктов, где изменяется только цена, то можно воспользоваться фундаментальным приемом Excel — автозаполнение формул, или протягивание. Возможность протягивать формулы — одно из базовых функций программы. Она автоматизирует процесс подсчета данных в таблице, без многократного прописывания одной и той же формулы. Выполнять протягивание формул можно следующим образом. В строке формул ставим равно и ссылку на ячейку из таблицы с исходными данными (=А3). После этого получим просто дублирование значения из таблицы. При протягивании этой ячейки получится копия таблицы с данным, которые будут изменяться соответственно со сменой информации в исходной таблице. Это пример протягивания ячеек без фиксирования диапазонов.
Можно закрепить ссылку, чтобы оставить ее неизменной при протягивании полностью, по строке или по столбцу. Фиксирование выполняется в строке формул с помощью знака $. Этот знак ставят перед той частью координат в ссылке, которую необходимо зафиксировать: $ перед буквой – фиксирование по столбцу — $С1 $ перед цифрой – фиксирование по строке — С$1 $ перед буквой и цифрой – полное фиксирование ячейки — $С$1.
2.Подсчет календарных дней
Excel может стать незаменимым помощником даже в таких простых действиях, как подсчет календарных дней. Бухгалтеру необходимо точно знать сколько дней было в том или ином периоде, чтобы рассчитать проценты, размер пени, неустойки, кредита и тому подобное. Если это небольшой отрезок времени, то его просто посчитать, воспользовавшись календарем, но для выполнения постоянных расчетов такой формат достаточно неудобен. В таких ситуациях приходит Excel в помощь бухгалтеру. Чтобы выполнить расчеты, необходимо выделить три свободных ячейки в таблице. В одну нужно записать начальную дату, во вторую конечную, а третью оставить пустой для получения результатов.
Рекомендация: набирайте дату на цифровой части клавиатуры так: 12/10/2016. Программа сама превратит введенные данные в формат даты и получится 12.10.2016. Далее выбираем третью ячейку и жмем “Вставить функцию”, вы можете найти ее по значку ¶x. После нажатия всплывет окно “Мастер функций”. Из списка “Категория” выбираем “Дата и время”, а из списка “Функция”— “ДНЕЙ360” и нажимаем кнопку Ок. В появившемся окне нужно вставить значения начальной и конечной даты. Для этого нужно просто щелкнуть по ячейкам таблицы с этими датами, а в строке “Метод” поставить единицу и нажать Ок. Если итоговое значение отражено не в числовом формате, нужно проверить формат ячейки: щелкнуть правой кнопкой мыши и выбрать из меню “Формат ячейки”, установить “Числовой формат” и нажать Ок. Еще можно выполнить подсчет дней таким способом: в третьей ячейке набрать = ДНЕЙ 360 (В1; В2; 1). В скобках необходимо указать координаты двух первых ячеек с датами, а для метода поставить значение единицы. При расчете процентов за недели можно полученное количество дней разделить на 7. Также к дате можно прибавлять и отнимать любое количество дней. Чтобы это выполнить, нужно в одной ячейке написать дату, во второй разместить знак равенства, затем щелкнуть по ячейке с датой и набрать “+” или “-” и требуемое количество дней.
3.Сортировка данных
Очень удобная функция, которая позволяет разместить данные по возрастанию/убыванию. Также сортировать данные можно и для упорядочивания записей по дате. Для выполнения этого действия необходимо выбрать область, которая требует сортировки. Затем можно нажать кнопку “Сортировка по возрастанию” в верхнем ряду меню “Данные”, ее вы найдете по знаку “АЯ”. Ваши данные разместятся от меньшего к большему по первому выделенному столбцу.
Таблицы Эксель для бухгалтера позволяют сортировать данные, начиная с первого выделенного столбца. Если вы выделили ячейки слева направо, то последовательность будет выполнена в крайнем левом столбце. Если справа налево, то в правом. Если данные нужно сортировать по среднему столбцу, то можно использовать меню “Данные” — пункт “Сортировка” — “Сортировка диапазона”. В разделе “Сортировать по” необходимо выбрать столбец и тип сортировки.
4.Работа с длинными таблицами
Таблицы Excel для бухгалтера — многофункциональный рабочий инструмент, который содержит множество информации для ведения отчетности и выполнения текущих расчетов. При печати таблицы, которая не умещается на один лист, можно разместить ее “шапку” на каждой отдельной страничке, что облегчит поиск необходимых данных. Для этого нужно выбрать в меню “Файл”— “Параметры страницы” и закладку “Лист”. Размещаем курсор на “Сквозные строки” или “Сквозные столбцы” и в таблице кликаем на строки, которые нужно разместить на каждом листе. Также для работы с такими документами можно использовать колонтитулы. В них отмечают необходимые данные, такие как дата, номера листов, имя составителя и прочее. Настройка колонтитулов доступна в “Параметрах страницы” — “Колонтитулы”. Там доступны готовые варианты разметки или возможность добавления собственного. Кроме полезных приемов по работе в Эксель, бухгалтеру необходимо освоить его горячие клавиши.
5.Сопоставление показателей за разные периоды
Часто данные за разные годы, кварталы, месяцы приходится сравнивать. Чтобы их сопоставить, нужно выбрать критерий — уникальный показатель для каждой единицы учета, например, артикул. Для этого используем функцию “ВПР” (“VLOOKUP”), которая к значениям по заданному критерию из первой таблицы подберет соответствующие из второй таблицы.
Чтобы сравнить, например, цены на товары, следуйте алгоритму:
На панели инструментов заходим в функции(“fx”) и выбираем “ВПР”. В появившемся окне указываем для нее аргументы:
- Искомое значение — критерий;
- Таблица — диапазон данных из второй таблицы, среди которых нужно найти соответствующие;
- Номер столбца — диапазон данных, к которым будут подбираться соответствующие;
- Интервальный просмотр — это степень соответствия, нам нужно точное, поэтому выбираем 0.
Копируем формулу до конца столбца. Сообщение ”#Н/Д” значит, что для указанного артикула во второй таблице значений не обнаружено.
На заметку!
Чтобы при копировании формулы вправо-влево и вверх-вниз диапазон данных не менялся, можно использовать символ «$». Для этого его нужно поставить перед названием строки или столбца, а чтобы закрепить ячейку — и там, и там.
6.Поиск показателей, соответствующих условиям
Возможности Excel облегчают поиск нужных данных. Разберем на примере поиска задолженности контрагента. Для этого:
- Рядом с таблицей, в которой будем искать, добавляем таблицу с условиями поиска. Для этого копируем шапку и в соответствующие столбцы вводим критерии поиска, используя знаки сравнения: «>», «<», «=».
- Заходим на вкладку “Данные” и выбираем пункт “Фильтр — Дополнительно”.
В открывшемся окне указываем:
- Исходный диапазон — полностью таблица, в которой будем искать;
- Диапазон условий — таблица с критериями поиска.
В результате получим значения, которые соответствуют заданным условиям.
7.Поиск ошибок в таблицах
Поиск опечаток и ошибок в таблицах работа долгая и скучная. Чтобы ускорить процесс, воспользуйтесь возможностями электронных таблиц Excel: Заходим на вкладку “Данные” и выбираем пункт “Проверка данных”.
В появившемся окне указываем:
- Тип данных — выбираем “список”;
- Источник — выделяем диапазон с образцовым списком.
В первом столбце таблицы появится кнопка “Список”. Ею можно пользоваться, чтобы в дальнейшем вводить данные без ошибок.
Далее выбираем пункт “Проверка данных — Обвести неверные данные” и ячейки, в которых есть опечатки, будут выделены овалом.
8.Формирование сводной таблицы
Подведение итогов за какой-то период — неотъемлемая часть работы бухгалтера. Формирование сводной таблицы с итоговыми показателями значительно упростит дело.
Для этого следуйте инструкции:
- Полностью выделяем диапазон с таблицей, переходим на вкладку “Вставка” и выбираем пункт “Сводная таблица”.
- В появившемся окне выбираем опцию “Поместить отчет на новый лист”.
- Заходим на новую вкладку и выбираем поля для новой таблицы.
- Для подведения итогов, выбираем диапазон данных за нужный период, кликаем правой кнопкой мышки и выбираем пункт “Группировать”.
На заметку!
Чтобы создать сводную таблицу на основе данных из нескольких таблиц, используйте “Мастер сводных таблиц и диаграмм” и выберите пункт “Создать таблицу на основе данных, находящихся в нескольких диапазонах консолидации”.
9.Выбор показателей в пределах лимита
Чтобы отобрать расходы в пределах установленного лимита, нужно выполнить в Excel следующие действия:
- Заходим на вкладку “Файл”, выбираем пункт “Параметры Excel – Надстройки”.
- В открывшемся окне ставим галочку напротив пункта “Поиск решения” и кликаем “Ок”( надстройка “Поиск решения”появится на вкладке “Данные”).
В свободной ячейке рядом с таблицей указываем предельную сумму(S1), а ниже находим S2. Для этого используем формулу “=СУММПРОИЗВ(Диапазон1*Диапазон)”, где: Диапазон1 — столбец со значениями, среди которых мы подбираем нужные; Диапазон2 — столбец, где будет указано, какие значения нужно сложить. Ниже находим разность S1 и S2 по формуле “=ABS(S1-S2)”. Переходим на вкладку “Данные”, выбираем пункт “Поиск решения”.
В открывшемся окне указываем:
- Установить целевую ячейку: ячейка с формулой “=ABS(S1-S2)”;
- Равной: минимальному значению;
- Изменяя ячейки: Диапазон2 — столбец, где будет указано, какие значения нужно сложить; Ограничения: добавляем для Диапазон2 и выбираем опцию “бин” (“двоич”).
Кликаем “Найти решение” или “Выполнить”и сохраняем результат. Напротив нужных сумм увидим единицы, напротив ненужных – нули.
При сложении отобранных программой значений получим число, максимально приближенное к предельному значению.
10.Определение влияния изменения данных на конечный расчет
А теперь научимся отслеживать, как изменение данных отражается на конечном расчете, на примере сметы расходов:
- Заходим на вкладку “Формулы” и выбираем пункт “Окно контрольного значения”.
- В открывшемся окне выбираем “Добавить контрольное значение” и указываем ячейку с итоговой суммой. Можно добавить сразу несколько контрольных показателей.
- Теперь при переходе на другой лист контрольное окно будет оставаться на экране. При изменении данных на других листах будет сразу видно, как это влияет на итоговые показатели.
11.Функция ЕСЛИ и выпадающий список
Одна из самых востребованных функций в Excel – функция ЕСЛИ. Она возвращает результат (значение или другую формулу) в зависимости от условия.
Функцию ЕСЛИ создают по такому синтаксису: ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Лог_выражение – это условие, которое нужно проверить.
Значение_если_истина – это то, что должно появиться в ячейке, если текст или цифра отвечают указанным требованиям.
Значение_если_ложь – то, что появится в ячейке, когда определится, что данные не соответствуют заданным параметрам.
Пример: =ЕСЛИ(В2>200;”Ок”;”Удалить”)
Функция проверяет ячейку В2 и сравнивает ее с логическим выражением – “>200”. Если истинное значение содержит цифру больше 200, то в столбце С появляется значение “Ок”, если меньше – “Удалить”.
Выпадающий список в Excel можно создать за пару кликов. Для этого необходимо:
1.Открыть таблицу с данными, которые нужно добавить в выпадающий список. Для версии Excel 2003 и более ранних делайте это через меню Вставка — Имя — Присвоить (Insert — Name — Define).
2.Для версий Excel 2007 и моложе нужно кликнуть на вкладку “Формулы”- “Диспетчер имен” и “Создать”. Введите название позиций (например Счета на оплату услуг), которые хотите сгруппировать и подтвердите его через “Ок”. Важно: имя должно быть без пробелов и начинаться с буквы.
3.Выделить ячейки, которые должны стать выпадающим списком. Выберите меню из вкладки “Данные” — “Проверка” (Data — Validation). Из предложенного списка выберите “Тип данных”, введите строку “Источник”= название вашего диапазона Счета на оплату услуг. Готово!
12.Набор горячих клавиш Excel, без которых вам не обойтись
Применение этих сочетаний клавиш в Excel ускорит работу и поможет в выполнении анализа данных, построении графиков и форматировании таблиц.
F4 — при вводе формулы, регулирует тип ссылок (относительные, фиксированные). Можно использовать для повтора последнего действия.
Shift+F2 — редактирование примечаний
Ctrl+; — ввод текущей даты (для некоторых компьютеров Ctrl+Shift+4)
Ctrl+’ — копирование значений ячейки, находящейся над текущей (для некоторых компьютеров работает комбинация Ctrl+Shift+2)
Alt+F8 — открытие редактора макросов
Alt+= — суммирование диапазона ячеек, находящихся сверху или слева от текущей ячейки
Ctrl+Shift+4 — определяет денежный формат ячейки
Ctrl+Shift+7 — установка внешней границы выделенного диапазона ячеек
Ctrl+Shift+0 — определение общего формата ячейки
Ctrl+Shift+F — комбинация открывает диалоговое окно форматирования ячеек
Ctrl+Shift+L — включение/ отключение фильтра
Ctrl+S — сохранение файла (сохраняйтесь как можно чаще, чтобы не потерять ценные данные).
Использование горячих клавиш и полезных приемов в Excel упростят и ускорят вашу работу только при условии того, что вы уже на достаточном уровне владеете этой программой. Если вы хотите повысить свой уровень и эффективнее использовать электронные таблицы вести бухгалтерский учет в Эксель, то можете бесплатно скачать книгу Макросы в Excel для новичков — схемы создания и применения, в которой уделено внимание вопросам повседневного использования Эксель в бухгалтерии. Книга подойдет как новичкам, так и опытным пользователям программы, которые хотят повысить свою квалификацию.
Не останавливайтесь на Excel!
Научитесь автоматизировать подготовку отчетов в программе Power BI на курсе «ACPM: Бизнес-анализ данных в финансах»!
Посмотреть пробный урок бесплатно
Насколько уверенно вы владеете Excel?
Существует ли бухгалтер, который выполняет только свою работу? Если это вы — вам сказочно повезло, не то что остальным 99%. Многим приходится не только вести учет, но и заменять собой других специалистов, например, юриста — составлять претензии, подсчитывать размер пени и неустоек, а еще аналитика — следить за изменением показателей (стоимостью закупаемого сырья, затратами на производстве и т.д.). Где тут успевать читать налоговые новости, когда шеф настойчиво требует ежедневный отчет. Хорошо, что у бухгалтеров есть Excel.
Нельзя сказать, что он идеальный и подходит для решения всех задач, но тоже совершенствуется. Например, сейчас востребованными стали такие инструменты как формулы DAX и мини-приложение для Excel под названием Power Pivot — для анализа данных (в том числе больших баз).
Мы считаем, что бухгалтер заслужил более современные методы работы и уже рассказывали о них в статье Tableau, SQL, Power BI, Python. Почему в этих словах придется разбираться бухгалтеру. И уже в 2020 году. Но мы понимаем, что расстаться со старым другом нелегко, так что решили рассказать о некоторых полезных приемах работы с Excel.
Подсчет календарных дней с помощью формулы
Для чего пригодится: посчитать дни для выставления пени по договору поставки или для расчета процентов по займу сотруднику. Удобно, если такие расчеты приходится делать постоянно без помощи программы, или вы хотите ее перепроверить (мы слышали иногда ваша любимая программа шалит).
Как сделать: чтобы провести расчет, необходимо три свободных ячейки в таблице.
В первую вписываем дату начал периода, во вторую — дату конца периода, третья остается пустой.
Выбираем третью ячейку и жмем «Вставить функцию». Вы можете найти ее по значку:
Или нажать Shift F3.
Появится окно со списком функций. Выбираем категорию «Дата и время», а из списка в окошке «Выберете функцию» — «ДНЕЙ360» и нажимаем Ок.
Еще одно окошко попросит ввести значение начальной и конечной даты — не нужно снова набирать их на клавиатуре, просто выберете соответствующие ячейки кликнув на них. В строке «Метод» ставим единицу и снова жмем Ок.
Лайфхак: если значение в ячейке отобразилось не в виде числа — измените формат ячейки ( щелкните правой кнопкой мыши, выберите «Формат ячейки» и установите «Числовой формат»).
Один нюанс — при расчете конечная дата в расчет дней не войдет. В нашем примере получилось 30 дней, если же в расчет должно войти 1 октября, то должно быть 31. Учитывайте это при расчете.
Сортировка данных
Для чего пригодится: буквально для всего. Если работаете с большой таблицей и надо сделать отбор определенных значений: сумма отгрузки от 100 тысяч, например, или все закупки после 1 сентября. Если нужно выстроить данные в порядке убывания цены или товары от А до Я.
Как сделать: можно воспользоваться простыми фильтрами. Вот у нас прайс с материалами, мы хотим найти все, что дороже 1000 рублей. Установим фильтр.
Выделяем заголовок таблицы (там где наименование, цена и т.п.) как на скрине:
Открываем вкладку «Данные» и выбираем в меню «Фильтр». Теперь у каждого столбца появился треугольничек фильтра. Выбираем тот, что привязан к колонке «Цена», и можем отметить галочками те значения, которые хотим там найти. Мы оставили только цены более 1000. Жмем Ок и вот результат:
С помощью того же фильтра можно сортировать данные от меньшего к большему, в алфавитном порядке и наоборот.
Сопоставление показателей за разные периоды
Для чего пригодится: проверить динамику показателя. Например, сравнить стоимость материалов по двум или более прайс-листам. Сравнение приходится делать с данными за разные годы, кварталы, месяцы или из разных источников.
Как сделать: для сопоставления понадобится «якорь» — уникальный показатель, свойственный для каждой единицы учета. Для товара или материала это может быть артикул.
Прибегнем к помощи функции «ВПР».
Итак, мы взяли и добавили в нашу книгу Excel еще один лист — Прайс 2, а в списке материалов вместо колонки порядкового номера появился артикул. Давайте сравним наши два прайса между собой.
Выделяем свободную ячейку рядом с первой строкой и колонкой цена (для наглядности), вот так:
На панели инструментов снова находим значок функций и нажимаем. Выбираем категорию — «Ссылки и массивы», функция — «ВПР».
Теперь надо настроить для нее аргументы. Искомое значение — наш «якорь» (выбираем первую ячейку в столбце Артикул в Прайсе № 2); Таблица — переходим на вкладку с первым прайсом (табличка последует за нами) и выделяем диапазон — всю таблицу со всеми строками и столбцами; Номер столбца — номер столбца с тем значением, которое хотим сравнивать — в нашем случае это цена — 5-й столбец; Интервальный просмотр — степень соответствия, чтобы искать точные значения ставим 0 и жмем Ок.
Получилось! Видим, что в первой ячейке цена такая же как и раньше, а что с остальными?
Протягиваем формулу на все ячейки нашего свободного столбца (ставим курсор на квадратик-уголок выделенной ячейки, нажимаем левую кнопку мыши и тянем вниз, не отпуская).
Если у вас появится значение «#Н/Д» — значит для этого артикула не нашлось значений во второй таблице.
На некоторые позиции изменилась цена, мы выделили ячейки, где цена снизилась зеленым, а где повысилась — красным цветом.
Лайфхак. Выделение можно сделать быстро с помощью панели инструментов. Посмотрите, вверху есть цветные кнопки с надписями «Плохой» , «Хороший» и другими. Достаточно встать на нужную ячейку и кликнуть соответствующую кнопку.
Поиск опечаток в таблицах
Для чего пригодится: страшный сон бухгалтера — в таблице Excel не выделяются допущенные ошибки. Пропустил букву в слове или местами перепутал, а то и совсем задумался, и вот в таблице уже на зарплата за сентябрь 2020-го, а «зряплата«…Так и директора обидеть недолго.
Чтобы ляпы не стали концом карьеры (и такое бывает), попробуйте проверять ваши таблички специальным инструментом.
Как сделать: мы допустили несколько неловких опечаток в Прайсе № 2 и теперь придется их найти и исправить.
Выделяем диапазон, который будем проверять — у нас это весь столбец 2. Заходим на вкладку «Данные» и выбираем пункт «Проверка данных». Если что спросит — соглашаемся.
Появилось окошко с настройками. Указываем в них: Тип данных — «список»; Источник — выделяем диапазон с ячейками, которые являются эталонными, это может быть, например, верный список наименований контрагентов или товаров. В нашем случае выделяем столбец 2 в Прайсе № 1, где названия все правильные. Жмем Ок.
В столбце таблицы вы заметите кнопку-треугольничек (список).
Теперь выберем на верхней панели «Проверка данных» — «Обвести неверные данные» и ячейки, в которых есть опечатки, будут выделены красным овалом.
Н-да, армЫтура, кирпичЬ и клюйкая лента это не дело — исправляем: щелкаем на ячейку с ошибкой, жмем треугольничек-список, выбираем верное значение. Теперь все в порядке. В меню проверки данных на панели сверху выбираем «Удалить обводку неверных данных».
Орфографию можно проверить при помощи еще одного инструмента Excel — меню Рецензирование — Орфография. Справочнику могут не понравится обычные сокращения, вроде «шт». Просто добавьте его в словарь и программа вас больше не побеспокоит.
Определение влияния изменения данных на конечный расчет
Для чего пригодится: при расчете сметы, когда подгоняем итоговую сумму под определенную величину и меняем, например, стоимость работ.
Как сделать: мы не стали создавать смету и покажем вам все на примере знакомого нам прайса, только теперь добавим к нему колонку со стоимостью всех остатков.
Итак, зайдите на вкладку «Формулы» и выберите «Окно контрольного значения».
В окне настроек нажмите «Добавить контрольное значение» и выделите ячейку с итогом, который хотите отследить, нажмите «Добавить». Так можно выбрать сразу несколько необходимых вам показателей.
Контрольное окно останется на экране, даже если вы перейдете на другой лист. В нем покажется значение итога при изменении каких-либо данных.
Мы переходить никуда не стали — просто изменили цену на первый товар в списке и итог поменялся:
При работе со связанными таблицами для расчетов на нескольких листах это удобно — не придется каждый раз возвращаться к итогам, чтобы проверить, как на них повлияли ваши манипуляции.
Это, конечно, далеко не полный список того, что умеет делать Excel. Мы в самом начале говорили о более современных инструментах — DAX и Power Pivot. Они помогут проделать более сложный анализ, к примеру, свести данные по инвентаризации из нескольких таблиц, находящихся в разных источниках данных. Это называется бизнес-аналитикой, которой мы на наших курсах тоже учим.
Тем, кто хочет узнать больше, пригодится курс Excel от Нетологии. Вы узнаете как работать с инструментами «классического» Excel (специальная вставка, проверка данных, разбивка текста по столбцам, форматирование ячеек и т.д.), научитесь грамотно писать формулы и использовать абсолютные и относительные ссылки. А еще:
- разберетесь в принципах эффективного анализа данных;
- научитесь использовать Power Query, таблицы и формулы для подготовки данных, сводные таблицы, Power Pivot;
- сможете использовать условное форматирование и диаграммы для визуализации результатов и многое другое.
Для тех, кто всерьез хочет изучить инструменты Excel (да, там еще много всего интересного) и стать его продвинутым пользователем, подойдет курс Power BI & Excel PRO. В результате прохождения курса вы научитесь:
- Обрабатывать большие массивы данных из разных источников. Сможете не хуже программистов строить сложные запросы с множеством параметров;
- Освоите сложные формулы в DAX. Научитесь создавать вычисляемые таблицы, столбцы и меры: от простых агрегатов до формул со скользящими периодами;
- Разберетесь как автоматизировать отчетность. Сможете создавать легко читаемые отчёты с богатой навигацией. Решите сложные и нестандартные задачи по преобразованию данных, научитесь создавать отчеты с использованием продвинутых сценариев и настройкой визуализаций.
Вы также узнаете о возможностях функционала Power BI Desktop — это интерактивные отчеты, работать с которыми можно с помощью облачных решений (бухгалтеры сейчас активно пользуются облачными технологиями и знают, как это удобно).
#Руководства
- 13 май 2022
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.
Скриншот: Skillbox Media
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Скриншот: Skillbox Media
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Скриншот: Skillbox Media
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Скриншот: Skillbox Media
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
- «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
- «Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
- «Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Скриншот: Skillbox
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Скриншот: Skillbox Media
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Скриншот: Skillbox Media
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Скриншот: Skillbox Media
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
Скриншот: Skillbox Media
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Скриншот: Skillbox Media
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Скриншот: Skillbox Media
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Скриншот: Skillbox
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Скриншот: Skillbox Media
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Скриншот: Skillbox Media
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
Скриншот: Skillbox
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Скриншот: Skillbox Media
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
Скриншот: Skillbox Media
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Скриншот: Skillbox Media
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Скриншот: Skillbox Media
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Скриншот: Skillbox Media
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Скриншот: Skillbox Media
Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше
Содержание
- 1 Примеры управленческого учета в Excel
- 1.1 Справочники
- 2 Удобные и понятные отчеты
- 2.1 Учет доходов
- 2.2 Учет расходов
- 2.3 Отчет о прибылях и убытках
- 2.4 Анализ структуры имущества кафе
- 2.5 Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ
- 2.6 Как быстро заполнить пустые ячейки в списке
- 2.7 Как найти ошибки в формуле
- 2.7.1 Вычисление отдельной части формулы
- 2.7.2 Как определить, от чего зависит или на что ссылается формула
- 2.8 Как найти сумму (количество, среднее) значений ячеек с нескольких листов
- 2.9 Как автоматически строить шаблонные фразы
- 2.10 Как сохранить данные в каждой ячейке после объединения
- 2.11 Как построить сводную из нескольких источников данных
- 2.12 Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)
Управленческий учет предназначен для представления фактического состояния дел на предприятии и, соответственно, принятия на основе данных управленческих решений. Это система таблиц и отчетов с удобной ежедневной аналитикой о движении денежных средств, прибылях и убытках, расчетах с поставщиками и покупателями, себестоимости продукции и т.п.
Каждая фирма сама выбирает способ ведения управленческого учета и нужные для аналитики данные. Чаще всего таблицы составляются в программе Excel.
Основные финансовые документы предприятия – отчет о движении денежных средств и баланс. Первый показывает уровень продаж, затраты на производство и реализацию товаров за определенный промежуток времени. Второй – активы и пассивы фирмы, собственный капитал. Сопоставляя эти отчеты, руководитель замечает положительные и отрицательные тенденции и принимает управленческие решения.
Справочники
Опишем учет работы в кафе. Предприятие реализует продукцию собственного производства и покупные товары. Имеют место внереализационные доходы и расходы.
Для автоматизации введения данных применяется таблица управленческого учета Excel. Рекомендуется так же составить справочники и журналы с исходными значениями.
- Типы контрагентов. Кафе сотрудничает с поставщиками продуктов питания, оборудования для хранения, подработки, подсортировки и упаковки товаров, санитарной одежды и столового белья, посуды. В этот же список можно внести банки, поставщиков коммунальных услуг и др.
- Статьи затрат. Перечень зависит от работы конкретного пункта общественного питания.
Если экономист (бухгалтер, аналитик) планирует расписывать по статьям и доходы, то такой же справочник можно создать для них.
Удобные и понятные отчеты
Не нужно все цифры по работе кафе вмещать в один отчет. Пусть это будут отдельные таблицы. Причем каждая занимает одну страницу. Рекомендуется широко использовать такие инструменты, как «Выпадающие списки», «Группировка». Рассмотрим пример таблиц управленческого учета ресторана-кафе в Excel.
Учет доходов
Присмотримся поближе. Результирующие показатели найдены с помощью формул (применены обычные математические операторы). Заполнение таблицы автоматизировано с помощью выпадающих списков.
При создании списка (Данные – Проверка данных) ссылаемся на созданный для доходов Справочник.
Учет расходов
Для заполнения отчета применили те же приемы.
Отчет о прибылях и убытках
Чаще всего в целях управленческого учета используется отчет о прибылях и убытках, а не отдельные отчеты по доходам и расходам. Данное положение не нормируется. Поэтому каждое предприятие выбирает самостоятельно.
В созданном отчете для подсчета результатов используются формулы, автозаполнение статей с помощью выпадающих списков (ссылки на Справочники) и группировка данных.
Анализ структуры имущества кафе
Источник информации для анализа – актив Баланса (1 и 2 разделы).
Для лучшего восприятия информации составим диаграмму:
Как показывает таблица и рисунок, основную долю в структуре имущества анализируемого кафе занимают внеоборотные активы.
Скачать пример управленческого учета в Excel
По такому же принципу анализируется пассив Баланса. Это источники ресурсов, за счет которых кафе осуществляет свою деятельность.
Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.
Работая в Excel с таблицами, вы сможете создавать отчеты, делать расчеты, строить графики и диаграммы, сортировать и фильтровать информацию.
Если ваша работа связана с обработкой данных, то навыки работы с таблицами в Эксель помогут вам сильно сэкономить время и повысить эффективность.
Как работать в Excel с таблицами. Пошаговая инструкция
Прежде чем работать с таблицами в Эксель, последуйте рекомендациям по организации данных:
- Данные должны быть организованы в строках и столбцах, причем каждая строка должна содержать информацию об одной записи, например о заказе;
- Первая строка таблицы должна содержать короткие, уникальные заголовки;
- Каждый столбец должен содержать один тип данных, таких как числа, валюта или текст;
- Каждая строка должна содержать данные для одной записи, например, заказа. Если применимо, укажите уникальный идентификатор для каждой строки, например номер заказа;
- В таблице не должно быть пустых строк и абсолютно пустых столбцов.
1. Выделите область ячеек для создания таблицы
Выделите область ячеек, на месте которых вы хотите создать таблицу. Ячейки могут быть как пустыми, так и с информацией.
2. Нажмите кнопку “Таблица” на панели быстрого доступа
На вкладке “Вставка” нажмите кнопку “Таблица”.
3. Выберите диапазон ячеек
В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.
4. Таблица готова. Заполняйте данными!
Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.
Форматирование таблицы в Excel
Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:
Если 7-ми стилей вам мало для выбора, тогда, нажав на кнопку, в правом нижнем углу стилей таблиц, раскроются все доступные стили. В дополнении к предустановленным системой стилям, вы можете настроить свой формат.
Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:
- Отображение строки заголовков – включает и отключает заголовки в таблице;
- Строку итогов – включает и отключает строку с суммой значений в колонках;
- Чередующиеся строки – подсвечивает цветом чередующиеся строки;
- Первый столбец – выделяет “жирным” текст в первом столбце с данными;
- Последний столбец – выделяет “жирным” текст в последнем столбце;
- Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
- Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.
Как добавить строку или столбец в таблице Excel
Даже внутри уже созданной таблицы вы можете добавлять строки или столбцы. Для этого кликните на любой ячейке правой клавишей мыши для вызова всплывающего окна:
- Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.
- Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.
Как отсортировать таблицу в Excel
Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:
В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.
Как отфильтровать данные в таблице Excel
Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:
- “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
- “Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
- “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
- В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.
Если вы хотите отменить все созданные настройки фильтрации, снова откройте всплывающее окно над нужной колонкой и нажмите “Удалить фильтр из столбца”. После этого таблица вернется в исходный вид.
Как посчитать сумму в таблице Excel
Для того чтобы посчитать сумму колонки в конце таблицы, нажмите правой клавишей мыши на любой ячейке и вызовите всплывающее окно:
В списке окна выберите пункт “Таблица” => “Строка итогов”:
Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.
В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.
Как в Excel закрепить шапку таблицы
Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.
Для того чтобы закрепить заголовки сделайте следующее:
- Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:
- Выберите пункт “Закрепить верхнюю строку”:
- Теперь, прокручивая таблицу, вы не потеряете заголовки и сможете легко сориентироваться где какие данные находятся:
Как перевернуть таблицу в Excel
Представим, что у нас есть готовая таблица с данными продаж по менеджерам:
На таблице сверху в строках указаны фамилии продавцов, в колонках месяцы. Для того чтобы перевернуть таблицу и разместить месяцы в строках, а фамилии продавцов нужно:
- Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):
- Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:
- В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:
- Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.
В этой статье вы ознакомились с принципами работы в Excel с таблицами, а также основными подходами в их создании. Пишите свои вопросы в комментарии!
Дата: 13 марта 2017 Категория: Excel Поделиться, добавить в закладки или статью
Здравствуйте, друзья. Как часто Вам приходится обобщать большие массивы данных? Получать промежуточные итоги? Если часто, значит сводные таблицы Excel – это то, что Вам нужно срочно! Создание сводной таблицы занимает всего пару минут, а результат – как будто работали целую неделю. Заманчиво? Читаем!
Сводная таблица – это мощный инструмент Microsoft Excel, решающий многие задачи, а главное – отвечающий на многие вопросы о процессах, описанных цифрами в Вашем файле. Приведу пример. На изображении ниже – список продаж торговых точек различных регионов с детализацией по дням в течение года:
Правда же, эта таблица мало информативна и в таком виде не представляет пользы? А вот сводная таблица, сформированная из этих данных:
Здесь все продажи систематизированы по регионам и менеджерам в строках, по группам товара в столбцах – в столбцах. Такие данные уже пригодны, как минимум, для последующего анализа. Наглядно видим, сводная таблица эффективно обобщает большие объемы данных и, как я расскажу дальше, не требует значительных усилий и времени на построение.
Не каждый диапазон данных в Эксель можно применить для построения сводной таблицы. Данные должны быть нормализованы. В нашем случае, это значит, что каждая строка должна описывать одно событие, и для нее должны быть заполнены все столбцы. В каждой строке столбца должны содержаться данные одного типа. Посмотрите еще раз, как это выглядит на первом рисунке поста.
Обязательно каждый столбец должен иметь информативный заголовок, т.е. шапка должна быть полной.
Я рекомендую на этапе проектирования таблицы продумывать, будете ли пользоваться сводными таблицами. Если будете – обязательно учитывайте изложенные требования.
Чтобы сделать сводную таблицу на основании своих данных – выполните такую последовательность действий:
- Установите курсор в любую ячейку таблицы
- Нажмите на ленте: Вставка – Сводная таблица
- Укажите расположение будущей сводной таблицы. Чтобы поместить ее на новый лист – установите галку «На новый лист». Чтобы выбрать расположение на существующих листах – выберите «На существующий лист» и в поле «Диапазон» укажите расположение верней левой ячейки сводной таблицы;
- Нажмите Ок
Откроется пустая область сводной таблицы и меню компоновки данных. Последнее состоит из пяти окон:
- «Выберите поля для добавления в отчет» — это заголовки всех столбцов, которые есть в таблице. Этими данными мы будем заполнять следующие 4 блока
- Фильтры – список полей, по которым будет применяться фильтр. Эти поля появляются над сводной таблицей
- Колонны – область, где задается, что будет содержаться в столбцах
- Строки – область, где указывается, что будет содержаться в строках
- Значения – задаем то, что будет отображаться или рассчитываться на пересечении строк или столбцов. То есть, основное тело таблицы
Области 2-5 заполняются данными перетягиванием заголовков из п.1. Например, нужно узнать, какая сумма продаж за год у менеджеров всех регионов. Значит, в строках у нас будут регионы и менеджеры, а в значениях – сумма продаж. Перетаскиваем соответствующие наименования столбцов из первой области меню компоновки в «Строки» и «Значения». Вот что получится:
Если теперь мы захотим, чтобы в столбцах данные были разбиты по группам товаров. Перетянем поле «Группа товара» в «Колонны», получаем результат:
А если вдруг мы решили, что нужны данные только по первому региону, добавим поле «Регион» и в «Фильтры», над сводной таблицей появится область фильтров. Открываем раскрывающийся список в этой области и выбираем только первый регион.
Мне кажется, это очень простой инструмент, и его обязательно нужно освоить. Представьте, в моем списке, который служит примером для этого поста – 9 883 строки, и я обрабатываю их без усилий, просто делаю несколько кликов мышью. И такая таблица, как мы с Вами только что сделали, уже похожа на профессиональный отчет.
А теперь нам, к примеру, захотелось узнать, кто из менеджеров продает больше всего. Снимем все фильтры, уберем галку «Регион» из строк. Получаем список менеджеров и их продажи. Кликнем правой кнопкой мыши в любой из строк «продажи» колонки «Общий итог», в контекстном меню выбираем Сортировка – Сортировка по убыванию. Естественно, сверху будет менеджер с наибольшими продажами, снизу – с наименьшими.
В поле «Значения» можно не только суммировать данные. Можно например, посчитать количество значений, отобразить минимальное или максимальное значение и многое другое. Для этого кликните правой кнопкой мыши на любой ячейке нужного столбца, в контекстном меню нажмите «Итоги по», а далее выбирайте ту функцию, которая нужна.
Вы можете настраивать макет сводной таблицы в части логики построения. Выделите любую ее ячейку и найдите на ленте Конструктор – Макет. Здесь можно сделать настройки по четырем пунктам:
- Промежуточные итоги – включить или отключить итоги для промежуточных групп внутри таблицы
- Общие итоги – настроить расчет общих итогов по всей таблице
- Макет отчета – способ компоновки данных для наибольшего удобства
- Пустые строки – вставить или удалить пустые строки в конце каждой категории для улучшения восприятия данных.
Рекомендую Вам поэкспериментировать с этими настройками, чтобы найти Вашу оптимальную комбинацию.
Раз уж сводные таблицы претендуют на звание универсального инструмента для выполнения отчетов, они должны быть гибкими в настройке внешнего вида. Для оформления Вы можете:
- Настраивать форматы данных
- Изменять внешний вид ячеек, применять стили
- Применять условное форматирование
Выделяйте ячейки, и применяйте к ним уже привычные Вам операции форматирования. Как правило, этого достаточно, чтобы готовый отчет был информативным и удобным к восприятию.
Чтобы еще детальнее настроить внешний вид – кликните правой кнопкой мыши на любой ячейке сводной таблицы и в контекстном меню выберите «Параметры сводной таблицы». Здесь собрано несколько полезных настроек. Например, задайте что отображать вместо кодов ошибок, или настройте детализацию вывода на печать сводной таблицы.
Если Ваша таблица не совсем вас удовлетворяет, и Вам хотелось бы немного изменить ее содержание в части содержимого строк, столбцов и основных данных – можете делать это в любой момент. Перетягивайте блоки заголовков в меню настройки сводных таблиц, удаляйте и добавляйте, изменяйте фильтры. Программа незамедлительно среагирует на внесенные Вами изменения.
На этом все о создании сводных таблиц, но тема все еще не закрыта и в следующей статье я расскажу о расширенных возможностях в работе с этим инструментом. Рекомендую к прочтению и использованию, ведь нет ничего лучше, чем получать результаты быстро и без усилий!
Как всегда, жду Ваших вопросов и комментариев, будем становиться профессионалами вместе!
Поделиться, добавить в закладки или статью
В этом посте Ренат Шагабутдинов, ассистент генерального директора издательства «Манн, Иванов и Фербер», делится классными Excel-лайфхаками. Приведённые советы будут полезны для всех, кто занимается различной отчётностью, обработкой данных и созданием презентаций.
Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: основные книги и онлайн-ресурсы, а также пошаговый алгоритм создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие упростить работу в Excel. Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну — в том или ином виде эти приёмы наверняка обсуждались на форумах или упоминались в статьях.
Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ
Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.
В остальных случаях есть два варианта:
- Использовать функцию ПРОСМОТР (LOOKUP).
У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию: - Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
• ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
• ИНДЕКС (массив; номер_строки; ). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.
Как быстро заполнить пустые ячейки в списке
Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):
Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:
Как найти ошибки в формуле
Вычисление отдельной части формулы
Чтобы разобраться в сложной формуле (в которой в качестве аргументов функции используются другие функции, то есть одни функции вложены в другие) или найти в ней источник ошибок, часто нужно вычислить её часть. Есть два простых способа:
- Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:
В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом. - Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:
В появившемся окне можно вычислять формулу по шагам и определить, на каком этапе и в какой функции возникает ошибка (если она есть):
Как определить, от чего зависит или на что ссылается формула
Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:
Появляются стрелки, указывающие, от чего зависит результат вычислений.
Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:
Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:
Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.
Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:
Как найти сумму (количество, среднее) значений ячеек с нескольких листов
Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:
Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:
Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:
Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).
Как автоматически строить шаблонные фразы
Используя базовые принципы работы с текстом в Excel и несколько простых функций, можно готовить шаблонные фразы для отчётов. Несколько принципов работы с текстом:
- Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
- Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
- Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.
Пример создания шаблонной фразы с помощью формул:
Результат:
В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
ТЕКСТ (значение; формат)
Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».
Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.
Как сохранить данные в каждой ячейке после объединения
При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:
Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:
Как построить сводную из нескольких источников данных
Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
При щелчке на неё появляется диалоговое окно:
В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных — выберите второй пункт:
В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:
После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы — на существующем или новом листе:
Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:
Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)
В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
- ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
- ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
- ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)
Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
А затем разделить эту разницу на длину той строки, которую мы искали:
6 / ДЛСТР (“МТС”) = 2
Именно два раза строка «МТС» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):
=(ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый);“”)))/ДЛСТР(искомый)
В нашем примере формула выглядит следующим образом:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)
Складской учет через Excel позволяет считать: остаток, расход и приход всякой продукции на складе фирмы или какого-либо завода, в особенности для тех организаций, которым требуется постоянное ведение отчетности материалов или готовых изделий. Если на больших предприятиях заказывают специальные программы, то в небольших фирмах обычно применяют табличный процессор, функциональности которого хватит для этих целей.
Excel — легкая программа ведения автоматизированного контроля на складе
Является простой и понятной платформой, к тому же поставляется совместно с самой популярной операционной системой — Windows.
Если хотите отказаться от ручного учета товаров на складе, то обратите внимание на наше решение для автоматизации. Узнать подробнее >>
Как вести складской учет в Excel (Эксель): особенности
Существуют определенные особенности использования, которые следует учитывать при его использовании:
- нет очередей на складе;
- вы нацелены на то, чтобы кропотливо работать с артикулами и сводками;
- готовы забывать в базу данных всю информацию вручную.
Также эффективно работать с Экселем можно в случае, когда контролем занимаются лишь несколько человек.
Готовые решения для всех направлений
Сократите издержки в работе склада до 70% : ускорьте складские операции, устраните ошибки человеческого фактора и забудьте про потерю и пересортицу товаров.
Узнать больше
У вас всегда будут актуальные ценники на товарах и витринах, товар перестанет теряться при перемещении между магазинами, а в вашей системе учета — только точные остатки по товарам.
Узнать больше
С маркировкой вы на 100% исключите приемку контрафактного товара на свой склад, а также сможете отслеживать полную цепочку поставок товара от производителя.
Узнать больше
Скорость, точность приёмки и отгрузки товаров на складе — краеугольный камень в E-commerce бизнесе. Начни использовать современные, более эффективные мобильные инструменты.
Узнать больше
Повысьте точность учета имущества организации, уровень контроля сохранности и перемещения каждой единицы. Мобильный учет снизит вероятность краж и естественных потерь.
Узнать больше
Повысьте эффективность деятельности производственного предприятия за счет внедрения мобильной автоматизации для учёта товарно-материальных ценностей.
Узнать больше
Первое в России готовое решение для учёта товара по RFID-меткам на каждом из этапов цепочки поставок.
Узнать больше
Исключи ошибки сопоставления и считывания акцизных марок алкогольной продукции при помощи мобильных инструментов учёта.
Узнать больше
Получение сертифицированного статуса партнёра «Клеверенс» позволит вашей компании выйти на новый уровень решения задач на предприятиях ваших клиентов..
Узнать больше
Используй современные мобильные инструменты для проведения инвентаризации товара. Повысь скорость и точность бизнес-процесса.
Узнать больше
Показать все решения по автоматизации
Кому могут помочь электронные реестры
Они используются теми предпринимателями, у которых небольшой поток покупателей, имеется достаточно времени для переноса информации о продаже в электронную таблицу.
Вот приблизительная «эволюция» товароучета на небольших предприятиях. Этот рисунок был выполнен после опроса нескольких десятков предпринимателей.
Главным недостатком учета товара в Excel (Эксель) является то, что его невозможно связать с кассой.
Мобильное решение от Клеверенс исключает этот недостаток. Подробнее >>
Но при этом у ПО существуют и преимущества:
- в интернете имеется большое число свободных шаблонов для контроля;
- также можно самому или из ютуб-уроков научиться их заполнению.
Как вести контроль
Конечно, одного и того же ответа на этот вопрос дать нельзя, но существуют рекомендации, способные помочь вам понять, как вести учет товаров склада в Excel (Эксель) правильно.
- Требуется сделать справочники, для маленьких предприятий с одним магазином это условие не является обязательным.
- При некотором постоянстве перечня продукции нелишне вводить лист под названием «Номенклатура», в котором все будет представлено в электронном виде. Он может включать в себя совершенно разные сведения о продуктах, в зависимости от особенностей вашего бизнеса.
- Поступающий продукт должно учитывать на листе под названием «Приход», страница «Расход» предназначается выбывшим позициям, а в «Остатке» следует записывать нынешнее состояние.
Организация складского учета в Excel — таблица
Программа ведения создается очень просто, для этого рассмотрим пример, на который вы сможете ориентироваться при формировании своего софта.
Создаем справочники
Справочники «Поставщики» и «Покупатели» заполняются по одной и той же форме: первый столбец — наименование компании, второй — код, третий — юридический адрес, и четвертый — комментарий, в котором вы можете вносить различные примечания, связанные с организацией.
Затем создадим справочник «Точки учета», это требуется, если в вашей компании несколько магазинов и/или складов. Она состоит из трех столбцов.
- Название места.
- Кода точки.
- Комментария.
После чего создадим раздел «Номенклатура» состоящий из 5 столбцов.
- Название.
- Сорт (или про другую подобную характеристику).
- Единица измерения.
- Размеры.
- Комментарий.
Разработка «Прироста»
Создаем новый лист и называем его «Приход». Он будет состоять из 10 колонок: имя, дата, № накладной, поставщик, код, точка контроля, единицы измерения, количество, цена и стоимость.
Таблица для учета расходов товаров в Excel (Эксель) составляется аналогичным образом, только после графы «№ накладной» идут следующие столбцы: «Точки отгрузки», «Точки поставки», а также столбец «Покупатель» — затем следует сразу же начать вставить четыре последних полос из листа «Приход».
Как наладить автоматическую работу
Чтобы все работало в режиме автоматизации, нужно связать некоторые столбцы из неодинаковых таблиц, такие как заглавие изделия, ед.изм., количество и цена. Чтобы сделать это, следует в реестрах, графа в котором присутствует 2-й и последующие разы, указать в качестве типа данных «Список», а строка «Источник» состоит из надписи: ДВССЫЛ («номенклатура!$А$ 4:$A$ 8») — приведен пример к столбцу «Наименование товара». По аналогии надо сделать и в оставшихся столбцах.
«Итоги»
«Оборотная ведомость»/»Итоги» состоит из 8 столбцов: наименование, ед.изм., сорт, характеристика, остаток на начало, поступление, отгрузка, сальдо на _, шт.
Каждый из них заполняется автоматически, лишь «Отгрузка» и «Поступление» используя формулу: СУММЕСЛИМН, а остаток — с помощью математических операций.
Инструкция по ведению
Для внедрения учета необходимо следовать следующему порядку шагов.
- Сначала следует провести инвентаризацию, главное — делать это внимательно, не допуская ошибок.
- Определить структуру.
- Следует выбрать шаблон, по которому будет вестись таблица, ведь помимо предложенного нами, в сети существует ещё большое количество всяческих вариаций, которые предназначены для многообразных целей.
- Сделайте или скачайте, а затем установите образец.
- Проведите первичное детальное заполнение справочников.
- Если требуется, то проведите редакцию, введя новые поля.
- Проверьте ошибки, проведя имитацию активности в магазине.
- Научите работников работать с ПО.
- Лучше создавать на каждый отчетный период отдельные листы, для предотвращения нагромождения.
Несколько обязательных принципов
При формировании собственной программы для учета склада в Excele необходимо придерживаться всех правил, чтобы не допустить ошибок.
Распорядок приемки
- Принимать товар у подрядчика следует, придерживаясь одних и тех же правил. Кладовщик обязан не только изучить документы но, но и также сверить позиции и провести сравнение сведений.
- Не вносить изменения в таблицу, пока не будет проведена сверка.
- Во избежание ошибок необходимо делать перепроверку показаний на всех этапах.
Режим перемещения
Довольно часто случается так, что изделие перемещается от одного хранилища к другому, или же он мигрирует внутри склада. В таком случае ответственный с того склада, откуда переместилось изделие, делает пометку в листе «Расход», а ответственный на базе, куда поступило, делает об этом заметку в «Приходе». Когда же подобные манипуляции происходят в пределах одного и того же помещения, эти операции выполняет один и тот же работник.
Норма отпуска со склада
В случае использования одной таблицы для всего, фиксация движения товара происходить внутри файла. Если вы работаете не с одним документом, то всё это отражается в обоих файлах, и делать это будете вручную.
Условия проведения инвентаризаций, когда используются Excel-таблицы
Процесс слишком слабо автоматизирован, из-за этого проведение инвентаризации будет примитивным, но одновременно простым. Всё, что потребуется, это пересчитать изделия вручную, а после сравнить полученные данные с цифрами из документа. После частичного списания документ надо привести к соответствию с информацией об инвентаризации, сделать это надо вручную.
Со слов наших клиентов ПО «Склад 15» ускоряет процесс инвентаризации в 5 раз. Узнать подробнее >>
Шаблон Excel при аналитике продаж
При учете продукта подобает подбирать удобный для вас стандарт, в который вы будете вносить какие-либо сведения.
Так, шаблон продаж может принять следующий вид.
- Наименование.
- Его артикул.
- Ед.изм.
- Количество.
- Стоимость.
- Цена.
Для гораздо большего удобства проведения аналитики следует создавать для каждого месяца новый лист. Ниже приведен пример таблицы учета склада в Excel.
|
А |
В |
С |
D |
|
Поставщики |
|||
|
Наименьшее |
Код |
Юридический адрес |
Комментарий |
|
ООО «Москва» |
12-01 |
||
|
ООО «Лето-3» |
12-02 |
||
|
ЗАО «Утро» |
12-03 |
Готовые решения для всех направлений
Сократите издержки в работе склада до 70% : ускорьте складские операции, устраните ошибки человеческого фактора и забудьте про потерю и пересортицу товаров.
Узнать больше
У вас всегда будут актуальные ценники на товарах и витринах, товар перестанет теряться при перемещении между магазинами, а в вашей системе учета — только точные остатки по товарам.
Узнать больше
С маркировкой вы на 100% исключите приемку контрафактного товара на свой склад, а также сможете отслеживать полную цепочку поставок товара от производителя.
Узнать больше
Скорость, точность приёмки и отгрузки товаров на складе — краеугольный камень в E-commerce бизнесе. Начни использовать современные, более эффективные мобильные инструменты.
Узнать больше
Повысьте точность учета имущества организации, уровень контроля сохранности и перемещения каждой единицы. Мобильный учет снизит вероятность краж и естественных потерь.
Узнать больше
Повысьте эффективность деятельности производственного предприятия за счет внедрения мобильной автоматизации для учёта товарно-материальных ценностей.
Узнать больше
Первое в России готовое решение для учёта товара по RFID-меткам на каждом из этапов цепочки поставок.
Узнать больше
Исключи ошибки сопоставления и считывания акцизных марок алкогольной продукции при помощи мобильных инструментов учёта.
Узнать больше
Получение сертифицированного статуса партнёра «Клеверенс» позволит вашей компании выйти на новый уровень решения задач на предприятиях ваших клиентов..
Узнать больше
Используй современные мобильные инструменты для проведения инвентаризации товара. Повысь скорость и точность бизнес-процесса.
Узнать больше
Показать все решения по автоматизации
«Складской учет» в Эксель
Таблица в обязательном порядке должна состоять из таких разделов.
- Артикул, необходимый, чтобы быстро искать товар в списке, его берут согласно данным производителя.
- Название с краткой характеристикой. К примеру: сарафан «Роза» красный с цветами.
Также название возможно придумать самому или же взять у производителя.
- Единица измерения. Могут быть как штуки, так и погонные метры с другими видами измерения.
- Остатки.
- Цена.
- Шипчандлер.
Помимо этого, вы можете создавать и другие столбцы, всё зависит от специализации вашего бизнеса.
На отдельной странице нужно поместить справочник представителей торговых организаций (ритейлеров).
Проблемы при работе с реестром
В таблице Excel (Эксель) при учете расхода и прихода товаров используется программа, но она имеет ряд минусов.
- В случае случайного изменения сотрудником данных, а затем выходя из документа, сохранив его, отыскать ошибку будет трудно.
- Чтобы учитывать все удаленно, можно использовать Google Tables, но когда будет достигнут порог в 100 и более строк, программа станет «тормозить».
- Запрещено интегрировать таблицу с ПО ведения бухгалтерии и кассой.
- Не существует шансов провести контроль остатка по сроку реализации, планировки поставок, доходов и т. д.
- Не выйдет включить сканер, работа производится вручную.
- Файл не может использоваться единовременно разными пользователями, за исключением таблицы от Гугла.
- Отсутствие настройки автоматической проверки сведений, кроме числовых, буквенных и т. п.
Обзор бесплатного программного обеспечения учета
Чтобы сэкономить бюджет компании, можно прибегнуть к общедоступному софту. В интернете можно найти программы, которые на первых этапах бизнеса могут стать прекрасной альтернативой Excel.
Редактор OpenOffice
Этот вариант является самым популярным среди пользователей. Поставляется в составе бесплатного пакета Apache OpenOffice.
Софт имеет русскую локализацию, и грузится быстрее Экселя, при меньшем весе продукта.
Единственным неудобством при работе может быть то, что автоматически файлы сохраняются в формате ODS, но также имеется формат XLS.
LibreOffice Calc
По интерфейсу и доступным функциям очень похожа на предыдущую. Что не удивительно, ведь разрабатывалась она одним из программистов OpenOffice. Но в отличие от неё, программа умеет не только читать, но и также сохранять файлы в более совершенно формате XLSX.
Открывается приложение в течение нескольких секунд. Имеется возможность поработать с макросами и дополнительными функциями.
PlanMaker
PlanMaker является третьим бесплатным решением для ведения отчетности. Разработчиком она включена в пакет SoftMaker.
Может выполнить те же операции, что и две предыдущие, но в отличие от них имеет бесплатную и платную версии. Первая поддерживает старый формат Эксель — XLS, а вот платное ПО будет работать с файлами более современного типа — XLSX.
Что дает учет через Excel
Функционал ориентируется на макросы. Но чем опытнее программист, написавший программу, тем большее число различных действий может делать юзер.
Наиболее встречаемые действия, в таких Эксель-шаблонах, следующие:
- Приход и расход товаров на разных листах.
- Поиск в базе настраиваемый.
- Перспектива вести клиентскую базу.
- Формируется прайс.
- Отображаются скидки.
- Возможно создать автонаценки.
- Возможность вести финансовую отчетность.
- История отгрузки и поступление доступна ламеру.
- Можно формировать оборотную ведомость за отчетный период.
- Можно, используя различное число фильтров, просматривать текущее количество.
- Согласно введенным данным создается шаблон счета, а также накладная для печати.
Перечисленные выше функции практически максимальные, которые возможно создать, работая в табличном процессоре Microsoft Excel.
Аналоги в платном сегменте
Отличия бесплатно распространяемых продуктов от программ, за которые необходимо заплатить, заключается в том, что все шаблоны уже загружены в них. Но при этом они десктопные, что является неудобным при работе с нескольких устройств из-за того, что ПО загружается в память девайса. Ниже приведено сравнение.
Таблица — Сравнение «Склад Excel» и «Склад производства»
|
Название |
Ключевые характеристики |
Стоимость в рублях |
|
Склад Excel |
|
Семь-восемь тысяч рублей |
|
Склад производcтва |
|
8 500 рублей |
Достоинства учета складских остатков
Из-за некоторых преимуществ, она популярна среди некоторых предпринимателей.
- Относительная дешевизна.
- Не требуется интернет.
- Бесконечная настройка под себя.
- Потенциал ведения нескольких таблиц.
- Простота в ведении.
- Готовые шаблоны.
- Схожие по функционалу программы, за которые не надо платить.
- Имеется реализация подключения мобильной кассы, но все данные придется вносить всё равно вручную.
Схема подключения
Складской учет: недостатки ведения
Из-за больших минусов контроль с помощью Эксель применяется лишь малыми предпринимателями.
- Одна ошибка при заполнении реестра складского учета приходов, расходов, остатков в Excel с большой долей вероятности приведет к серьезному перекосу отчетности.
- Исключено напрямую интегрировать онлайн-кассу.
- Промежуточные носители информации необходимы в использовании.
- Сложно формировать в автоматическом режиме различные документы.
- Присутствует возможность потери данных.
- Невыполнимо удаленное ведение.
- При проведении инвентаризации возникают трудности.
- Отсутствует техническая поддержка.
Мы постарались подробно рассказать о том, как правильно вести учет на складе товаров, все нюансы и правила. Важно учитывать специфику деятельности вашего предприятия. Компания «Клеверенс» реализует ПО для различных отраслей бизнеса, поэтому вы можете смело обращаться к нам за помощью в подборе необходимого софта.
Количество показов: 186411
За долгие годы своего существования Microsoft Excel стал незаменимой «рабочей лошадкой» для многих миллионов прикованных к стулу тружеников. Зачастую в головах пользователей Excel является синонимом самой работы. Давайте сломаем стереотипы и возьмём на вооружение 10 отличных шаблонов Excel на каждый день.
Отрадно, что корпорация Microsoft всеми силами идёт нам навстречу, предоставляя абсолютно бесплатный доступ к своей офисной продукции для мобильных устройств. Поэтому вы можете вполне удобно использовать мощный инструментарий Excel на своём рослом iPhone, на iPad и на Android-гаджетах с крупной диагональю.
Не забывайте и о том, что предлагаемые шаблоны могут быть доработаны с учётом ваших пожеланий, а также распечатаны, если экрану вы предпочитаете бумагу. Но прежде чем браться за коррективы, почитайте об эффективных приёмах, секретах и распространённых ошибках работы в Excel. С таким багажом знаний вам не страшен этот могучий монстр!
1. Список задач
Даже самая светлая голова при ясном уме и твёрдой памяти однажды даст сбой, и вы что-то забудете. Хорошо, если это будет покупка корма для рыбок, поздравление тёщи с Днём матери или поливка фиалки вашей второй половинки. Они побулькают, пошипят и поквакают, а ваша совесть останется чистой. Но вдруг вы не оплатите предмет первой необходимости — интернет? Вам будет стыдно смотреть на себя в зеркало. И в тот злополучный день вы сломаетесь и пообещаете составлять списки дел. А пока вы будете мучиться выбором среди электронных планировщиков, попробуйте начать с простого перечня задач.
Назначайте задания, устанавливайте им приоритет, задавайте дедлайн, выбирайте ответственное лицо, следите за прогрессом выполнения и оставляйте заметки, не выходя из Excel. Шаблон уже настроен на быструю сортировку по дате, важности, статусу и другим параметрам.
2. Бюджет путешествия
Теоретически даже самое нереальное (и при этом полноценное) путешествие может обойтись вообще без финансовых вложений с вашей стороны. Для этого необходимо заручиться поддержкой на краудфандинговой площадке, найти бесплатные места для посещения и немного поработать за еду и койко-место. Такие успехи сопутствуют лишь прожжённым путешественникам с изрядной долей везения. Хотя чего таить, даже им приходится искать пару монет на звонок, чтобы отчитаться маме о своём здравии. Поэтому любое перемещение за границы места проживания сопровождается предварительным планированием и составлением сметы. И дабы вам не пришлось марать листки бумаги и без конца крутить-вертеть цифры, предлагаем обратиться за помощью к калькулятору поездки.
Помимо самого расчёта, шаблон умеет отображать предполагаемые траты в виде круговой диаграммы. Разбиение денежек на категории в процентном отношении даст понять, какая статья расходов наиболее прожорлива.
3. Инвентарный список
Что объединяет пожар, потоп, кражу и приезд родни погостить «на недельку»? Правильно, высокая вероятность утраты целостности вашего имущества.
Уж такова человеческая натура, что жаба душит не за сам факт утери бабушкиных носков, а за то, что вы вообще не можете припомнить весь свой накопленный скарб. При таких неблагоприятных раскладах вас выручит опись домашней утвари. А в дополнение к ней не лишним будет и сфотографировать свои хоромы со всем содержимым.
Отставив шутки, можно посоветовать шаблон тем людям, которые сдают жильё. Принимая постояльцев на долгий срок заселения, не забудьте ознакомить их под роспись с актом проведённой инвентаризации. Он сослужит вам добрую службу при выселении арендаторов.
4. Список контактов
Как бы ни старался технический прогресс, но он не в силах победить «динозавров», не желающих знать о существовании удобных инструментов организации ваших контактов. Дневники, записные книжки и просто клочки бумаги — их всё. Обычно в таких случаях говорят, что горбатого могила исправит (привет, жена!). Но не будем опускать руки и найдём компромиссный вариант — блокнот.
Электронный лист ваших знакомств хорош как минимум по двум причинам: им легко делиться и просто сортировать силами Excel. Поэтому не лишним будет заиметь его резервный вариант даже тем, кто всецело доверяет Google Contacts.
5. Диаграмма Ганта
Добрая русская традиция ударить по рукам, прогулять аванс, расслабиться и выполнить работу в ночь перед днём отчётности — довольно рискованная бизнес-техника, существенно влияющая на качество. Только планирование, разбиение задач на этапы и твёрдое следование графику способно спасти вашу репутацию.
Диаграмма Ганта — это популярный тип столбчатых диаграмм (гистограмм), который используется для иллюстрации плана, графика работ по какому-либо проекту.
Разумеется, мощности Excel позволяют создавать эти самые диаграммы. Их конёк — наглядность и доступность.
И даже если у вас нет собственного дела, попробуйте спланировать ремонт в квартире, подготовку к поступлению или марафону по методике Ганта. Вы оцените силу инструмента.
6. Семейное древо
Венец свадебного торжества — мордобой — пойдёт по правильному сценарию лишь в том случае, если вы чётко разделяете противоборствующие стороны на «своих» и «чужих». А помешать вам разобраться в ситуации может не только алкоголь, но и банальное незнание своей родни.
Конечно, повод для создания генеалогического древа выбран не самый лучший, есть куда более веская причина. Если шаблон покажется вам непонятным, переключитесь на лист Example, где представлено семейное древо Кеннеди.
7. График дежурств
«Провалы в памяти» сопровождают человечество с тех пор, как появилось распределение обязанностей. Недуг особо характерен для детского возраста. Именно малявки чаще своих мам и пап забывают помыть посуду, убрать игрушки и вынести мусор. Болезнь лечится без применения медицинских препаратов: достаточно распечатать недельный график работ и написать под ним предполагаемые карательные меры.
Впишите имена домочадцев в ячейки шаблона, распределив работы по дням недели до и после полудня. И не забудьте повесить распечатку на самое популярное место в квартире — холодильник. Теперь никто не сошлётся на свою забывчивость.
8. Журнал техобслуживания
Однажды, после долгих лет эксплуатации автомобиля, у вас может сложиться впечатление, что в машине не осталось ни одной родной детали. Кроме дворника, естественно, — он свят и неприкосновенен. Так ли это на самом деле? Ответ может быть получен лишь в том случае, если при покупке авто вы завели себе привычку записывать каждый акт его технического обслуживания в специальный журнал. В дополнение шаблон предоставляет возможность подсчитать общую сумму трат, связанных с ремонтом транспортного средства.
9. Журнал пробега
Родись Илон Маск на просторах советского пространства, мы бы с вами уже сейчас катались на электромашинах с низким уровнем затрат на проезд. Хотя кого я обманываю, не случилось бы такого. Илон разбил бы лоб о стену бюрократии и давно спился.
Поэтому владельцы авто по старинке прокручивают перед глазами утекающие доллары под мелькание цифр бензоколонки. Но природа человека склонна быстро забывать всё плохое, оберегая вас от мыслей о мыле и верёвке. Конечно, я утрирую, но почему бы не узнать, сколько денег вы тратите на постоянные дозаправки? А сделать это легко при помощи журнала пробега.
Внесите в форму показания одометра, количество залитых литров и их стоимость, и вы оцените стоимость одного километра пробега. Схожая функциональность реализована и в мобильных приложениях, например Car Logbook для Android.
10. Ежедневник
Только у членов общества, живущих по принципу «с утра выпил — день свободен», перечень дел заканчивается с открытием близлежащего магазина. Остальным же приходится крутиться порой не хуже, чем белке в колесе, упорно укладываясь в ограниченные временные рамки. И дабы не забыть в суматохе свой список планов, люди предпочитают фиксировать его в ежедневниках. Предложенный шаблон хорош тем, что позволяет разбивать каждый трудовой час на 15-минутные отрезки.