Справочник в excel как сделать

Справочник в MS EXCEL

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

Создадим Справочник на примере заполнения накладной.

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

Таблица Товары

Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица , т.е. в формате EXCEL 2007(см. файл примера ). По умолчанию новой таблице EXCEL присвоит стандартное имя Таблица1. Измените его на имя Товары, например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )

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

Для гарантированного обеспечения уникальности наименований товаров используем Проверку данных ( Данные/ Работа с данными/ Проверка данных ):

  • выделим диапазон А2:А9на листе Товары;
  • вызовем Проверку данных;
  • в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность:

При создании новых записей о товарах (например, в ячейке А10), EXCEL автоматически скопирует правило Проверки данных из ячейки А9 – в этом проявляется одно преимуществ таблиц, созданных в формате Excel 2007, по сравнению с обычными диапазонами ячеек.
Проверка данныхсрабатывает, если после ввода значения в ячейку нажата клавиша ENTER. Если значение скопировано из Буфера обмена или скопировано через Маркер заполнения, то Проверка данных не срабатывает, а лишь помечает ячейку маленьким зеленым треугольником в левом верхнем углу ячейке.

Через меню Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные можно получить информацию о наличии данных, которые были введены с нарушением требований Проверки данных.

Для контроля уникальности также можно использовать Условное форматирование (см. статью Выделение повторяющихся значений).

Теперь, создадим Именованный диапазон Список_Товаров, содержащий все наименования товаров:

  • выделите диапазон А2:А9;
  • вызовите меню Формулы/ Определенные имена/ Присвоить имя
  • в поле Имя введите Список_Товаров;
  • убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
  • нажмите ОК.

Таблица Накладная

К таблице Накладная, также, предъявляется одно жесткое требование: все значения в столбце (поле) Товар должны содержаться в ключевом поле таблицы Товары. Другими словами, в накладную можно вводить только те товары, которые имеются в справочной таблице Товаров, иначе, смысл создания Справочника пропадает.
Для формирования Выпадающего (раскрывающегося) списка для ввода названий товаров используем Проверку данных:

  • выделите диапазон C4:C14;
  • вызовите Проверку данных;
  • в поле Тип данных выберите Список;
  • в качестве формулы введите ссылку на ранее созданный Именованный диапазон Список_товаров, т.е. =Список_Товаров .

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

Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС. Для этого используем функцию ВПР() :

или аналогичную ей формулу

Преимущество этой формулы перед функцией ВПР() состоит в том, что ключевой столбец Наименование в таблице Товары не обязан быть самым левым в таблице, как в случае использования ВПР() .

В столбцах Цена и НДС введите соответственно формулы:
=ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»»)
=ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)

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

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

Со справочником можно работать не только в личном кабинете, но и в MS Excel, OpenOffice, Google Документах и других табличных редакторах. Для этого существует функция «Импорт» и «Экспорт».

Экспорт скачивает текущий список товаров в виде таблицы в формате: XLS, XLSX или CSV. В будущем эту таблицу можно отредактировать и загрузить обратно в личный кабинет.

Импорт — это способ управления справочником товаров. У импорта две главных функции — обновление и замена каталога:

  • Обновление справочника. Используйте эту опцию, когда меняете цены на услуги, распределяете позиции по группам и добавляете новые товары большой партией.
  • Замена справочника. Используйте замену, чтобы удалить текущий справочник товаров и заменить на новый. Например, чтобы вслед за сменой деятельности вашей точки поменять и ассортимент.

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

Читать еще:  Как сделать чтобы в таблице excel не скрывался текст?

Если у вас ведущая и ведомая точка продаж, загружайте файл только в ведущую точку. Новые товары автоматически появятся и в ведомой точке.

2. Таблицы доступны в форматах: XLS, XLSX и CSV. С такими форматами работают MS Excel, Google Таблицы и другие программы для работы с таблицами. CSV-файлы можно открыть в стандартном Блокноте.

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

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

У эксель-таблиц свои правила для названий столбцов и заполнения полей. Чтобы не настраивать все вручную, воспользуйтесь шаблоном или скачайте текущий справочник.

Шаблоны помогают заполнить таблицу с нуля. Чтобы скачать шаблон, перейдите в «Товары > Импорт» и выберите нужный формат шаблона. Затем заполните файл по нашему образцу и загрузите обратно в МодульКассу через кнопку «Импорт».

Текущий справочник помогает редактировать готовый ассортимент. Например, если изменились цены на всю выпечку. Тогда скачайте текущий справочник, отредактируйте строки с выпечной и загрузите файл обратно через «Импорт > Обновить каталог».

Чтобы скачать текущий справочник, нажмите «Товары > Экспорт» и выберите формат файла. Затем отредактируйте строки и загрузите исправления обратно через «Импорт».

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

Правило для таблиц XLS и XLSX: располагайте товары только на первом листе таблицы.

О правилах для таблиц CSV вы можете узнать в памятке. Чтобы открыть памятку, нажмите «Товары > Импорт > Правила оформления формата CSV»

Общие правила:

  1. Обязательных полей всего четыре. Это название товара, код, штрихкоды, и единица измерения.
  2. Если оставить пустыми поля«Цена», «Минимальная цена» и «Группа», то карточка товара будет без группы и с нулевой ценой. Дополнить карточку можно позднее через личный кабинет.
  3. Если оставить пустыми поля «НДС», «СНО», «Тип», то подставляется значение по умолчанию: НДС и СНО из настроек точки, а тип товара — «Обычный».
  4. Заполняйте поля по образцу в шаблоне. Например, нельзя написать «штуки» или «штук», личный кабинет понимает только «шт». Образец — в нижней части шаблона.
  5. Если у вас несколько штрихкодов, перечисляйте их без пробелов через точку с запятой (11039;11045;23052)

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

Главное при обновлении справочника — совпадение кода товара. Когда загружаете файл, личный кабинет сравнивает между собой коды товара в таблице и точке продаж. В зависимости от совпадения кодов личный кабинет:

  • Добавляет новую карточку, если товар с таким же кодом есть только в эксель-файле. Чтобы добавить новые карточки товаров, скачайте шаблон, заполните его новыми товарами и загрузите обратно через «Импорт > Обновить каталог».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как сопоставить два списка в Excel

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

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

Выделим оба списка (без шапок) с помощью клавиши CTRL. Свободное место между списками (т.е. столбец B) нам не нужно. Затем на вкладке ГЛАВНАЯ выбираем УСЛОВНОЕ ФОРМАТИРОВАНИЕ – ПРАВИЛА ВЫДЕЛЕНИЯ ЯЧЕЕК – ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ.

Появится небольшое окно, где можно выбрать, чтобы команда показывала повторяющиеся или уникальные значения. Выберем УНИКАЛЬНЫЕ. Они подсветятся цветом, который можно выбрать справа. У нас это красный.

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

База данных в Excel: особенности создания, примеры и рекомендации

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

Что такое база данных?

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

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

Создание хранилища данных в Excel

База, создаваемая нами, будет простой и без изысков. Настоящие же вместилища данных — довольно громоздкие и представляют собой большую информационную систему с внутренним «ядром», которое состоит из множества строк программного кода и написано специалистом.

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

Горизонтальные строки в разметке листа «Эксель» принято называть записями, а вертикальные колонки – полями. Можно приступать к работе. Открываем программу и создаем новую книгу. Затем в самую первую строку нужно записать названия полей.

Особенности формата ячеек

Полезно узнать о том, как правильно оформлять содержимое ячейки. Если ваша база данных в Excel будет содержать какие-либо денежные суммы, то лучше сразу в соответствующих полях указать числовой формат, в котором после запятой будет идти два знака. А если где-либо встречается дата, то следует выделить это место и также установить для него соответствующий формат. Таким образом, все ваши данные будут оформлены правильно и без ошибок. Все операции с пустыми полями программы производятся через контекстное меню «Формат ячеек».

Также немаловажно и соответствующее оформление проекта. Лист, на котором находится проект, нужно подписать, чтобы избежать путаницы. Для того чтобы система могла отличать простое содержание от заголовков и подписей, следует выделять их курсивом, подчеркиванием или жирным шрифтом, при этом не забывая помещать названия в отдельные, не объединенные поля. Это стоит делать для возможности использования таких инструментов, как автоформа и автофильтр.

Что такое автоформа в «Эксель» и зачем она требуется?

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

Фиксация «шапки» базы данных

Кроме этого, не нужно забывать о закреплении первой строки. В Excel 2007 это можно совершить следующим образом: перейти на вкладку «Вид», затем выбрать «Закрепить области» и в контекстном меню кликнуть на «Закрепить верхнюю строку». Это требуется, чтобы зафиксировать «шапку» работы. Так как база данных Excel может быть достаточно большой по объему, то при пролистывании вверх-вниз будет теряться главная информация – названия полей, что неудобно для пользователя.

Читать еще:  Как сделать график точки безубыточности в excel онлайн?

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

Продолжение работы над проектом

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

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

Как создать раскрывающиеся списки?

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

Для того чтобы база данных MS Excel предоставляла возможность выбора данных из раскрывающегося списка, необходимо создать специальную формулу. Для этого нужно присвоить всем сведениям о родителях диапазон значений, имена. Переходим на тот лист, где записаны все данные под названием «Родители» и открываем специальное окно для создания имени. К примеру, в Excel 2007 это можно сделать, кликнув на «Формулы» и нажав «Присвоить имя». В поле имени записываем: ФИО_родителя_выбор. Но что написать в поле диапазона значений? Здесь все сложнее.

Диапазон значений в Excel

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

Чтобы получился динамический диапазон, необходимо использовать формулу СМЕЩ. Она, независимо от того, как были заданы аргументы, возвращает ссылку на исходные данные. В выпадающем списке, который получится в итоге, не должно встречаться пустых значений. С этим как раз превосходно справляется динамический диапазон. Он задается двумя координатами ячеек: верхней левой и правой нижней, словно по диагонали. Поэтому нужно обратить внимание на место, откуда начинается ваша таблица, а точнее, на координаты верхней левой ячейки. Пусть табличка начинается в месте А5. Это значение и будет верхней левой ячейкой диапазона. Теперь, когда первый искомый элемент найден, перейдем ко второму.

Нижнюю правую ячейку определяют такие аргументы, как ширина и высота. Значение последней пусть будет равно 1, а первую вычислит формула СЧЁТ3(Родители!$B$5:$I$5).

Итак, в поле диапазона записываем =СМЕЩ(Родители!$A$5;0;0;СЧЁТЗ(Родители!$A:$A)-1;1). Нажимаем клавишу ОК. Во всех последующих диапазонах букву A меняем на B, C и т. д.

Работа с базой данных в Excel почти завершена. Возвращаемся на первый лист и создаем раскрывающиеся списки на соответствующих ячейках. Для этого кликаем на пустой ячейке (например B3), расположенной под полем «ФИО родителей». Туда будет вводиться информация. В окне «Проверка вводимых значений» во вкладке под названием «Параметры» записываем в «Источник» =ФИО_родителя_выбор. В меню «Тип данных» указываем «Список».

Аналогично поступаем с остальными полями, меняя название источника на соответствующее данным ячейкам. Работа над выпадающими списками почти завершена. Затем выделяем третью ячейку и «протягиваем» ее через всю таблицу. База данных в Excel почти готова!

Внешний вид базы данных

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

Как перенести базу данных из Excel в Access

Не только лишь Excel может сделать базу данных. Microsoft выпустила еще один продукт, который великолепно управляется с этим непростым делом. Название ему – Access. Так как эта программа более адаптирована под создание базы данных, чем Excel, то и работа в ней будет более быстрой и удобной.

Но как же сделать так, чтобы получилась база данных Access? Excel учитывает такое желание пользователя. Это можно сделать несколькими способами:

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

  • Можно импортировать лист формата .xls (.xlsx). Откройте Access, предварительно закрыв Excel. В меню выберите команду «Импорт», и кликните на нужную версию программы, из которой будете импортировать файл. Затем нажимайте «ОК».
  • Можно связать файл Excel с таблицей в программе Access. Для этого в «Экселе» нужно выделить диапазон ячеек, содержащих необходимую информацию, и, кликнув на них правой кнопкой мыши, задать имя диапазона. Сохраните данные и закройте Excel. Откройте «Аксесс», на вкладке под названием «Внешние данные» выберите пункт «Электронная таблица Эксель» и введите ее название. Затем щелкните по пункту, который предлагает создать таблицу для связи с источником данных, и укажите ее наименование.

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