Справочная тема должна быть флагманской темой для каждого аналитика и предпринимателя, которые ведут свои записи в электронных таблицах. Ведь гиды помогут:
Например, рассмотрим любую таблицу данных, такую как таблица покупки спортивного инвентаря. Если вы ведете такую таблицу в Excel, то, вероятно, через некоторое время обнаружите:
- сколько товаров из категории «Мячи» было куплено за последние три месяца,
- сколько денег вы должны каждому поставщику в отдельности и по группам продуктов
- и так далее. Получите это сами!
Механизм очень прост. Он состоит всего из двух шагов. Если вам лень читать, просто посмотрите это видео про гайды в Google Docs и вы все поймете:
скачать пример в файле
Птицы…
и попробуйте сделать то же самое сами.
Пример видео упрощен. Это требует, чтобы вы начали с нуля. Далее я расскажу, как создать ссылку на существующую таблицу данных с помощью Excel.
Шаг первый. Создай список уникальных значений
В Excel самый простой способ создать список уникальных значений — использовать сводную таблицу.
- Выберите диапазон, который содержит список ВСЕХ значений
- Перейдите в меню «Данные» (в более новых версиях Excel — «Вставить») > «Сводная таблица
- Нажмите OK в появившемся диалоговом окне сводной таблицы
- Макет сводной таблицы будет создан на новом листе. Переместите имя выбранного вами поля в список значений (strings.
Перетащите поле в область ROW
- Прозрачный! Вы должны получить список всех уникальных значений.
Теперь скопируйте его и вставьте как значения. Это будет вашим гидом. Иногда все же нужно очистить таблицу от дубликатов. Например, категории товаров «Мячи» и «Мячи» (с пробелом в конце) будут учитываться компьютером как 2 разных значения. В этом случае нужно вернуться к таблице с данными и заменить все шарики пробелом на шарики без пробелов. Сделать это:
- Нажмите сочетание клавиш Ctrl+H - Найти и заменить
- В поле Найти: напишите «Шарики » с пробелом
- В поле «Заменить на:» введите «Мячи» без пробелов
- Нажмите кнопку «Заменить все!
Таким образом, вы очистите свою базу от ненужных повторений. Затем вы также можете удалить их из каталога. Вы уже на полпути. Теперь вам нужно связать свой каталог с исходной таблицей
Шаг второй. Привязать справочник к исходной таблице
Вы должны выбрать весь столбец в исходной таблице, где вы вводите категорию из каталога. А дальше всего пара кликов мышкой:
- Перейдите в меню «Данные» > «Проверка данных
- Откроется диалоговое окно. Выберите Тип данных > Список
- Вы сможете выбрать источник данных. Зайдите в него и выберите выделение из руководства
- Совет: Выберите диапазон, который длиннее, чем просто все данные из каталога. Например, если был диапазон A2:A30, исправьте его на A2:A3000. Тогда вы сможете добавлять в каталог новые строки и они будут входить в исходный код.
Последнее. Проверьте свой каталог. Попробуйте ввести значения из выпадающего списка, попробуйте также ввести неправильное значение. Компьютер должен запрещать ввод значений, которых нет в справочнике.
И ура! Надеюсь, следуя моим советам, вы смогли настроить себе один или несколько каталогов в исходной таблице данных. Вы сделали большой шаг к автоматизации своего бизнеса. Если у вас возникли проблемы, напишите об этом в разделе комментариев.
Excel полезен для создания телефонных справочников. Причем информация там не только надежно хранится, но и всегда может быть использована для выполнения различных манипуляций, сравнения с другими списками и т.п.
Чтобы каталог впоследствии стал действительно полезным массивом, его нужно правильно создать.
Шаблон телефонного справочника
Как создать справочник в Excel? Чтобы создать телефонный справочник, вам нужно как минимум два столбца: имя человека или организации и фактический номер телефона. Но вы можете сразу сделать список более информативным, добавив больше строк.
Шаблон готов. Заголовок может быть другим, какие-то столбцы нужно добавить, какие-то исключить. Остается только наполнить гайд информацией.
Кроме того, вы можете выполнить манипуляцию, чтобы: определить формат ячеек. По умолчанию формат каждой ячейки указан как ОБЩИЙ. Можно оставить все как есть, а можно указать специальный формат для столбца с номером телефона. Для этого выделите ячейки из этого столбца, щелкните правой кнопкой мыши, чтобы открыть меню, выберите ФОРМАТИРОВАНИЕ ЯЧЕЙКИ.
Выберите НЕОБЯЗАТЕЛЬНЫЙ из вариантов. Справа открывается мини-список, где можно выбрать НОМЕР ТЕЛЕФОНА.
Как пользоваться справочником
Любой справочник нужен, чтобы легко узнавать остальных по одному критерию. Так в телефонном справочнике мы можем ввести нужную фамилию и узнать номер телефона этого человека. В Excel в этом помогают функции ИНДЕКС и ПОИСКПОЗ.
У нас есть небольшой путеводитель. На самом деле, компании, как правило, имеют более длинные списки, что затрудняет поиск информации вручную. Давайте создадим пустоту, где будет появляться вся информация. И появится он по заданному критерию - фамилии, поэтому сделаем этот элемент в виде выпадающего списка (ДАННЫЕ - ПРОВЕРКА ДАННЫХ - ТИП ДАННЫХ - СПИСОК).
Необходимо сделать так, чтобы при выборе фамилии в остальные ячейки автоматически вносились соответствующие данные. Ячейки с телефоном выделены зеленым, потому что это самая важная информация.
В ячейке J6 (где ИМЯ) введите команду =ИНДЕКС и начните заполнять аргументы.
- Массив: выберите всю таблицу заказов вместе с заголовком. Мы, безусловно, делаем это, фиксируя его с помощью клавиши F4.
- Номер строки: введите здесь ПОИСКПОЗ и заполните аргументы этой функции. Искомым значением будет ячейка с выпадающим списком — J6 (плюс F4). Матрица, которая просматривается, представляет собой столбец с фамилией (включая заголовок): A1: A13 (плюс F4). Тип соответствия: точное совпадение, т.е. 0.
- Номер столбца: нужно снова ПОИСКПОЗ. Желаемое значение: I7. Поисковый массив: лидер массива, т.е. A1:H1 (плюс F4). Тип совпадения: 0.
Получил следующее. Формула универсальна, ее можно распространить на остальные линии в заготовке. Теперь при выборе фамилии вся остальная информация будет выпадать. В том числе номер телефона.
Получается, что команда ИНДЕКС при задании критерия из массива дает нам номер его строки и столбца. Но так как критерий текучий, и мы будем постоянно менять имена, чтобы узнать номера телефонов людей, мы дополнительно воспользовались функцией ПОИСКПОЗ. Это помогает найти позиции нужной нам строки и столбца.
Как сопоставить два списка в Excel
работа со списками в Excel предполагает их сопоставление. Они сравнивают данные, находят идентичные или уникальные позиции. Например, попробуем сравнить два простых списка.
Есть информация о двух складах. Задача: проверить, каких товаров нет на обоих складах, чтобы в дальнейшем оформить заказ и доставить недостающие товары.
Выделите оба списка (без заголовков) с помощью клавиши CTRL. Нам не нужно свободное пространство между списками (т.е столбец B). На вкладке ГЛАВНАЯ выберите УСЛОВНОЕ ФОРМАТИРОВАНИЕ - ПРАВИЛА ВЫБОРА ЯЧЕЙКИ - ПОВТОР ЗНАЧЕНИЯ.
Появится небольшое окно, в котором вы можете выбрать, будет ли команда отображать повторяющиеся или уникальные значения. Давайте выберем УНИКАЛЬНЫЙ. Они будут выделены цветом, который можно выбрать справа. Наш красный.
Теперь вы можете скопировать все красные ячейки из левого столбца и добавить их в правый и наоборот. Будет два соответствующих списка.
Если вы заполняете таблицу в Excel, и данные в одном столбце иногда могут повторяться, например, название товара или фамилия сотрудника, то чтобы не вводить каждый раз нужный параметр, проще и проще один раз создать выпадающий список и выбрать из него значение.
В статье мы рассмотрим, как создавать выпадающие списки различных типов в электронной таблице Excel.
Создаем простой выпадающий список
Для этого в ячейки А1:А7 вносим данные, которые будут отображаться в списке. Теперь выбираем ячейку, где будем создавать выпадающий список — B2.
Перейдите на вкладку «Данные» и нажмите на кнопку «Проверка данных».
На вкладке «Параметры» в поле «Тип данных» выберите «Список». Вводить значения в исходное поле можно разными способами:
1 - ввести значения для списка вручную через точку с запятой;
2 - укажите диапазон ячеек, куда вводятся данные для выпадающего списка;
3 - выделить ячейки с именами, нажать на них правой кнопкой мыши и выбрать в меню пункт «Присвоить имя».
Затем введите «имя» для выбранных данных.
Выберите ячейку B2 и введите «=» в поле «Источник», затем введите созданное имя.
Таким образом, мы создали простой выпадающий список в Excel.
Если у вас есть заголовок для столбца и вам нужно заполнить каждую строку значениями, выберите не одну ячейку, а диапазон ячеек - B2:B9. После этого можно будет выбрать нужное значение в каждой ячейке из выпадающего списка.
Добавляем значения в выпадающий список – динамический список
В этом случае мы добавим значения в нужный диапазон и они автоматически добавятся в выпадающий список.
Выберите диапазон ячеек — D1:D8, затем нажмите «Форматировать как таблицу» на вкладке «Главная» и выберите стиль.
Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками».
вверху пишем название таблицы — «Сотрудники», и заполняем ее данными.
Выберите ячейку, которая будет содержать раскрывающийся список, и нажмите кнопку «Проверка данных». В следующем окне в поле "Источник" введите следующее: =ДВССЫЛ("Таблица1"). У меня на листе одна таблица, поэтому я пишу "Таблица1", если есть другая - "Таблица2" и так далее.
Теперь давайте добавим в наш список имя нового сотрудника: Ира. Он появился в выпадающем списке. Если мы удалим имя из таблицы, оно также будет удалено из списка.
Выпадающий список со значениями с другого листа
Если таблица с выпадающими списками находится на одном листе, а данные для этих списков на другом, то эта функция нам очень поможет.
На Листе 2 выберите одну ячейку или диапазон ячеек, затем нажмите кнопку «Проверка данных».
Перейдите на лист 1, установите курсор в поле «Источник» и выберите нужный диапазон ячеек.
Теперь вы можете добавить имена на лист 1, они будут добавлены в выпадающие списки на листе 2.
Создаем зависимые выпадающие списки
Допустим, у нас есть три поля: имя, фамилия и отчество сотрудников. Для каждого вы должны дать имя. Выбираем ячейки в этой области, она также может быть пустой — со временем можно будет добавлять данные к тем, которые появляются в выпадающем списке. Щелкаем по ним правой кнопкой мыши и выбираем из списка «Присвоить имя».
Первый называется "Имя", второй - "Фамилия", третий - "Отец».
Создадим еще одну область, где будут записаны присвоенные имена. Назовем его «Сотрудники».
Создаем первый выпадающий список, который будет состоять из названий областей. Выберите ячейку E1 и на вкладке «Данные» выберите «Проверка данных».
В поле «Тип данных» выберите «Список», в поле источника — либо введите «=Сотрудники», либо выберите диапазон ячеек, которому присвоено имя.
Создан первый раскрывающийся список. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выбираем "Имя", то во втором появится список фамилий, если выбираем "Фамилия" - список фамилий.
Выберите ячейку и нажмите кнопку «Проверка данных». В поле «Тип данных» выберите «Список», в поле источника введите следующее: =ДВССЫЛ($E$1). Здесь E1 — это ячейка с первым выпадающим списком.
Следуя этому принципу, вы можете создавать зависимые выпадающие списки.
Если в дальнейшем потребуется ввести значения в поле, которое дано имя, например «Фамилия». Перейдите на вкладку «Формулы» и нажмите «Диспетчер имен». Теперь в названии диапазона выберите «Фамилия», а внизу вместо последней ячейки С3 напишите С10. Нажмите на галочку. После этого диапазон увеличится и в него можно будет добавлять данные, которые автоматически появятся в выпадающем списке.
