Как создать справочник в excel

Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ&nbsp&nbsp

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я – ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

EXCEL ДЛЯ “ЧАЙНИКОВ” И НЕ ТОЛЬКО

ВСЕ УРОКИ

Как создать справочник в Excel. Массивы. Функции ИНДЕКС и ПОИСКПОЗ

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

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

Функция «ИНДЕКС»

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

Например, у нас есть список людей с телефонами:

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

Выведем телефон Иванова (22222) в выбранную ячейку. Для этого выделяем ячейку, куда хотим вывести искомое значение и через функции (обведено красным) находим функцию ИНДЕКС:

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

В строке состояния (обведена синим) мы видим, что сначала указан диапазон (массив), в котором происходит поиск значения, а дальше следуют координаты искомого значения:

Функция «ПОИСКПОЗ»

дает возможность найти номер строки, в которой находится искомое значение:

Как видим «Смирнов» находится в 3-ей строке массива, что и выдает функция «ПОИСКПОЗ».

Для вывода функции выбираем ячейку, где будет отражаться номер строки, вызываем функцию «ПОИСКПОЗ» через функции (обведено красным) :

Выбираем в качестве искомого значения ячейку, где будет задаваться фамилия человека, которого мы хотим найти в списке, в качестве массива – ячейки со всеми фамилиями. Тип сопоставления поставим «0».

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

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

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

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

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

Источник: www.offisny.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: excel2.ru

Создание книги

В этом курсе:

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

Создание книги

Выберите элемент Пустая книга или нажмите клавиши CTRL+N.

Начните вводить текст.

Создание книги на основе шаблона

Откройте вкладку Файл и выберите пункт Создать.

Откройте шаблон двойным щелчком.

Щелкните и начните вводить текст.

Откройте новую пустую книгу.

Откройте вкладку Файл.

В разделе Доступные шаблоны дважды щелкните элемент Пустая книга.

Сочетание клавиш. Чтобы быстро создать пустую книгу, вы также можете нажать клавиши CTRL+N.

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

При необходимости также можно добавить и удалить листы.

Дополнительные сведения о добавлении и удалении листов см. в статье Вставка и удаление листов.

Создание книги на основе существующей

Откройте вкладку Файл.

В разделе Шаблоны щелкните Из существующего документа.

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

Щелкните книгу, а затем нажмите кнопку Создать.

Создание книги на основе шаблона

Откройте вкладку Файл.

Выполните одно из указанных ниже действий.

Чтобы использовать один из образцов шаблонов, доступных в Excel, в разделе Доступные шаблоны щелкните Образцы шаблонов, а затем дважды щелкните нужный шаблон.

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

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

Примечание: Вкладка Личные шаблоны содержит созданные вами шаблоны. Если вы не видите шаблон, который хотите использовать, убедитесь, что он находится в правильной папке. Пользовательские шаблоны обычно хранятся в папке Шаблоны (стандартный путь в Windows Vista: C:Пользователиимя_пользователяAppDataLocalMicrosoftШаблоны; стандартный путь в Microsoft Windows XP: C:Documents and Settingsимя_пользователяApplication DataMicrosoftШаблоны).

Совет: Другие шаблоны книг можно скачать с сайта Microsoft Office.com. В разделе Доступные шаблоны щелкните Шаблоны Office.com, выберите определенную категорию и дважды щелкните шаблон, который хотите скачать.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник: support.office.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: modulkassa.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: exceltable.com

Финансы в Excel

Использование справочников

Содержание
Поиск в одномерном справочнике
Поиск в двумерном справочнике
Использование справочников для создания пользовательского интерфейса

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

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

  • Одномерный – продукты с наименованием и единицей измерения
  • Двумерный – прайс-листы с различными ценами по продуктам и периодам.

Поиск в одномерном справочнике

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

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

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

Функция ВПР ищет значение по определенному номеру столбца (в примере – второй столбец) в прямоугольном диапазоне ячеек. Последний параметр (0 или ЛОЖЬ) используется для указания искать ли ближайшее значение (ИСТИНА), либо точное совпадение (ЛОЖЬ). Первый вариант редко используется на практике, так как требует предварительно отсортированного диапазона поиска. Важное замечание по параметру «столбец». Это не номер столбца во всей электронной таблице, а номер столбца в диапазоне поиска. Для аналойного поиска по горизонтальному массиву используется функция ГПР (HLOOKUP).

Поиск в двумерном справочнике

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

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

Функция ПОИСКПОЗ (MATCH) ищет и возвращает порядковый номер элемента в списке (работает аналогично ВПР). Функция СМЕЩ (OFFSET) возвращает значение по относительной ссылке от определенной ячейки – в примере, от верхнего левого угла таблицы прайс-листов. Функция преобразования даты ДАТА (DATE) превращает дату в из таблицы продаж в дату, используемую в прайс-листе – первое число месяца.

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

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

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

Первое условие на продукт возвращает массив <6,4;6,9;6,7>, второе условие выбирает из этого массива последнее значение.

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

Использование справочников для создания пользовательского интерфейса

Обратите внимание на выпадающий список для выбора элементов справочника на листе «Пример2», таблицы «Продажи» (Пример2!B17).

Организация подобных выпадающих списков возможна через опцию «Проверка». Доступ в Excel до 2007 через меню ДанныеПроверка (DataValidation), в Excel 2007 и старше – лента ДанныеРабота с даннымиПроверка данных (DataData ToolsData Validation).

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

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

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

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

Обратите внимание на описание проверки в ячейке Пример2!B18. Здесь вместо ссылки на лист «Пример1» задана формула

Имя listProducts задано в примере как диапазон Пример1!$A$3:$A$6. Очевидно, что использование такого именованного диапазона делает более прозрачным смысл заданного условия проверки, а также снижает риск ошибки в формуле.

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

Источник: www.excelfin.ru

Добавить комментарий

Adblock
detector