Создаем систему учета в Excel: клиенты, сотрудники и зарплата

Иван Корнев·10.04.2026·⏱5 мин

Чтобы создать единую систему учета в Excel, необходимо разбить данные на три отдельных листа: «Клиенты», «Сотрудники» и «Зарплата», а затем связать их с помощью формул поиска (ВПР). Это позволит автоматически подтягивать оклады при расчете зарплаты и анализировать эффективность менеджеров по сделкам. Ниже приведена подробная инструкция по настройке структуры, формул и автоматизации процесса за 30 минут.

Подготовка структуры файлов

Не пытайтесь уместить все данные на один лист. Разделение информации обеспечивает чистоту данных и упрощает работу формул.

Откройте новую книгу Excel.
Переименуйте три нижних вкладки (листа) следующим образом:
- Клиенты — база контактов и сделок.
- Сотрудники — штатное расписание и ставки.
- Зарплата — ежемесячный расчет выплат.

Назовите диапазоны данных как «Умные таблицы» (выделите данные и нажмите Ctrl+T). Это позволит формулам автоматически расширяться при добавлении новых строк.

Лист 1: База клиентов (CRM)

Этот лист служит реестром всех взаимодействий. Здесь хранятся контакты и финансовые результаты работы с каждым контрагентом.

Структура столбцов (строка 1):

A: ID (уникальный номер)
B: ФИО / Название компании
C: Телефон
D: Email
E: Дата первой сделки
F: Общая сумма сделок (руб.)
G: Статус (Активный, Потенциальный, Архив)
H: Ответственный менеджер (ФИО)

Настройка:

В ячейку A2 введите формулу для автогенерации номера: =СТРОКА()-1. Протяните её вниз.
Для столбца F (Сумма) установите числовой формат с разделителями тысяч.
Для столбца G используйте выпадающий список: Данные → Проверка данных → Тип данных: Список. В источник впишите: Активный;Потенциальный;Архив.

Избегайте объединения ячеек в таблицах с данными. Это ломает сортировку и работу формул ВПР.

Лист 2: Штат сотрудников

Здесь фиксируются трудовые договоры, ставки и отработанное время. Эти данные будут источником для расчета зарплаты.

Структура столбцов (строка 1):

A: Табельный номер (ID)
B: ФИО сотрудника
C: Должность
D: Часовая ставка (руб.)
E: Норма часов в месяц
F: Оклад (расчетный)
G: Дата приема на работу

Автоматизация оклада: В ячейку F2 введите формулу расчета базового оклада: =D2*E2 (Часовая ставка × Норма часов). Протяните формулу на весь столбец.

Для защиты персональных данных (согласно ФЗ-152) не храните в этом файле паспортные данные, адреса и ИНН. Используйте только ФИО и табельный номер.

Лист 3: Расчет зарплаты

Это итоговый лист, где происходит сведение данных. Мы будем использовать функцию ВПР (VLOOKUP) для подтягивания информации из листа «Сотрудники».

Структура столбцов:

A: Месяц расчета
B: Табельный номер (вводится вручную или выбирается из списка)
C: ФИО (авто)
D: Оклад (авто)
E: Премия (вводится вручную)
F: НДФЛ 13% (авто)
G: К выплате (авто)

Формулы для автоматизации:

Поиск ФИО (ячейка C2): =ВПР(B2; Сотрудники!$A:$G; 2; 0) Ищет номер из столбца B в листе "Сотрудники" и возвращает имя из 2-го столбца.
Поиск Оклада (ячейка D2): =ВПР(B2; Сотрудники!$A:$G; 6; 0) Возвращает значение оклада из 6-го столбца листа "Сотрудники".
Расчет НДФЛ (ячейка F2): =(D2+E2)*0,13 Налог берется от суммы оклада и премии.
Итого на руки (ячейка G2): =(D2+E2)-F2

Протяните все формулы вниз. Теперь при изменении ставки сотрудника в листе «Сотрудники», она автоматически обновится в расчете зарплаты.

Сравнение методов ввода данных

Метод	Плюсы	Минусы	Когда использовать
Ручной ввод	Полный контроль	Высокий риск опечаток	Малое кол-во сотрудников (<5)
Выпадающий список	Стандартизация ввода	Требует настройки	Для выбора статусов и должностей
Формула ВПР	Автоматическая связь листов	Ошибки при изменении структуры	Для связи зарплаты и штата

Аналитика и сводные отчеты

Чтобы увидеть, кто из сотрудников приносит больше всего денег, свяжите листы «Клиенты» и «Зарплата».

Добавьте в лист «Зарплата» столбец «Выручка менеджера» и используйте формулу СУММЕСЛИ: =СУММЕСЛИ(Клиенты!$H:$H; C2; Клиенты!$F:$F) Где:

Клиенты!$H:$H — столбец с фамилиями менеджеров в базе клиентов.
C2 — ФИО сотрудника в текущей строке расчета зарплаты.
Клиенты!$F:$F — столбец с суммами сделок.

Для создания быстрого отчета выделите всю таблицу зарплаты и нажмите Вставка → Сводная таблица.

В строки перетащите поле ФИО.
В значения перетащите К выплате и Выручка менеджера. Вы мгновенно получите таблицу эффективности: сколько компания заплатила сотруднику и сколько он принес прибыли.

Частые ошибки при создании таблиц

#Н/Д в формулах ВПР. Возникает, если искомое значение (например, табельный номер) не найдено во втором листе. Проверьте, нет ли лишних пробелов в номерах.
Сбитые ссылки. Если вы вставляете новые столбцы в середину таблицы «Сотрудники», формулы ВПР могут начать тянуть неверные данные. Всегда проверяйте номер столбца в формуле после изменений структуры.
Отсутствие абсолютных ссылок. При копировании формул убедитесь, что диапазоны поиска зафиксированы знаками доллара (например, $A:$G), если вы не используете умные таблицы.

FAQ

Можно ли рассчитать отпускные в этой таблице? Да, добавьте столбец «Тип выплаты» и используйте функцию ЕСЛИ: если тип «Отпуск», берите средний заработок, иначе — оклад. Однако для сложного кадрового учета лучше использовать специализированное ПО.

Как защитить файл от изменений формул? Выделите ячейки, которые можно менять (ввод данных), нажмите правой кнопкой → Формат ячеек → Снимите галочку «Защищаемая ячейка». Затем перейдите на вкладку «Рецензирование» → «Защитить лист». Теперь пользователи смогут менять только разрешенные поля.

Что делать, если сотрудников больше 100? Excel начнет тормозить при большом количестве формул массива. В таком случае рассмотрите переход на Google Таблицы для совместной работы или использование надстройки Power Query для обработки данных.