Бесплатные шаблоны для управления проектами в Excel

В любом из трех Word-ов можно выделить нужное количество ячеек, подведя курсор к верхней или левой границе таблицы до появления черной стрелки. Также, по нажатию кнопки «Скрыть сетку» исчезнут линии ячеек и столбцов.

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

С помощью ластика можно стирать отдельные линии в таблице.

Поставив курсор в нужной ячейке и нажав на «Заливка», можно заменить цвет ячейки на подходящий.

Вот мы и завершили знакомство с таблицами в Microsoft Office Word 2003, 2007 и 2010. Надеюсь, что данный материал вам помог.

Что такое шаблоны Эксель

Шаблоны в Microsoft Excel – это особый вид рабочих листов и книг, для которых заданы стили и оформление. Открывая такой объект, вы можете сразу заполнять его данными, уделяя меньше внимания форматированию. Таким образом, вы поддерживаете единый стиль своих документов и экономите огромное количество времени. Применение шаблонов позволяет концентрироваться на качественной составляющей расчетов, без постоянного однотипного оформления.

Чтобы было понятнее, представьте какой-нибудь бланк. Например, отчета о полученной прибыли. Сам по себе — это всего лишь пустой лист с разметкой. Он ничего не значит и ни на что не влияет. Это и есть шаблон в Эксель. Но как только Вы в очередной раз внесли в него информацию — это уже документ. Бланк сам по себе практически никогда не изменяется, а данные в нем всегда разные. Так зачем каждый раз рисовать бланк и заполнять его данными? Думаю, такое сравнение Вам понятно.

Нужно понимать, шаблон – это не рабочая книга в том понятии, к которому вы привыкли. Это только информация о разметке листа и исходных данных. Обратите внимание:

  • Когда мы загружаем шаблон, программа создаёт новую рабочую книгу с форматированием, как в шаблоне
  • А когда мы открываем шаблон – можно менять его оформление и содержание для последующей загрузки в рабочую книгу и заполнения данными.

Использование форм Excel для быстрого расчета бизнес-плана

Таблицы Excel входят в стандартный пакет MS Office. Она по методу расчетов относится к типу закрытых программ, то есть пользователь не наблюдает за ходом расчетов, а видит только их результат.

Excel популярна, поскольку имеет простой интерфейс и включает ряд самых полезных инструментов, которые упрощают расчеты и повышают их результативность.

Назначение шаблона вводится в поиске, и программа подбирает варианты форм под ваш запрос.

В Excel можно выполнять расчеты двумя методами: воспользовавшись встроенными формами или создавая свои собственные.

Применение встроенных форм использовать в работе проще всего. Такие формы запускают, добавив соответствующий значок на панель быстрого доступа. Нужно лишь ввести свои данные. Остальное сделают формулы.

Пользовательскую форму создают самостоятельно с помощью макросов (программных кодов, которые описывают алгоритм ваших действий в документе). Преимущество в том, что вы получаете гибкий, рассчитанный именно на ваши нужды, инструмент.

Однотипные, рутинные действия (подготовка таблиц и макетов, очистка и заполнение граф) и расчеты в Excel можно объединить в макрокоманду, которая помещается на панель инструментов в виде кнопки. Нажатие на такую кнопку вызывает автоматическое выполнение предусмотренных действий, что экономит время и делает расчеты не столь трудоемкими.

Такие шаблоны ускоряют финансовые расчеты по:

выходу на точку безубыточности и др.

Учет договоров в Excel с напоминаниями

Чтобы в будущем можно было пользоваться фильтрами, вводимые данные необходимо нормировать. Например, чтобы в одной строке не ввели ООО «Сириус», а в другой — Сириус ООО. В 1С это делается справочниками, в Excel есть функция — Проверка данных.
Заводим листы с нашей нормативно-справочной информацией:
Виды договоров (с покупателем, поставщиком, прочее)
Контрагенты (здесь будут заноситься наши контрагенты)
Далее выделяем столбец D, идем в меню Данные — Проверка данных. На вкладке параметры выбираем Тип данных — Список, указываем диапазон-источник (лист Виды договоров).
Проверка данных ExcelПроверка данных Excel
Аналогично поступаем с контрагентами, проверяем, что выбрать можно только имеющийся элемент:
Выбор значения из выпадающего списка ExcelВыбор значения из выпадающего списка Excel

Цветовое выделение просроченных договоров

Здесь на помощь приходит Условное форматирование. Выделяем наш реестр договоров целиком, идем в раздел Условное форматирование — Управление правилами.
Условное форматированиеУсловное форматирование
Создаем новое правило, выбираем тип правила как на рисунке.
Условие выделения цветом просроченных договоровУсловие выделения цветом просроченных договоров
Указываем диапазон (весь регистр договоров), задаем формулу

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

Текущий статус договора

Идем в ячейку А2 и вводим формулу:

Работает по такому же принципу, если дата окончания договора больше текущей хоть на 1 день, выводится сообщение «Договор истек».
Копируем формулу на другие ячейки столбца А, вводим данные, убеждаемся, что все работает.
Учет договоров в Excel с напоминаниямиУчет договоров в Excel с напоминаниями
Итак, просроченные договоры у нас будут подсвечиваться кирпичным цветом, в первом столбце будет выводится надпись «Договор истек«.

Получившийся файл можно скачать по ссылке.

Учет договоров в 1С

Для этой цели подойдут различные конфигурации, приведем пример самой распространенной и специализированной

1С:Бухгалтерия 8

Если Вы уже пользуетесь 1С:Бухгалтерия 8, справочник «Договоры» прекрасно справится с функцией системы учета договоров.

  • прикреплять сканы,
  • печатать договор по шаблону,
  • контролировать возврат оригиналов.

1С:Договоры 8

Отдельный специализированный продукт, подробное описание и стоимость можно посмотреть здесь

Если статья оказалась полезной, поддержите авторов, оставляйте комментарии, поделитесь ссылкой с друзьями в соц. сетях.
Поделиться в соцсетяхПоделиться в соцсетях

Применение инструментов заполнения

Форма заполнения представляет собой объект с полями, наименования которых соответствуют названиям колонок столбцов заполняемой таблицы. В эти поля нужно вводить данные и они тут же будут добавляться новой строкой в табличный диапазон. Форма может выступать как в виде отдельного встроенного инструмента Excel, так и располагаться непосредственно на листе в виде его диапазона, если она создана самим пользователем.

Теперь давайте рассмотрим, как пользоваться этими двумя видами инструментов.

Способ 1: встроенный объект для ввода данных Excel

Прежде всего, давайте узнаем, как применять встроенную форму для ввода данных Excel.

    Нужно отметить, что по умолчанию значок, который её запускает, скрыт и его нужно активировать. Для этого переходим во вкладку «Файл», а затем щелкаем по пункту «Параметры».

Переход в параметры в Microsoft Excel

Добавление инструмента форма на панель быстрого доступа в Microsoft Excel

Инструмент форма добавлен на панель быстрого доступа в Microsoft Excel

Инструмент форма отображается на панеле быстрого доступа в Microsoft Excel

Шапка таблицы в Microsoft Excel

первое значение в таблице в Microsoft Excel

Запуск формы в Microsoft Excel

Форма открыта в Microsoft Excel

Ввод значений в форму в Microsoft Excel

Значения перенесы в таблицу в Microsoft Excel

Добавление второй строки в таблицу через форму в Microsoft Excel

Вторая строка заполнена в таблице в Microsoft Excel

Все значения в таблицу введены в Microsoft Excel

Навигация по форме в Microsoft Excel

Корректировка данных в форме в Microsoft Excel

Изменение произведено в таблице в Microsoft Excel

Удаление строки через форму в Microsoft Excel

Подтверждение удаления строки в Microsoft Excel

Закрытие формы в Microsoft Excel

Таблица отформатированв в Microsoft Excel

Способ 2: создание пользовательской формы

Кроме того, с помощью макроса и ряда других инструментов существует возможность создать собственную пользовательскую форму для заполнения табличной области. Она будет создаваться прямо на листе, и представлять собой её диапазон. С помощью данного инструмента пользователь сам сможет реализовать те возможности, которые считает нужными. По функционалу он практически ни в чем не будет уступать встроенному аналогу Excel, а кое в чем, возможно, превосходить его. Единственный недостаток состоит в том, что для каждого табличного массива придется составлять отдельную форму, а не применять один и тот же шаблон, как это возможно при использовании стандартного варианта.

    Как и в предыдущем способе, прежде всего, нужно составить шапку будущей таблицы на листе. Она будет состоять из пяти ячеек с именами: «№ п/п», «Наименование товара», «Количество», «Цена», «Сумма».

Шапка таблицы создана в Microsoft Excel

Создание умной таблицы в Microsoft Excel

Окошко форматирования таблицы в Microsoft Excel

Отключение фильтра через вкладку Данные в Microsoft Excel

Отключение фильтра через вкладку Главная в Microsoft Excel

Фильтр снят в Microsoft Excel

Форма ввода данных на листе в Microsoft Excel

Таблица со списком товаров в Microsoft Excel

Переход к проверке данных в Microsoft Excel

Окно проверки вводимых значений в Microsoft Excel

Выбор списка в окне проверки вводимых значений в Microsoft Excel

Переход к выбору источника в окне проверки вводимых значений в Microsoft Excel

Адрес диапазона внесен в поле в Microsoft Excel

Закрытие окна проверки вводимых значений в Microsoft Excel

Список со значениями в Microsoft Excel

Выбранная позиция отобразилась в ячейке в Microsoft Excel

Наименование первой ячейки в Microsoft Excel

Наименование второй ячейки в Microsoft Excel

Наименование третьей ячейки в Microsoft Excel

присвоение наименования диапазону в Microsoft Excel

Сохранение книги в Microsoft Excel

Окно сохранения файла в Microsoft Excel

Переход в редактор макросов в Microsoft Excel

Редактор макросов в Microsoft Excel

В поле «Name» тоже можно заменить название на более удобное. Но это не обязательно. При этом допускается использование пробелов, кириллицы и любых других знаков. В отличие от предыдущего параметра, который задает наименование листа для программы, данный параметр присваивает название листу, видимое пользователю на панели ярлыков.

Изменение наименования листа в редакторе макросов в Microsoft Excel

Поле в редкторе макросов отобразилось в Microsoft Excel

Sub DataEntryForm()
Dim nextRow As Long
nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
With Producty
If .Range(«A2»).Value = «» And .Range(«B2»).Value = «» Then
nextRow = nextRow — 1
End If
Producty.Range(«Name»).Copy
.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range(«Volum»).Value
.Cells(nextRow, 4).Value = Producty.Range(«Price»).Value
.Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value
.Range(«A2»).Formula = «=IF(ISBLANK(B2), «»»», COUNTA($B$2:B2))»
If nextRow > 2 Then
Range(«A2»).Select
Selection.AutoFill Destination:=Range(«A2:A» & nextRow)
Range(«A2:A» & nextRow).Select
End If
.Range(«Diapason»).ClearContents
End With
End Sub

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

Итак, первая строка:

«DataEntryForm» — это название самого макроса. Вы можете оставить его как есть, а можете заменить на любое другое, которое соответствует общим правилам создания наименований макросов (отсутствие пробелов, использование только букв латинского алфавита и т.д.). Изменение наименования ни на что не повлияет.

Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.

Наименование для листа в Microsoft Excel

Теперь рассмотрим такую строку:

nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара». По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.

Второй столбец таблицы в Microsoft Excel

Далее рассмотрим строку

If .Range(«A2»).Value = «» And .Range(«B2»).Value = «» Then

«A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара»). Если они у вас отличаются, то введите вместо этих координат свои данные.

Две первые ячейки таблицы с данными в Microsoft Excel

Переходим к строке

В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.

Имя поля наименования товара в форме ввода в Microsoft Excel

.Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
.Cells(nextRow, 3).Value = Producty.Range(«Volum»).Value
.Cells(nextRow, 4).Value = Producty.Range(«Price»).Value
.Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value

наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

Наименование полей количество и цена в Microsoft Excel

В этих же строках, которые мы указали выше, цифры «2», «3», «4», «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара», «Количество», «Цена» и «Сумма». Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.

Колонки в таблице в Microsoft Excel

В строке производится умножение количества товара на его цену:

.Cells(nextRow, 5).Value = Producty.Range(«Volum»).Value * Producty.Range(«Price»).Value

Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.

Колонка для вывода суммы в Microsoft Excel

В этом выражении выполняется автоматическая нумерация строк:

If nextRow > 2 Then
Range(«A2»).Select
Selection.AutoFill Destination:=Range(«A2:A» & nextRow)
Range(«A2:A» & nextRow).Select
End If

Все значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты « — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.

Столбец с нумерацией в Microsoft Excel

В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:

Не трудно догадаться, что («Diapason») означает наименование того диапазона, который мы ранее присвоили полям для ввода данных. Если вы дали им другое наименование, то в этой строке должно быть вставлено именно оно.

Наименование полей для ввода данных в Microsoft Excel

Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.

Код в редакторе макросов в Microsoft Excel

Выбор элемента управления в Microsoft Excel

Указание границ кнопки в Microsoft Excel

Окно назначения макроса объекту в Microsoft Excel

Переименовывание кнопки в Microsoft Excel

Кнопка переименована в Microsoft Excel

Ввод данных в форму в Microsoft Excel

Значения первой строки внесены в таблицу в Microsoft Excel

Повторный ввод данных в форму в Microsoft Excel

Вторая строка добавлена в таблицу в Microsoft Excel

В Экселе существует два способа применения формы заполнения данными: встроенная и пользовательская. Применение встроенного варианта требует минимум усилий от пользователя. Его всегда можно запустить, добавив соответствующий значок на панель быстрого доступа. Пользовательскую форму нужно создавать самому, но если вы хорошо разбираетесь в коде VBA, то сможете сделать этот инструмент максимально гибким и подходящим под ваши нужды.

Читайте также:  Как присвоить имя значению Excel

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12049 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Создаем приложение за 8 шагов

Учет клиентов, распределение задач между сотрудниками, управление сделками – задачи, знакомые любому предпринимателю. Если свести их в единую систему, получится CRM . На рынке много готовых приложений, в том числе бесплатных – бери и пользуйся. А если нужен полный контроль над бизнес-процессами и гарантия внедрения, можно заказать интеграцию CRM с сайтом у профессионалов.

Но наша цель сейчас – просто изучить работу с AppSheet, поэтому попробуем самостоятельно создать небольшую кросс-платформенную CRM-систему. Она должна использовать данные из таблиц Excel или Google, добавлять в них новых клиентов, заполнять и редактировать информацию, ставить задачи сотрудникам и помогать контролировать дедлайны.

Шаг 1: создаем данные

Для работы нашему приложению необходимы данные, поэтому начнем с создания таблицы с заголовками, используя для этого «Google Таблицы». Советую давать листам осмысленные названия, , чтобы в процессе работы в них не запутаться.

Какого рода должны быть данные? На самом деле любые. Давайте представим,что у нас B2B компания, которая продает свои услуги другим предприятиям. Поэтому создадим для нашей CRM таблицу с названием компаний-клиентов, адресами их сайтов, e-mail и ссылками на чаты.

Наша база компаний для CRM

В дальнейшем нам понадобятся еще данные. Чтобы не запутаться, будем создавать их не сразу, а по мере необходимости.

Шаг 2: создаем приложение

Заходим на официальный сайт AppSheet. Регистрируемся и нажимаем Start for free. После того, как выберете подходящее облачное хранилище, открывается выбор из трех категорий приложений. Вкладка Start with your own data позволит создать собственное приложение с нуля. На нее и жмем.

Интерес также представляет вкладка Start with sample app. Она позволяет использовать готовые приложения или просто заглянуть под капот некоторых интересующих вас фич.

На странице выбора данных находим только что созданный файл. Готово! Теперь мы попадаем на панель разработки, где видно, как будет выглядеть наше мобильное приложение.

Наше приложение будет выглядеть так, как показано справа

Обратите внимание на кнопки View и Data. Если их использовать при настройке данных и видов, можно переключаться быстрее. Этот маленький лайфхак сбережет вам кучу времени.

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

Переключайтесь быстрее, используя кнопки View и Table. А с помощью кнопок в верхнем правом углу можете оценить, как выглядит приложение на других устройствах

Шаг 3: разбираемся с ключами

Чтобы работать с AppSheet, нужно понимать принцип работы с ключами базы данных. Если попробовать отредактировать информацию о компании, мы увидим интересную особенность – название нельзя поменять, потому что оно является уникальным ключом. А что если компаний много и в название случайно закралась ошибка?

В этом случае требуется уникальный, неповторяющийся ключ.

Сейчас название компании – это ключ, но лучше ключом делать уникальное значение

Теперь добавим в базу компаний колонку с уникальными ID. Для этого нужно зайти в нашу Google-таблицу и вставить новый столбец с ID.

Создаем новую колонку ID

Далее заполняем уникальными случайными знаками те строчки, которые уже созданы. Это можно сделать двумя способами:

  • Если значений мало, можно просто присвоить случайные значения нашим ID.
  • Если много – использовать генераторы паролей. Однако, массово копировать их не всегда удобно.

В итоге должна получиться такая колонка:

Создаем новую колонку и размещаем там ID

Но это не все – сейчас программа выдает ошибку. Помните, мы обсуждали, что при добавлении новых данных, приложение ломается? Дело в том, что добавив в Google-таблицу новую колонку, вы не сообщили об этом приложению. Это можно сделать, нажав Data – Column – Regenerate Structure.

Нажмите Regenerate Structure, чтобы обновить данные в вашем приложении

После этого должна появиться новая вкладка ID.

Вкладка ID появилась, теперь настроим ее

Теперь отмечаем новую строчку ID как ключ, а значение Label оставляем там же. Label – это этикетка. Этот параметр помогает распознать название ключа. В данном случае наша этикетка – название компании.

Настраиваем ключ и этикетку, как показано на скриншоте

Раскрываем строку ID и добавляем формулу UNIQUEID() в строчку Initial value. Если этого не сделать при добавлении новой компании, значение ID будет пустым.

Добавляем формулу UNIQUEID() в строчку «Initial value»

Не забываем скрыть ID. Это техническая информация, ее не нужно отображать пользователям. Для этого убираем флажок с Show. Пробуем ввести новую компанию – теперь название можно редактировать.

После присвоения ID появится возможность менять названия

Сейчас в нашей базе автоматически сформирован новый ID. Механизм создания ключей всегда одинаков, а сами ключи нужны практически везде.

После того, как вы вводите информацию в приложение, она автоматически появляется в таблицах

Шаг 4: добавляем возможность ставить задачи

Иногда необходимо контролировать выполнение задач. Это удобно делать в файле с базой компаний-клиентов.

Создадим специальный раздел в нашем приложении. Для этого нам понадобится еще одна таблица с данными.

Создаем таблицу с задачами. Также создаем ID, присваиваем дедлайн

Таблица создана. Разумеется, в ней могут быть любые разделы.

Теперь разберемся, в чем разница между ID задачи и обычным ID. Важно понимать, что ID задачи – это уникальное значение, которое отличает одну задачу от другой и не путать с просто ID, который нужен для присвоения задачи конкретному клиенту. В нашем случае это компания.

Присоединяем данные в приложение. Для этого заходим в раздел Data и жмем Add new table.

Присоединяем новую таблицу с помощью клавиши Add New Table

Выбираем нашу новую таблицу.

Подключаем новую таблицу с задачами

После обновления появится новый раздел.

Появился новый раздел, теперь его можно настроить

Открываем вновь созданный раздел с задачами и настраиваем его.

Настраиваем раздел задач

Как видно из рисунка выше, на страничке компании ничего не изменилось и связи с задачами не возникло. Нужно настроить. Для этого перейдем в настройки строчки ID, в поле Type меняем тип на Ref, а в Source table выбираем «База компаний».

Обязательно выставляем данные значения, чтобы появилась связь между таблицами

Теперь у нас появилась возможность ставить задачи по конкретной компании.

Теперь на страничке компании можно ставить задачи

Настроим корректное отображение. Сейчас вкладка с задачами представляет собой набор непонятных символов.

Если отображение данных вас не устраивает, настройте его

Возвращаемся во вкладку «Задачи» и меняем следующие пункты:

  • Переставляем Label на раздел «Задача».
  • Скрываем при необходимости вкладку «Дата постановки задачи», она будет заполняться сама.
  • Скрываем вкладку «ID задачи», это техническая информация. Приложение само распознало колонку «ID задачи»» как колонку с ключами, поэтому прописывать ничего не нужно. Стоит лишь проверить, чтобы в поле Initial value была формула UNIQUEID(). Если ее нет, то пропишите.
  • Заходим в настройки колонки ID, находим раздел DISPLAY. В Display name прописываем имя строки. В данном случае, это «Компания».

Результат настроек – выглядит уже лучше

На страничке компаний задачи тоже приняли понятный вид:

Страница компаний тоже изменилась – теперь интерфейс понятен

Шаг 5: добавляем сотрудников

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

Создадим новую таблицу «Сотрудники»

Добавляем таблицу в приложение. В данном случае ID – это имя сотрудника. Если имена повторяются, присваиваем каждому свой ID.

Настраиваем типы данных с помощью раздела Type

Добавляем связь между сотрудниками и задачами. Для этого переходим в таблицу с задачами и добавляем новую колонку с сотрудниками.

Добавляем колонку с сотрудниками, чтобы закрепить задачу за конкретным лицом

Обновляем таблицу в приложении.

Жмем Regenerate Structure, чтобы таблица с задачами обновилась

В таблице «Задачи» меняем тип данных в колонке «Сотрудник» на ref и ссылаемся на таблицу с сотрудниками:

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

Теперь у нас появилась необходимая связь.

Теперь на страничке с задачами появился сотрудник, за которым закреплена задача

Часто возникает такая проблема, что вроде бы все настроил, а связь не работает.

Часто связь не появляется

В таком случае необходимо обновить все таблицы с помощью кнопок Regenerate Structure. Если это не помогло, проблема, скорее всего, в заголовках. При ссылках заголовки в таблицах должны быть одинаковыми.

Обновляем данные с помощью Regenerate Structure во всех таблицах, чтобы решить проблему со связями

Далее обновляем таблицы «Задачи» и «Сотрудники» в приложении с помощью клавиши Regenerate Structure. Теперь задача присвоена конкретному сотруднику.

Следом сделаем так, чтобы каждый сотрудник видел только свои задачи. Переходим Data – User Settings. Открываем любой раздел, например, Option 1.

Чтобы сотрудники видели только необходимые им задачи, жмем на значок «Редактировать»

Обязательно включаем раздел Show?, делаем type «Enum» и прописываем ссылку «Сотрудники[сотрудник]».

Чтобы вход в приложение для сотрудников корректно работал, настроите так же, как показано на скриншоте

Теперь в приложении появилась возможность зайти как сотрудник.

Теперь в приложении появилась возможность зайти как сотрудник

Мы почти у цели. Переходим Data – Table – Таблица «Задачи» – Security – Security filter. В строке вставляем формулу: «[Сотрудник] = USERSETTINGS(Пользователь)».

Добавим формулу в Security filter, чтобы настроить отображения данных для конкретных пользователей

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

Теперь высвечиваются только задачи, которые присвоены мне

Шаг 6: настраиваем пользовательский интерфейс

Теперь надо, чтобы все выглядело красиво. Для этого используем вкладку UX. Переходим UX – VIEWS. Выбираем раздел «База компаний».

Настраиваем отображение базы компаний

Убираем лишние кнопки из данного вида. Для этого находим строчку Column order и меняем порядок расположения элементов так, как нам нужно.

Настраиваем порядок отображения элементов с помощью Column order

Чтобы поменять иконки, нужно в DISPLAY найти раздел icons, а там уже можно выбрать необходимую иконку. Точно так же настраиваем вид «Задачи».

Добавим еще один вид – «Сотрудники». Для этого выбираем тип Desk, а в самом приложении подгружаем фотографии сотрудников.

Настраиваем отображения данных наших сотрудников

Для того, чтобы настроить необходимые кнопки, заходим в раздел Action.

Настраиваем кнопки с помощью раздела Action

Иконки меняем в Display, в разделе icons.

Теперь сделаем так, чтобы выполненные задачи скрывались и, если сегодня дедлайн, высвечивалось бы напоминания. Для этого переходим в Data – Slice и жмем кнопку Add New Slice.

Добавляем Slice, чтобы выполненные задачи скрывались

Всего у задач три статуса: активная, задача завершена или отложена. Давайте сделаем так, чтобы завершенные задачи скрывались из отображения.

Прописываем формулу для нового Slice

Прописываем формулу: OR([Статус задачи] = «Активная», [Статус задачи]=»Отложена»). После переходим в UX во вкладку Views. Там находим вкладку Задачи. Подменим данные в вкладке For this data с «Задачи» на только что созданный Slice «Актуальные задачи».

Обязательно меняем данные в виде «Задачи» на только что созданный Slice «Актуальные задачи»

Теперь выполненную задачу можно легко скрыть, изменив статус.

Теперь задачу легко скрыть

Чтобы активные и отложенные задачи разделялись в табличном виде, сгруппируем их. Для этого перейдем в вкладку Views меню настройки UX. Найдем там раздел Group by и сгруппируем по статусу задачи.

Группируем данные, как показано на скриншоте

Группировка появилась. Теперь добавим выделение задачи, если у них сегодня дедлайн. Для этого переходим UX – Format rules – Add new rules. В строке формулы прописываем: «[Дедлайн]=Today()». Отмечаем колонку задачи, выбираем иконку и цвет.

Прописываем формулу, чтобы форматирование заработало

Шаг 7: русифицируем. Добавляем логотип и название

Теперь осталось русифицировать наше приложение. Для этого переходим UX – Localize, там переводим названия.

Переводим названия для того, чтобы русифицировать наше приложение

Изменим логотип. Для этого переходим UX – Brand. Во вкладке App logo прикрепляем ссылку на свой логотип или выбираем системное лого.

Добавляем логотип с помощью раздела App logo

Теперь меняем название приложения. Это делается в разделе Info – Properties. В поле Short name коротко прописываем название. Не старайтесь делать его длинным, оно не поместится на экране телефона.

Назовите приложение в строке Short Name

Шаг 8: устанавливаем на телефон и смотрим, что у нас получилось

Чтобы установить созданное нами приложение на телефон, скачиваем AppSheet в App Store или Google Play. Заходим в него и видим список всех созданных вами приложений, находим там нашу CRM. Переходим в нее и нажимаем в настройках Add Shortcut. Теперь логотип нашего приложения появился на рабочем столе.

Жмем Add Shortcut, чтобы добавить наше приложение на рабочий стол телефона или планшета

Итак, мы с вами создали свою простейшую CRM для любых устройств на основе «Google Таблиц». Можем добавлять в нее любые компании, отправлять письма, ставить и скрывать выполненные задачи. А если сегодня дедлайн, информация будет выделяться. Все задачи закреплены за сотрудниками, им можно звонить и отправлять СМС.

Основные вкладки выглядят минималистично. Они нужны для того, чтобы быстро находить нужные данные

Подробное представление всегда более информативное

Простой способ ведения базы данных

Можно разработать таблицу в форме, которая будет удобна вам.

Как создать базу клиентов в программе Excel

В лист «Услуги» включать все услуги, предоставляемые вами клиенту, объединять их примерно по 10 видов и переносить в таблицу.

В лист «Мои клиенты» включить всех клиентов, с которыми вы работали. Он включает данные, указанные в таблице.

По количеству первой графы No п/п вы будете видеть, сколько у вас было клиентов.

Во вторую графу можно включать ФИО клиента или наименование организации.

В графе «вид услуги» указывается основной вид заказа, в случае, если их несколько, оставшиеся расписать в графе «Примечание».
В этой графе описываются пожелания или предложения клиента.

Графа «Дата первого заказа» показывает, с какого времени вы с ним сотрудничаете.

Читайте также:  Вторичная круговая диаграмма в Excel и способы ее построения

Графа «Дата последнего заказа» дает возможность проследить, когда был последний заказ.

Перечень можно отсортировать по любому значению. Например, отсортировав по виду услуги, можно определить, какие виды более востребованы.
И заказчикам, покупающим уникальные статьи по копирайтингу, сделать предложение о написании таких статей.

В таблицу можно дополнять и другие графы, если это потребуется.

База данных в excel: расширенный вариант

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

  • каналы привлечения: источники, откуда пришёл тот или иной клиент;
  • клиентский статус: он может быть активным или пассивным, если заказов не было в течении 3-6 месяцев.

В таблицу Excel также есть возможность добавить функциональный столбец «напомнить о себе». Если заданный периодзавершился, а заказчик так и не проявил себя, ячейка в столбце загорится красным цветом. Если клиент ничего не отвечает после индивидуального предложения, его статус можно поменять на пассивный и система больше не будет делать о нём напоминаний.

SpreadsheetZONE имеет несколько страниц бесплатных шаблонов Excel в тоннах категорий.

Вы можете просматривать шаблоны по типу приложения, такие как календари, игры, финансы, обзоры и калькуляторы, или по отраслям, например, ИТ, маркетинг, HR, юридические услуги, страхование, поддержка клиентов и телекоммуникации.

На странице загрузки показан снимок экрана шаблона в действии и краткий обзор того, для чего он предназначен.

Телефонный справочник в Excel готовый шаблон скачать

ООО «Клиентская база»

Попробуйте первые 14 дней бесплатно

Создание шаблона печати Excel

Добавлено: 01 марта 2016 г.

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

Шаблоны печати

В нашей CRM программе вы можете создавать любые шаблоны печати. Например, вы можете выписывать счета, акты о выполненных работах, заполнять типовые договора, пользуясь данными из таблиц. В шаблоне печати задается общий вид документа и отмечается, куда в этом документе нужно вставить значения полей таблицы. Шаблоны печати в системе «Клиентская база» позволяют не только распечатывать документы, но и сохранять их в форматах rtf, odt (OpenOffice), docx (Word 2007), xls, pdf. На этом уроке мы научимся создавать шаблоны в формате xls (при создании шаблонов xls для нашей программы рекомендуем использовать Excel 2007).

Важно: На данный момент значения из подтаблиц могут некорректно отображаться в версии Microsoft Office 2013. Если Вы столкнулись с такой проблемой, попробуйте открыть сформированный файл в другом текстовом редакторе (например, Libre office или Open office).

Создание шаблонов печати на основе документа Excel

1. Сначала необходимо подготовить документ в программе Excel. Разместите в нем необходимый текст и таблицы. Места, куда должны будут подставляться значения из таблиц CRM «Клиентской базы», оставьте пока пустыми. В документе также можно использовать изображения, их необходимо вставить в сам файл Excel, из поля программы изображения подтягиваться не будут.

Важно: В шаблонах печати Excel можно использовать только изображения в формате «png», в противном случае изображения при печати выводиться не будут!

Сохраните документ, но не закрывайте – в дальнейшем нам еще нужно будет его редактировать. Вы также можете скачать шаблон любого Excel документа из интернета. Возьмем, для примера, счет-фактуру нового формата.

photo

2. Теперь в программе «Клиентская база» перейдем в «Настройки» — «Конфигурация».

3. Выберите категорию «Документы», затем перейдите в таблицу «Счет-фактура». Перед вами отобразится окно с настройками, здесь нажмите на кнопку «Шаблоны печати».

photo

4. Вы увидите список уже имеющихся шаблонов. Нажмите на кнопку «Добавить шаблон».

5. В окне «Создание шаблона» сначала нужно ввести имя шаблона. Затем выбрать формат шаблона. Выбираем «xls».

photo

6. Далее необходимо выбрать файл, с которым мы будем работать. Нажимаем «Загрузить».

7. Итак, файл загружен. Мы видим, что появился список полей, которые мы можем использовать в шаблоне.

photo

Теперь нужно в нашем файле Excel разместить шаблоны полей в тех местах, куда должны подставляться значения из базы. Для этого в окне «Создание шаблона» есть список всех полей, которые можно разместить в шаблоне. Копируем из списка название поля целиком, включая фигурные скобки, и вставляем в Excel файл в нужное место.

photo

Обратите внимание: вы можете использовать поля не только из текущей таблицы, например <Номер>, но и любые поля из связанных таблиц — они пишутся через точку, например, <На кого.Название>.

Задайте ячейкам нужный формат, чтобы Excel корректно отображал данные. Например, ячейкам, в которых должен выводиться ИНН или КПП, лучше задать текстовый формат, т.к. Excel может воспринять номер ИНН как слишком большое число и выводить его некорректно.

8. После того, как мы разместили в xls файл все нужные поля, сохраняем его. Шаблон готов. Теперь нужно подгрузить заново готовый шаблон. Опять нажимаем «Выбрать файл», выбираем наш xls-файл, нажимаем «Загрузить». Наш шаблон с уже вставленными в него переменными загружен.

9. Теперь мы можем настроить ориентацию страниц шаблона: альбомную или книжную.

photo

После этого задаем размер страницы нашего шаблона, выбрав его из предустановленных и нажимаем на кнопку «Сохранить».

photo

Примечание. В целях безопасности редактировать шаблоны печати с расширением xls могут только пользователи с правами администратора.

10. Распечатать в программе можно несколькими способами. Например, переходим в таблицу, выбираем запись. В режиме просмотра нажимаем кнопку «Дополнительно», открывается кнопка «Распечатать». Или находясь в таблице отмечаем галочками одну или несколько нужных записей, а затем нажимаем на кнопку «Распечатать» на верхней панели.

photo

Выбираем наш шаблон печати.

photo

По умолчанию имя файла совпадает с названием шаблона печати. Данный файл будет сохранен на вашем компьютере в той папке, которая указана для вашего браузера, как папка для загружаемых файлов. Всё, документ готов. Можно его открыть и убедиться, что в нем проставлены значения полей из базы.

photo

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

photo

Функции для использования в xls-шаблонах

Шаблоны полей, условия, функции в ячейки xls-файла нужно вставлять в фигурных скобках, например, <Наша компания.Адрес>. Внутри фигурных скобок можно использовать круглые скобки, например, <(Сумма*(1+Наша компания. Ставка НДС/100))>

ПрописьюДата(Дата) — предназначена для вывода даты прописью. Например: Если поле «Дата» имеет значение «21.11.12», функция Прописью(Дата) будет возвращать значение «21 ноября 2010г».

ПрописьюМесяц(Дата) — предназначена для вывода месяца прописью. Например: Если поле «Дата» имеет значение «21.11.12», функция ПрописьюМесяц(Дата) будет возвращать значение «ноября».

ДатаЧисло(Дата) — возвращает значение числа. Например: Если поле «Дата» имеет значение «21.11.12», функция ДатаЧисло(Дата) будет возвращать значение «21».

ДатаМесяц(Дата) — возвращает значение месяца. Например: Если поле «Дата» имеет значение «21.11.12», функция ДатаМесяц(Дата) будет
возвращать значение «11».

ДатаГод(Дата) — возвращает значение года. Например: Если поле «Дата» имеет значение «21.11.12», функция ДатаМесяц(Дата) будет возвращать значение «2012».

ЧислоФормат(Шаблон поля) — возвращает число с двумя знаками после запятой с округлением. Например, ЧислоФормат(3.068) возвращает 3,07.

ЧислоФормат(Шаблон поля,n) — возвращает число с n-знаками после запятой с округлением. Например, ЧислоФормат(3.068,1) возвращает 3,1.

ЧислоФормат(Шаблон поля,n,1) — возвращает число с n-знаками после запятой с округлением и с разделением разрядов. Например, ЧислоФормат(35200.068,2,1) возвращает 35 200,07.

ПрописьюРублей(Шаблон поля) — возвращает значение числа прописью в рублях и копейках. Например, ПрописьюРублей(3.068) возвращает «три рубля 07 копеек».

ПрописьюРублей(Шаблон поля, », ‘none’ ) — возвращает значение числа прописью в рублях без копеек. Например, ПрописьюРублей(3.23, », ‘none’) возвращает «три рубля».

Имя_Подтаблицы.НомерСтроки — возвращает значение номера строки в подтаблице. Например, если нужно вывести в табличном виде все строки подтаблицы «Позиции Счета», то в ячейке, в которой должен выводиться номер строки, нужно прописать <Позиции Счета.НомерСтроки>.

КоличествоСтрок(‘Имя_подтаблицы’) — возвращает значение количества строк в подтаблице. Например, если после распечатанных в табличном виде позиций счета необходимо вывести общее количество строк, то в ячейке нужно прописать: Всего наименований

Кроме того, там где это уместно, можно использовать функции Excel и задавать формат ячеек.

Задание условий

Условия в ячейках xls-файла можно добавить с помощью краткой записи условного оператора if php.

Cинтаксис краткой записи условного оператора if:

expression ? true_value : false_value

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

Возможность печати многостраничных документов

Начиная с версии 2.0.1 в программе «Клиентская база» появилась возможность печати многостраничных документов. Для создания такого шаблона нужно проделать те же действия, которые описаны выше в пункте «Создание шаблонов печати на основе документа Excel». Однако документ, который необходимо будет подготовить перед созданием шаблона будет иметь другой вид:

Нужно добавить в шаблон четыре новых листа, чтобы общее количество листов в документе составило 5.

  • Лист №1 — это лист, где будет содержаться тело шаблона. Назовем его, например, «Body»;
  • Лист №2 — это лист, где будет указываться общий заголовок всего документа. Назовем его «Header»;
  • Лист №3 — это лист, где будет указывается общий подвал шаблона. Назовем его «Footer»;
  • Лист №4 — это лист, где будет указываться заголовок отдельно взятой страницы. Назовем его «Table header»;
  • Лист №5 — это лист, где будет указываться подвал отдельно взятой страницы. Назовем его «Table footer».

Важно: Порядок листов, указанных здесь нарушать нельзя. это приведет к некорректной печати шаблонов.

Распределим все данные по листам шаблона. После распределения, загрузим шаблон обратно в программу и на выходе мы получим шаблон, скомпонованный из листов документа. Например, компоновка при печати шаблона на два листа будет происходить следующим образом:

  • Лист №2
  • Лист №4
  • Лист №1
  • Лист №5
  • Лист №4
  • Лист №1
  • Лист №5
  • Лист №3
  • Header;
  • Table header;
  • Body;
  • Table footer;
  • Table header;
  • Body;
  • Table footer;
  • Footer.

При добавлении такого шаблона, состоящего из пяти листов, необходимо проставить галочку в графе «Разрыв строк» для корректного отображения документа при печати.

photo

Давайте разберем создание сложного многостраничного документа на примере шаблона ТОРГ-12. Мы хотим, чтобы при печати накладной она выглядела следующим образом:

photo

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

photo

Итак, в качестве заголовка документа мы отмечаем то, что будет в начале первой страницы документа и больше нигде повторяться не будет. Шаблон этой части должен быть расположен на листе №2 — Header.

photo

В качестве подвала документа мы выделяем то, что будет в самом конце последней страницы документа. Шаблон этой части должен быть расположен на листе №3 — Footer.

photo

Далее выделяем заголовок таблицы — это та часть, которая должна быть напечатана на каждой странице документа, содержащей данные. То есть, если у нас будет много позиций по накладной, то часть из них будут перенесены на вторую страницу, в начале которой тоже должен стоять заголовок таблицы с названиями столбцов. Шаблон этой части должен быть расположен на листе №4 — Table header.

photo

Теперь давайте посмотрим на окончание (подвал) таблицы (на рисунке выделен фиолетовый цветом), которая должна быть также на каждой странице, но в отличие от заголовка располагаться не до, а сразу же после основной части. В примере с шаблоном печати по форме ТОРГ-12 это одна строка итога. Шаблон этой части должен быть расположен на листе №5 — Table footer.

Читайте также:  Панели инструментов на полосе панели в Excel

photo

Затем обратим внимание на ту часть, которая является основной. В нашем примере — это непосредственно сами позиции накладной. На каждой странице эти данные будут обрамлены сверху той частью, которая помещена в лист «Table header», а снизу той частью, которая размещена в листе «Table footer». Шаблон этой части должен быть расположен на листе №1 — Body. Данные из этой части дублироваться не будут.

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

photo

Скачать или посмотреть шаблон готового многостраничного документа из нашего примера Вы всегда можете в нашей демонстрационной версии по следующей ссылке — http://demo.clientbase.ru/forms.php?table=53&mode=print&admin&form=200&edit

Функции для использования в многостраничных xls-шаблонах

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

Печать.СтрНомер — предназначена для вывода номера страницы документа.
CуммаПоПолюНаСтраницеМинусНДС(Сумма,Отправитель.НДС включен в стоимость,Отправитель.Ставка НДС) — предназначена для вывода суммы по столбцу данных за вычетом НДС, считает сумму для конкретной страницы.
— Сумма — название поля, по которому необходимо посчитать сумму;
— Отправитель.НДС включен в стоимость — название поля, в котором содержатся данные о том включен НДС в стоимость или нет, по нему система определяет нужно ли вычитать из суммы ставку НДС;
— Отправитель.Ставка НДС — название поля со значением ставки НДС в процентах, необходимо для подсчета и вычета.
CуммаПоПолюНаСтраницеНДС(Сумма,Отправитель.НДС включен в стоимость,Отправитель.Ставка НДС) — предназначена для вывода суммы НДС по значениям на конкретной странице.
— Сумма — название поля, по которому необходимо посчитать сумму
— Отправитель.НДС включен в стоимость — название поля, определяющее вычитать ли сумму НДС из стоимости (должно быть Да или Нет), если он включен или если НДС не включен в стоимость, то сумма должна остаться неизменной.
— Отправитель.Ставка НДС — название поля со значением для вычета в процентах
CуммаПоПолюНаСтранице(Сумма) — предназначена для вывода суммы по выбранному полю
— Сумма — название поля, по которому необходимо посчитать сумму

Примечание: Функции, которые считают суммы по странице нельзя помещать в какой-либо другой лист, кроме Листа № 5 — Table footer.

Давайте разберем их применение также на примере ТОРГ-12:

photo

Примечание: Если необходимо добавить шаблон печати, в котором на каждой странице документа будут повторяться верхняя (заголовок) и нижняя (подвал) части, то в файле шаблона достаточно оставить три листа: Лист №1 — Body, Лист №2 — Header и Лист №3 — Footer. Однако, в таком шаблоне нельзя использовать функции расчета по странице. Теперь если включить параметр «Разрыв строк», то в случае многостраничного документа шапка и подвал будут напечатаны на каждой странице. Если параметр «Разрыв строк» не включить, то шапка будет только на первом листе, а подвал только на последнем.

Шаблон телефонного справочника

Как сделать справочник в Excel? Для создания телефонного справочника нужны, минимум, два столбца: имя человека или организации и, собственно, номер телефона. Но можно сразу сделать список более информативным, добавив дополнительные строки.

Шаблон готов. Шапка может быть другой, какие-то столбцы должны быть добавлены, какие-то исключены. Осталось только заполнить справочник информацией.

Дополнительно можно провести еще одну манипуляцию: определить формат ячеек. По умолчанию формат каждой ячейки значится как ОБЩИЙ. Можно оставить все как есть, но для столбца с номером телефона можно задать специальный формат. Для этого надо выделить ячейки из этого столбца, правой кнопкой вызвать меню, выбрать ФОРМАТ ЯЧЕЕК.

Среди предоставленных вариантов выбрать ДОПОЛНИТЕЛЬНЫЙ. Справа откроется мини-список, среди которых можно будет выбрать НОМЕР ТЕЛЕФОНА.

4.1. Нажимаете «Добавить». Здесь указаны все возможные варианты добавления контактов. Ваш вариант – «Загрузить из Excel».


4.2. Вы выбираете файл для загрузки, и все данные из Excel автоматически переносятся в группу. В колонках вы можете указать, где какие данные должны содержаться.

4.3. Нажмите «Продолжить загрузку контактов».
Совет: чем больше вы знаете о своем клиенте, тем точнее и эффективнее будет ваша рассылка по базе. Постарайтесь ввести полное ФИО, и дату рождения.

Немного магии 🙂

1. Сервис автоматически определяет пол клиента по его имени. Эта функция позволит Вам подставлять обращение к Клиенту в нужном спряжении и сделает вашу рассылку еще более таргетированной, а значит, результативной.

2. Не надо беспокоиться о том, что в базу попадут дублирующиеся номера. Телефонные номера автоматически корректируются, удаляются дефисы, пробелы, все дубликаты и все номера на которые невозможно доставить смс (например городские телефоны).

Как пользоваться справочником

Любой справочник нужен для того, чтобы по одному критерию можно было легко узнать остальные. Так, в телефонном справочнике мы можем ввести необходимую фамилию и узнать номер телефона этого человека. В Excel сделать это помогают функции ИНДЕКС и ПОИСКПОЗ.

Имеем небольшой справочник. В действительности, в фирмах обычно более длинные списки, поэтому и искать в них информацию вручную сложно. Составим заготовку, в которой будет значиться вся информация. А появляться она будет по заданному критерию – фамилия, поэтому сделаем этот пункт в виде выпадающего списка (ДАННЫЕ – ПРОВЕРКА ДАННЫХ – ТИП ДАННЫХ – СПИСОК).

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

В ячейку J6 (там, где ИМЯ) вводим команду =ИНДЕКС и начинаем заполнять аргументы.

  1. Массив: выделяем всю таблицу заказов вместе с шапкой. Делаем его абсолютным, фиксируя клавишей F4.
  2. Номер строки: сюда вводим ПОИСКПОЗ и заполняем уже аргументы этой функции. Искомым значением будет ячейка с выпадающим списком – J6 (плюс F4). Просматриваемым массивом является столбец с фамилиями (вместе с шапкой): A1:A13 (плюс F4). Тип сопоставления: точное совпадение, т.е. 0.
  3. Номер столбца: снова нужен ПОИСКПОЗ. Искомое значение: I7. Просматриваемый массив: шапка массива, т.е. А1:Н1 (плюс F4). Тип сопоставления: 0.

Получили следующее. Формула универсальна, ее можно протянуть и на остальные строки в заготовке. Теперь, при выборе фамилии, будет выпадать вся остальная информация. В том числе и номер телефона.

Получается, что команда ИНДЕКС при задании критерия из массива, выдает нам номер его строки и столбца. Но т.к. критерий плавающий, и мы постоянно будем менять фамилии, чтобы узнавать номера телефонов людей, мы дополнительно воспользовались функцией ПОИСКПОЗ. Она помогает искать позиции нужных нам строки и столбца.

Пошаговая инструкция

Друзья, вот план того, что нам предстоит сделать для создания удобного и функционального реестра документов:

  1. Создаём новый документ, задаём ему имя и сохраняем как «Книга с поддержкой макросов».

  1. Выделяем примерную область значений, выбираем раздел «Вставка», элемент «Таблица», подтверждаем наличие заголовков.

  1. Заполняем названия заголовков. В моём случае это «Номер», «Заказчик», «Папка», «Название», «Документ» и «Директория».

  1. Приступаем к заполнению столбцов.
  2. Столбец «Номер» заполняется просто. Необходимо ввести в первую строку значение 1, а во вторую – 2, выделить обе заполненные ячейки, зацепиться за маленький чёрный квадратик в правом нижнем углу выделенной области и протащить его вниз на нужное количество строк. В нижних ячейках автоматически проставятся значения от 1 до …(зависит от нас).

  1. Столбец «Заказчик» можно заполнить вручную или способом, описанным для столбца «Название» при условии, что имена заказчиков – это по совместительству названия папок в общей директории.

  1. В столбце «Папка» я хочу видеть адрес папки, в которой располагается конкретный документ и при этом сделать его ссылкой, при нажатии на которую Excel будет перебрасывать меня в эту папку. Для этого я воспользуюсь функцией «ГИПЕРССЫЛКА». Подробнее опишу ниже.
  2. В столбце «Название» мне нужно отобразить имена документов, но вводить их все вручную очень долго. В Excel существует небольшая хитрость, позволяющая в разы ускорить процесс и автоматически заполнить ячейки названиями всех документов, находящихся в указанной папке. Это возможно благодаря функции «Файлы», ищите алгоритм чуть ниже.
  3. Столбец «Документ» будет содержать рабочую ссылку на нужный файл, нажатие на которую спровоцирует его открытие. Как это сделать, ищите чуть ниже.
  4. Столбец «Директория» — это всё тот же адрес папки, но без гиперссылки. Он нужен для того, чтобы заполнить предыдущие столбцы.

Папка

Чтобы получить активный адрес папки, где расположен нужный документ, воспользуемся функцией «Гиперссылка»:

  1. Ставим курсор в первую ячейку соответствующего столбца.
  2. Копируем адрес нужной папки.

  1. Вставляем его сразу в «Директорию», он пригодится в дальнейшем.

  1. Нажимаем в строке значений на fx и находим нужную функцию или вписываем вручную =ГИПЕРССЫЛКА(«адрес папки»).

  1. Ещё один способ, на мой взгляд, самый удобный – щёлкаем в нужной ячейке ПКМ (правой кнопкой мыши), в меню выбираем команду «Гиперссылка», в открывшемся мастере отыскиваем нужную папку и подтверждаем действие.

  1. Так придётся делать для каждого документа, но если учесть, что большое количество их расположено в одной и той же папке, то процесс не должен отнять много времени. Для последующих документов, являющихся соседями первого по директории, адрес можно просто скопировать.

Название

С названием немного сложнее:

  1. Добавляем к адресу, помещённому нами в «Директорию» ещё один обратный слеш и звёздочку, то есть \*.

  1. Выбираем раздел меню «Формулы», функцию «Диспетчер имён».

  1. А в её мастере команду «Создать».

  1. Задаём имя, в моём случае это будет «Название».

  1. В строке «Диапазон» пишем =ФАЙЛЫ( и щёлкаем курсором по соответствующей ячейке в столбце «Директория», закрываем скобку).

  1. Нажимаем ОК и «Закрыть».
  2. Ставим курсор в первую ячейку столбца.
  3. Вписываем туда формулу =ИНДЕКС(Название;СТРОКА()-1), где Название – это имя, заданное нами на четвёртом шаге данного алгоритма, а -1 обозначает, что данная ячейка стоит второй в столбце.
  4. Нажимаем «Энтер» и видим, что в столбце появились имена всех файлов, расположенных в указанной папке.

  1. Таким методом можно поместить в реестр названия не всех файлов, а выборочно в зависимости от формата. Для этого предварительно в адресе папке, указываемом в столбце «Директория», после звёздочки следует написать расширение файла, например, \*docx, xls или jpeg. Это актуально, когда в одной папке находятся вперемешку файлы разных форматов – таблицы, картинки, текстовые документы и любые другие.

Если что-то вам по моим картинкам и описанию не понятно, то посмотрите следующую подробную инструкцию в формате видео.

Документ

Друзья, столбец «Документ» содержит то же название, но являющееся ссылкой. Чтобы этого добиться, следует проделать все те же действия, что и в предыдущем алгоритме до шага 8. Мы их опустим, так как повторно делать одно и то же не нужно. Теперь немного усложняем формулу. Она примет вид =ГИПЕРССЫЛКА(ИНДЕКС(Название;СТРОКА()-1)). После её размещения и ввода строки заполнятся теми же названиями, но являющими собой активные ссылки.

Вот, кажется, и всё. Старался описать алгоритм наиболее подробно. Меня ещё недавно спросили, как создать реестр сведений о доходах физических лиц, то есть формировать справки 2-НДФЛ и вести их учёт в программе «Налогоплательщик ЮЛ». Тема, в общем, та же, но программа другая, поэтому я решил посвятить ей отдельный материал. До скорых встреч.

Ссылка на основную публикацию