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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Posted On 02.12.2017

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

ВСЕ УРОКИ

Поиск по сайту:

Пользовательский поиск

Назад

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

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

Пример телефонного справочника в Excel

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

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

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

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

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

Выведем телефон Иванова (22222) в выбранную ячейку.

Телефонный справочник

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

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

Получаем:

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

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

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

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

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

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

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

А теперь совместим функцию«ИНДЕКС» с функцией «ПОИСКПОЗ».

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

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

Получаем:

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

Прилагаю пример телефонного справочника в Excel.

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

Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой «эксель» по адресу: ngt@inbox.ru

Назад&nbsp&nbsp&nbsp Вверх страницы

Для разработки телефонного справочника воспользуемся офисной программой Microsoft Access. Открыть программу можно двумя способами, используя меню кнопки «Пуск» => ВСЕ ПРОГРАММЫ => MicrosoftOffice =>, или ярлык MicrosoftAccess 2010, размещенный на Рабочем столе компьютера. Откройте программу любым из предложенных способов.

В открывшемся диалоговом окне выберите Новая база данных, в поле Имя файла справа введите имя создаваемой базы данных telephone directory. Чтобы изменить расположение, в котором будет создан файл, нажмите кнопку Обзор (Изображение раскрытой папки справа от поля Имя файла), выберите новое расположение и нажмите кнопку ОК, а далее командную кнопку Создать. В MS Access имя файла задается ДО создания новой базы. Все изменения, вносимые в базу данных, отображаются в ее файлах, что позволяет минимизировать риск потери большого количества данных даже при непредвиденных сбоях (например, отключение электропитания).

Microsoft Access создаст базу данных и откроет пустую таблицу (с именем «Таблица1») в режиме таблицы.

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

Начинаем проектировать телефонную книгу. Телефонная книга будет состоять из двух таблиц: первая таблица будет содержать номера телефонов, ФИО и адрес абонентов, а также код категории, вторая таблица будет содержать код категории и наименование категории.

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

Дважды кликнув на наименование поля (например, наименование поля Код), получаем мигающий курсор в имени поля. Введите название поля, которое запланировано, а именно, Номер телефона. Далее открывается окно типов данных, которые могут быть размещены в данном поле. Выберите поле Текст. Используя левую клавишу мыши, добавьте следующее поле . После заполнения всех полей таблицы перейдите в режим конструктора, воспользовавшись пиктограммой ( ) на ленте Главная. Перед переходом в режим конструктора MSAccess попросит присвоить имя первой таблице. Назовем ее «Телефонный справочник», далее ОК.

Создайте вторую таблицу. Выберите ленту Создание и на этой ленте блок Таблицы, далее Конструктор таблиц ( ). Запустите конструктор создания таблиц. Откроется окно проектирования структуры таблицы.

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

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

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

Закройте таблицы Категория и Телефонный справочник, при закрытии появляется окно диалога, предлагающее сохранить изменения в созданных таблицах. Подтвердите сохранение изменений, выбрав командную кнопку ДА (ОК). Обратите внимание на то, что в окне ВСЕ ОБЪЕКТЫ Accessпоявились новые элементы – только что созданные таблицы «ТЕЛЕФОННЫЙ СПРАВОЧНИК» и «КАТЕГОРИИ», сохраненные не в виде отдельных файлов на диске, а в структуре базы данных.

Заполним таблицы.

Первой откройте таблицу «КАТЕГОРИИ», дважды кликнув левой клавишей мыши по наименованию таблицы в окне ВСЕ ОБЪЕКТЫ ACCESS => Таблицы или с помощью контекстно независимого меню, установив курсор мыши на наименование таблицы и, нажав правую клавишу мыши, выбрать из ниспадающего списка команд команду ОТКРЫТЬ.

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

Откройте таблицу «Телефонный справочник» и заполните ее приведенными ниже данными:

Таблица «ТЕЛЕФОННЫЙ СПРАВОЧНИК»

Телефонный справочник
Номер телефона	ФИО абонента	Адрес	Код категории
	Анна Егоровна Чернова	Москва, ул. Большая, 1	РД
	Иван Петрович Рут	Москва, Маленький пер., 15	РД
	Ольга Иванова	Москва, Пр-т Центральный, 21	ДР
	ГИПЕРМАРКЕТ	Москва, Мастеров пер., 5	МС
	ЦВЕТОЧНЫЙ МАГАЗИН	Москва, ул. Фиалковая, 28	МГ
	Андрей Сидоров	Москва, Аллея Звезд, 4	ДР
	Светлана Геннадьевна Короткова	Москва, ул. Родная, 8	РД

При заполнении КОДА КАТЕГОРИИ надо иметь в виду, что коды категорий двухсимвольные.

В структуре нашей базы данных уже есть две таблицы. Однако они пока существуют независимо и наша цель сделать так, чтобы в таблице «ТЕЛЕФОНЫ» вместо кодов категорий абонентов подставлялись их полные наименования из таблицы «КАТЕГОРИИ». Для этого необходимо настроить подстановочные параметры поля «КОД КАТЕГОРИИ», а также задать параметры связей между таблицами.

Для установления связей между таблицами воспользуемся вкладкой Работа с базами данных и выбираем из блока ОТНОШЕНИЯ пиктограмму Схема данных ( ). После выполнения команды открывается окно схемы данных, в которое нужно добавить уже созданные таблицы. Воспользуйтесь пиктограммой Отобразить таблицу ( ); откроется окно добавления таблицы, в котором представлены наши две таблицы. Используя SHIFT+ левая клавиша мыши, выделите обе таблицы и подтвердите выбор командной кнопкой ДОБАВИТЬ, а затем, используя командную кнопку ЗАКРЫТЬ, перейти в окно СХЕМЫ ДАННЫХ.

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

Microsoft Access упражнение «телефонный справочник»

Для настройки связей воспользуемся пиктограммой ( ) ИЗМЕНЕНИЕ СВЯЗЕЙ. В открывшемся окне, используя командную кнопку Новое…, настраиваем связи между полями Код таблицы КАТЕГОРИИ и поля Код категории таблицы ТЕЛЕФОННЫЙ СПРАВОЧНИК окна Создание.

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

Закройте макет СХЕМЫ ДАННЫХ, используя кнопку ЗАКРЫТЬ. На вопрос о сохранении схемы подтвердите желание Сохранить «Да/ОК». Закройте окно «Схема данных».

Естественно, электронный справочник удобней, чем традиционная телефонная книга. Во-первых, можно редактировать (изменять) телефоны абонентов, которые размещены в электронном телефонном справочнике. Во-вторых, можно быстро выбрать нужного абонента с полным набором необходимой информации: ФИО абонента, Адрес, Номер телефона. Это делается с помощью запросов.

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

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

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

Перетащите поля «Номер телефона», «ФИО абонента» «Адрес» «Наименование категории» и «Код» в формируемую таблицу. Можно указать ;параметр «Сортировка» (по возрастанию) для поля «Код категории». Это обеспечит группировку отображаемых записей по категориям.

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

Сохраните запрос под именем «Телефоны родных», закройте бланк запросов по образцу и посмотрите результаты выполнения запроса, активировав пиктограмму ВЫПОЛНИТЬ.

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

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

Шаблон базы данных «Домашняя телефонная книга» можно скачать здесь по ссылке:

Материал подготовила методист Шутилина Л.А.

При подготовке материала использовались ресурсы:

1. Как пользоваться Microsoft Access, URL –
2. Среда выполнения Microsoft Access 2010, URL –
3. MicrosoftAccess 2010, URL –

Уроки Microsoft Excel

       

Работа с таблицами Excel

Соединение базы данных сотрудников и списка с окладами

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

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

Для соединения двух таблиц откройте две книги, содержащие списки, В книге База на листе Сотрудники выделите столбцы A:U и скопируйте их в буфер обмена. Перейдите в книгу Штат и, выделив столбец G на листе Оклады, произведите вставку из буфера. База данных сотрудников разместится в диапазоне G1:AA11.

ПРИМЕЧАНИЕ: Обратите внимание на то, что ширина столбцов после вставки осталась такой же, как и в рабочем листе Сотрудники.

Теперь из таблицы необходимо удалить все лишние данные.

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

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

Чтобы удалить ненужные столбцы, сначала следует их выделить (одновременное выделение столбцов можно произвести, удерживая нажатой клавишу ), a затем вызвать контекстное меню и выбрать в нем команду Удалить. Удаление можно выполнить и посредством команды Правка/Удалить.

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

Рис. 5.10. Лист «Оклады», содержащий фрагмент листа «Сотрудники»

Теперь на одном листе электронных таблиц Excel собраны все необходимые нам данные:

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

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

1

Нажмите кнопку справки Excel или нажмите клавишу F1.

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

2

Нажмите ссылку «Справка», пока не найдете нужную тему.

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

3

Нажмите кнопку «Показать оглавление».

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

4

Введите ключевые слова в поле поиска и нажмите кнопку «Поиск».

Excel выполняет поиск в Интернете по темам, связанным с ключевыми словами, которые вы вводите, например печатать заголовки . Если ваш компьютер не имеет доступа к Интернету при выполнении поиска, Excel отображает на вашем компьютере только локальные разделы справки.

5

Щелкните правой кнопкой мыши на панели задач Windows и выберите «Показывать Windows рядом».

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

Назад Далее

Posted On 02.12.2017

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EXCEL ДЛЯ «ЧАЙНИКОВ» И НЕ ТОЛЬКО

ВСЕ УРОКИ

Поиск по сайту:

Пользовательский поиск

Назад

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

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

Пример телефонного справочника в Excel

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

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

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

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

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

Выведем телефон Иванова (22222) в выбранную ячейку.

Телефонный справочник

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

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

Получаем:

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

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

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

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

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

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

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

А теперь совместим функцию«ИНДЕКС» с функцией «ПОИСКПОЗ».

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

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

Получаем:

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

Прилагаю пример телефонного справочника в Excel.

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

Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой «эксель» по адресу: ngt@inbox.ru

Назад&nbsp&nbsp&nbsp Вверх страницы

Для разработки телефонного справочника воспользуемся офисной программой Microsoft Access. Открыть программу можно двумя способами, используя меню кнопки «Пуск» => ВСЕ ПРОГРАММЫ => MicrosoftOffice =>, или ярлык MicrosoftAccess 2010, размещенный на Рабочем столе компьютера. Откройте программу любым из предложенных способов.

В открывшемся диалоговом окне выберите Новая база данных, в поле Имя файла справа введите имя создаваемой базы данных telephone directory. Чтобы изменить расположение, в котором будет создан файл, нажмите кнопку Обзор (Изображение раскрытой папки справа от поля Имя файла), выберите новое расположение и нажмите кнопку ОК, а далее командную кнопку Создать. В MS Access имя файла задается ДО создания новой базы. Все изменения, вносимые в базу данных, отображаются в ее файлах, что позволяет минимизировать риск потери большого количества данных даже при непредвиденных сбоях (например, отключение электропитания).

Microsoft Access создаст базу данных и откроет пустую таблицу (с именем «Таблица1») в режиме таблицы.

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

Начинаем проектировать телефонную книгу. Телефонная книга будет состоять из двух таблиц: первая таблица будет содержать номера телефонов, ФИО и адрес абонентов, а также код категории, вторая таблица будет содержать код категории и наименование категории.

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

Дважды кликнув на наименование поля (например, наименование поля Код), получаем мигающий курсор в имени поля. Введите название поля, которое запланировано, а именно, Номер телефона. Далее открывается окно типов данных, которые могут быть размещены в данном поле. Выберите поле Текст. Используя левую клавишу мыши, добавьте следующее поле . После заполнения всех полей таблицы перейдите в режим конструктора, воспользовавшись пиктограммой ( ) на ленте Главная. Перед переходом в режим конструктора MSAccess попросит присвоить имя первой таблице. Назовем ее «Телефонный справочник», далее ОК.

Создайте вторую таблицу. Выберите ленту Создание и на этой ленте блок Таблицы, далее Конструктор таблиц ( ). Запустите конструктор создания таблиц. Откроется окно проектирования структуры таблицы.

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

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

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

Закройте таблицы Категория и Телефонный справочник, при закрытии появляется окно диалога, предлагающее сохранить изменения в созданных таблицах. Подтвердите сохранение изменений, выбрав командную кнопку ДА (ОК). Обратите внимание на то, что в окне ВСЕ ОБЪЕКТЫ Accessпоявились новые элементы – только что созданные таблицы «ТЕЛЕФОННЫЙ СПРАВОЧНИК» и «КАТЕГОРИИ», сохраненные не в виде отдельных файлов на диске, а в структуре базы данных.

Заполним таблицы.

Первой откройте таблицу «КАТЕГОРИИ», дважды кликнув левой клавишей мыши по наименованию таблицы в окне ВСЕ ОБЪЕКТЫ ACCESS => Таблицы или с помощью контекстно независимого меню, установив курсор мыши на наименование таблицы и, нажав правую клавишу мыши, выбрать из ниспадающего списка команд команду ОТКРЫТЬ.

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

Откройте таблицу «Телефонный справочник» и заполните ее приведенными ниже данными:

Таблица «ТЕЛЕФОННЫЙ СПРАВОЧНИК»

Телефонный справочник
Номер телефона	ФИО абонента	Адрес	Код категории
	Анна Егоровна Чернова	Москва, ул. Большая, 1	РД
	Иван Петрович Рут	Москва, Маленький пер., 15	РД
	Ольга Иванова	Москва, Пр-т Центральный, 21	ДР
	ГИПЕРМАРКЕТ	Москва, Мастеров пер., 5	МС
	ЦВЕТОЧНЫЙ МАГАЗИН	Москва, ул. Фиалковая, 28	МГ
	Андрей Сидоров	Москва, Аллея Звезд, 4	ДР
	Светлана Геннадьевна Короткова	Москва, ул. Родная, 8	РД

При заполнении КОДА КАТЕГОРИИ надо иметь в виду, что коды категорий двухсимвольные.

В структуре нашей базы данных уже есть две таблицы. Однако они пока существуют независимо и наша цель сделать так, чтобы в таблице «ТЕЛЕФОНЫ» вместо кодов категорий абонентов подставлялись их полные наименования из таблицы «КАТЕГОРИИ». Для этого необходимо настроить подстановочные параметры поля «КОД КАТЕГОРИИ», а также задать параметры связей между таблицами.

Для установления связей между таблицами воспользуемся вкладкой Работа с базами данных и выбираем из блока ОТНОШЕНИЯ пиктограмму Схема данных ( ). После выполнения команды открывается окно схемы данных, в которое нужно добавить уже созданные таблицы. Воспользуйтесь пиктограммой Отобразить таблицу ( ); откроется окно добавления таблицы, в котором представлены наши две таблицы. Используя SHIFT+ левая клавиша мыши, выделите обе таблицы и подтвердите выбор командной кнопкой ДОБАВИТЬ, а затем, используя командную кнопку ЗАКРЫТЬ, перейти в окно СХЕМЫ ДАННЫХ.

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

Microsoft Access упражнение «телефонный справочник»

Для настройки связей воспользуемся пиктограммой ( ) ИЗМЕНЕНИЕ СВЯЗЕЙ. В открывшемся окне, используя командную кнопку Новое…, настраиваем связи между полями Код таблицы КАТЕГОРИИ и поля Код категории таблицы ТЕЛЕФОННЫЙ СПРАВОЧНИК окна Создание.

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

Закройте макет СХЕМЫ ДАННЫХ, используя кнопку ЗАКРЫТЬ. На вопрос о сохранении схемы подтвердите желание Сохранить «Да/ОК». Закройте окно «Схема данных».

Естественно, электронный справочник удобней, чем традиционная телефонная книга. Во-первых, можно редактировать (изменять) телефоны абонентов, которые размещены в электронном телефонном справочнике. Во-вторых, можно быстро выбрать нужного абонента с полным набором необходимой информации: ФИО абонента, Адрес, Номер телефона. Это делается с помощью запросов.

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

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

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

Перетащите поля «Номер телефона», «ФИО абонента» «Адрес» «Наименование категории» и «Код» в формируемую таблицу. Можно указать ;параметр «Сортировка» (по возрастанию) для поля «Код категории». Это обеспечит группировку отображаемых записей по категориям.

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

Сохраните запрос под именем «Телефоны родных», закройте бланк запросов по образцу и посмотрите результаты выполнения запроса, активировав пиктограмму ВЫПОЛНИТЬ.

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

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

Шаблон базы данных «Домашняя телефонная книга» можно скачать здесь по ссылке:

Материал подготовила методист Шутилина Л.А.

При подготовке материала использовались ресурсы:

1. Как пользоваться Microsoft Access, URL –
2. Среда выполнения Microsoft Access 2010, URL –
3. MicrosoftAccess 2010, URL –

Уроки Microsoft Excel

       

Работа с таблицами Excel

Соединение базы данных сотрудников и списка с окладами

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

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

Для соединения двух таблиц откройте две книги, содержащие списки, В книге База на листе Сотрудники выделите столбцы A:U и скопируйте их в буфер обмена. Перейдите в книгу Штат и, выделив столбец G на листе Оклады, произведите вставку из буфера. База данных сотрудников разместится в диапазоне G1:AA11.

ПРИМЕЧАНИЕ: Обратите внимание на то, что ширина столбцов после вставки осталась такой же, как и в рабочем листе Сотрудники.

Теперь из таблицы необходимо удалить все лишние данные.

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

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

Чтобы удалить ненужные столбцы, сначала следует их выделить (одновременное выделение столбцов можно произвести, удерживая нажатой клавишу ), a затем вызвать контекстное меню и выбрать в нем команду Удалить. Удаление можно выполнить и посредством команды Правка/Удалить.

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

Рис. 5.10. Лист «Оклады», содержащий фрагмент листа «Сотрудники»

Теперь на одном листе электронных таблиц Excel собраны все необходимые нам данные:

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

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