Как создать ключевое поле в excel
Excel несомненно обладает богатейшим инструментарием по обработке больших массивов информации. Много трудов написано по этой замечательной программе. Я же хочу остановиться на двух практически полезных функциях и их возможном применении при работе с 1С.
Excel несомненно обладает богатейшим инструментарием по обработке больших массивов информации. Много трудов написано по этой замечательной программе. Я же хочу остановиться на двух практически полезных функциях и их возможном применении при работе с 1С.
Несмотря на громкое название заголовка статьи (это всего лишь шутка), материал не претендует на оригинальность и открытие каких-то сокровенных тайн. Естественно, что эти две описываемые функции не являются панацеей. Но по опыту могу сказать, что они довольно часто выручают в различных ситуациях.
1. Функция ВПР (VLOOKUP). И ее разновидность - функция ГПР.
Данная функция позволяет "склеивать" по какому-либо ключевому (уникальному) полю две таблицы в одну. Например, можно склеить справочник физических лиц с их адресами по коду физического лица.
1) Как это работает
Представим, что есть две таблицы в Excel. Первая содержит поля: Код физлица, ФИО физлица, дата примема на работу. Вторая содержит поля: Код физлица, Адрес проживания. И в одной и в другой таблице есть уникальное поле "Код физлица". По нему и произведем сборку двух таблиц в одну. На скриншоте, представленном ниже в колонку "D" выведен результат работы функции. Цифрами обозначены составные части функции. Итак, в колонку "D" по ключевому полю (1) из области ячеек (2) Excel нам "подтянул" вторую колонку - по номеру (3) указанной области (2). При этом мы использовали точное совпадение ключевого поля (4).
Разберем пошагово работу функции.
Увидев в ячейке формулу ВПР Excel смотрит на ключевое поле текущей строки (1). Затем он ищет это ключевое поле в первой колонке в указанной области (2) - область поиска. Причем ищет по точному совпадению - это говорится в параметре (4) функции (ИСТИНА - поиск по вхождению значения ключевого поля в значение первой колонки области поиска, ЛОЖЬ - точное совпадение). После того, как находит - берет колонку, указанную в параметре (3) функции, т.е. вторую. Вот и все - ничего сложного, как видите.
Обратите внимание на указание области поиска - параметр (2) функции ВПР. Вы можете видеть там знаки доллара - это так назваемые абсолютные ссылки. Они означают, что при копировании формулы из ячейки в ячейку ссылка меняться не будет. В то время как ссылка на ключевое поле (1) будет меняться по мере копирования формулы в нижние строки. Что нам собственно и на руку, ведь ключевое поле мы уже должны будем взять из нижней строки, при этом область поиска остается прежней. Ссылку можно сделать абсолютной, выделив ее в строке формулы и нажав F4.
Функция ГПР является полным аналогом функции ВПР, с той лишь разницей, что поиск осуществляется не по вертикали, а по горизонтали.
2) Кому и когда может пригодиться
- Пользователю. В любую типовую конфигурацию 1С встроен универсальный отчет по справочникам, документам и регистрам. С помощью него можно выгрузить любую информацию по соответствующей таблице. И не нужно ждать отдел разработки, чтобы они сделали отчет, долго тестировали и еще дольше бились за выпуск в свет релиза с этим отчетом. Все бы хорошо, но как быть, когда нужно получить один отчет на базе двух и более таблиц - универсальный отчет может за один раз выгрузить информацию только по одной таблице?
В развитие этой ситуации, предположим, что нам нужно получить адреса всех сотрудников, принятых на договор подряда за первое полугодие текущего года. Нет ничего проще. Выгружаем универсальным отчетом в Excel таблицу по документу "Договор на выполнение работ с физлицами", у которого есть реквизит "Физлицо" (используем нужные нам отборы по реквизитам документа, чтобы выгрузить только нужных сотрудников). Отдельной колонкой в этом отчете показываем код физлица. Адресная информация хранится в другой таблице - соответствующем регистре сведений. Выгружаем ее по всем физлицам. Здесь же отдельной колонкой также показываем код физлица. Теперь в Excel склеиваем эти две таблицы. Задача выполнена. - Разработчику. Представим ситуацию, когда вам дали несколько таблиц, выгруженных из другой системы (например SAP) в Excel, и попросили прогрузить их в один справочник в 1С (все таблицы представляют собой реквизиты одного и того же справочника внутри 1С, но разных таблиц хранения в системе SAP). Обрабатывать поочередно каждый файл нудно, долго и неэффективно. И снова нам поможет ВПР. Собираем разрозненные таблицы воедино и загружаем за один заход.
- Тестировщику или пользователю, для проверки отчета или других данных. Допустим есть отчет, написанный разработчиками, соединяющий информацию из разных таблиц. Стоит задача проверить его. Также можно прибегнуть к этой функции, чтобы что-то с чем-то сравнить в отчете.
2. Функция СЧЁТЕСЛИ (COUNTIF).
Данная функция подсчитывает количество значений указанной области, удовлетворяющих определенным критериям. Так, например, можно искать дубли значений (есть ли задвоенные элементы справочника, не двоит ли строки отчет и т.д.).
1) Как это работает
Представим, что есть таблица в Excel, которая содержит список эелементов справочника физических лиц с колонками: Код физлица, ФИО физлица. Нужно прверить справочник на дублирующиеся элементы. Выведем напротив каждой строки количество совпадений кода физлица. На скриншоте, представленном ниже в колонку "C" выведен результат работы функции. Цифрами обозначены составные части функции. Итак, в колонке "C" в области (1) Excel подсчитал количество совпадений значения (2).
Разберем пошагово работу функции.
Увидев в ячейке формулу СЧЁТЕСЛИ Excel ищет в области, указанной в параметре (1) формулы, совпадения со значением, указанным в параметре (2). Количество найденных совпадений суммируется и результат выводится в ячейку с формулой. Далее можно наложить автофильтр на таблицу и отобрать строки, где количество совпадений больше 1 - это и будут задублированные элементы. Быстро и просто.
Также обратите внимание на абсолютную ссылку на область, указанную в параметр (1). Более подробно можете прочитать в описании предыдущей функции.
Имейте ввиду, что критерием поиска совпадений может быть не только ссылка на одну ячейку, но и другие произвольные выражения, что фактически позволяет применять данную функцию при решении широкого спектра задач.
Создание структуры таблицы завершается определением ключевого поля - выбор одного или нескольких полей, которое однозначно (без повторений) идентифицирует каждую запись таблицы, данные в котором не могут повторяться (имеют уникальные значения например, код или инвентарные номера), для примера это поле Номер1(рис.1). Не рекомендуется определять ключ по полям «Имена» или «Фамилии», поскольку нельзя исключить повторения этой пары значений для разных людей. Таким образом, указание главного ключа является единственный способ отличить одну запись от другой.
Если ключ состоит из одного поля, он называется простым, если из нескольких полей – сложным.
Для создания ключевого поля переместится на нужное полеи нажать кнопку Ключевое поле на панели инструментов (аналогичная команда контекстного меню или команда Правка/Определить ключ). При этом рядом с именем этого поля появится изображение ключа.
Когда выполняется просмотр записей,Microsoft Access показывает их упорядоченными по первичному ключу по умолчанию.
Когда для таблицы обозначены ключевые поля, то Microsoft Access не позволит хранить данные с одним и тем же значением первичного ключа или ввод пустых значений в ключевое поле.
Ключевые поля используются для создания межтабличных связей.
В случаях, когда невозможно гарантировать уникальность значений каждого поля, существует возможность создать ключ, состоящий из нескольких полей. Чаще всего такая ситуация возникает для таблицы, используемой для связывания двух таблиц в отношении «многие-ко-многим».
11. Действия с полями в режиме Конструктора.
В готовую спецификацию можно вносить изменения. В частности, можно изменять параметры отдельных полей, добавлять поля в запись в нужных местах и удалять лишние. Но при этом следует постараться внести все исправления в спецификацию до начала заполнения базы данных, т.к. попытка изменить параметры полей, заполненной базы может повлечь за собой потерю или искажение данных.
Переупорядочение полей:
· Выбрать поле, подлежащее перемещению, нажать в селекторной колонке, расположенной на левой части окна таблицы.
· Повторно нажать в селекторной колонке и удерживая кнопку мыши нажатой перетащить указатель мыши в нужную позицию.
Удаление полей.
· Перейти на поле, которое необходимо удалить;
· Нажать на панели инструментов кнопку (команда Правка – Удалить, контекстное меню Удалить строки).
Вставка полей.
· Установить указатель строки в поле, над которым необходимо разместить новое поле.
· Нажать на панели инструментов кнопку (команда Вставка – Строки, контекстное меню Добавить строки).
При выходе из конструктора будет предложено сохранить таблицу и ввести ее имя. По умолчанию структура таблицы будем сохранена с именем "Таблица1".
Работа с таблицами.
Наполнение данными созданных структур таблиц может осуществляться разными способами. В окне созданной базы данных (рис. 13) после сохранения структуры таблицы появляется значок для новой таблицы, нажав на который будет открыта таблица, в которую будут вводиться данные. При этом не имеет значения, какую таблицу заполнять первой.
Рисунок 13 – Окно базы данных.
После нажатия на значке нужной таблицы на экране появится бланк (форма) таблицы (рис.14), в которую необходимо ввести соответствующие данные.
Ввод данных логично начинать со справочников. После заполнения справочников можно заполнить и основные таблицы. Все эти операции проводятся в режиме открытия таблицы. При этом она выводится в виде, очень похожем на электронные таблицы Excel с заголовками. Справедливы все оформительские операции: изменение ширины/высоты столбца/строки, перемещение столбцов.
Рисунок 14 – Окно ввода данных в таблицу.
Очень полезна операция фиксирования столбцов (от прокрутки), чтобы они всегда присутствовали на экране. Она выполняется через пункт меню Формат - Закрепить столбцы с предварительной отметкой столбцов для фиксации (с помощью клавиши Shift). Закрепленные столбцы разместятся слева от всех столбцов. Если нужно освободить эти столбцы, используется команда Формат - Освободить все столбцы.
Конечный пользователь не работает в таком режиме, для него используются Формы.
В режиме таблицы добавляются, редактируются или просматриваются табличные данные. Также можно проверить орфографию и напечатать табличные данные, отфильтровать и отсортировать записи, изменить внешний вид таблицы или изменить структуру таблицы, добавив или удалив столбцы.
Если до этого момента связь между таблицами еще не была установлена, то обеспечение корректности ввода полагается на пользователя. При вводе система проверяет на соответствие данные, которые вводятся, свойствами текущего поля и при необходимости преобразует данные в соответствующий тип.
В режиме таблицы отображаются все введенные данные. Если же создана только структура таблицы, значит, данных в таблице еще нет. В пустой таблица Маркер записи (с изображением треугольного указателя, направленного вправо) обычно установлен в первой строке таблицы. При вводе новой записи (на маркере которой изображен карандаш) автоматически появляется следующая строка (на маркере которой изображена звездочка для обозначения новой записи). Маркер новой записи всегда отмечает последнюю строку таблицы.
Для добавления записей используется кнопка Новая запись на панели инструментов. При нажатии этой кнопки в конце таблицы появляется незаполненная строка. Либо выполнить команду Вставка – Запись (Правка – Перейти – Новая запись).
Для удаления записей необходимо выделить записи и нажать Delete (Правка – Удалить,комбинация клавиш ), при удалении одной записи - выбрать любое поле записи и нажать кнопку Удалить запись на панели инструментов, либо Правка – Удалить запись. При удалении записи появляется диалоговое окно, с помощью которого нужно подтвердить удаление.
Добавленная или измененная запись автоматически сохраняется при переходе к другой записи или при закрытии таблицы. При этом маркер записи меняет свою форму: карандаш превращается в направленную вправо стрелку.
Для сохранения записи нужно ввести допустимое значение в поле первичного ключа. Чтобы сохранить запись, не покидая ее, можно нажать комбинацию клавиш . Значение первичного ключа проверяется по типу данных, уникальности и другим правилам проверки значения, введенным в свойстве Условие на значение.
Кнопка Отмена(либо Esc), расположенная на панели инструментов, позволяет отменить изменения, внесенные только в текущую запись. После перехода к следующей записи эта кнопка вернется в обычный режим отмены, изменив следующую запись, уже не получится отменить изменение предыдущей.
Сохранить текущую запись на диске можно с помощью команды Записи - Сохранить запись.
Если необходимо внести изменения в структуру какой-нибудь таблицы, то следует выделить соответствующую таблицу и нажать кнопку Конструктор.
Переходить из Режима таблицы в Режим конструктора можно с помощью команд Вид/Конструктор или Вид/Режим таблицы или нажать соответствующую кнопку на панели инструментов (рис. 15).
Рисунок 15 – Выбор режима работы.
После формирования структуры таблицы ее необходимо сохранить с помощью команды Файл – Сохранить.
Связывание объектов.
В базе данных сведения из каждого источника сохраняются в отдельной таблице. При работе с данными из нескольких таблиц и для упрощения конструирования многотабличных форм, отчетов и запросов устанавливаются связи между таблицами.
Для создания связей между таблицами, кроме ключевых полей, необходимо задать Внешний ключ. Поле внешнего ключа определяет способ связывания таблиц, значение такого поля должно совпадать со значениями ключевого поля. Поля, используемые для создания связи, могут иметь разные имена, но должны иметь одинаковый тип данных и содержать данные одного рода.
Связи определяются (вводятся) в окне Схемы данных(информационно-логическая модель), которое можно открыть, нажав кнопку на панели инструментов или выполнить команду Сервис – Схема данных.
Открывается окно схемы и диалог добавления таблицы (рис. 16) - это дает возможность добавить к схеме таблицу. В данном окне необходимо выбрать названия таблиц, для которых необходимо создать связи и нажать кнопку Добавить или можно перетащить значок нужной таблицы из окна базы данных в окно Схемы данных.
Рисунок 16 – Окно Схемы данных.
После закрытия окна добавления таблицы можно установить связь между таблицами путем перемещения поля из одной таблицы на эквивалентное поле другой таблицы. Обычно связывают ключевое поле одной таблицы с одноименным полем в другой. При отпускании кнопки мыши открывается окно диалога установления параметров связи (рис. 17), где необходимо проверить имена полей, подлежащих связыванию, а также установить все необходимые опции:
Обеспечение целостности данных - не допускается удаление любой из таблиц или связанных полей до удаления связи.
Рисунок 17 – Окно установки параметров связи.
Условие целостности не позволяет ввод во внешнее ключевое поле связанной таблицы значения, отсутствующего в ключевом поле главной таблицы. Однако возможен ввод во внешнее ключевое поле пустого значения, показывающего, что записи не являются связанными. Например, невозможно ввести в таблицу заказ на имя несуществующего клиента, однако, допускается ввод заказа, не относящегося ни к одному из клиентов с пустым значением в поле «Код клиента».
Кнопка Объединение – позволяет изменить тип отношений.
Виды связей между таблицами могут быть следующими:
Связь «один-к-одному» (1-1). Связь «один-ко-многим» (1-∞). Связь «много-ко-многим» (∞-∞).
Для изменения связи на ней необходимо нажать два раза, что приведет к открытию диалогового окна установки параметров связи, либо выполнить команду Связи – Изменить связь…
Для удаления связи, ее необходимо активизировать, нажав на ней один раз, а затем нажать клавишу Delete.
Для того, чтобы снова открыть диалоговое окно Добавление таблицы, необходимо выполнить команду Связи – Добавить таблицу, или нажать на кнопке на панели инструментов.
Если вам нужно отобразить список значений, которые сможет выбирать пользователь, добавьте на лист список.
Добавление списка на лист
Создайте перечень элементов, которые должны отображаться в списке, как показано на рисунке.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).
Щелкните ячейку, в которой нужно создать список.
В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.
Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.
В поле Связь с ячейкой введите ссылку на ячейку.
Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.
В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.
Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX "Список".
Добавление поля со списком на лист
Упростите ввод данных для пользователей, позволив им выбирать значение из поля со списком. Поле со списком состоит из текстового поля и списка, которые вместе образуют раскрывающийся список.
Можно добавить поле со списком одного из двух типов: элемент управления формы или элемент ActiveX. Если вы хотите создать поле со полем, которое позволит пользователю редактировать текст в текстовом поле, можно использовать ActiveX поле со ActiveX. Поле со списком ActiveX Control является более универсальным, так как можно изменить свойства шрифта, чтобы текст на листе с увеличенным масштабом было удобнее читать, и с помощью программирования отображать его в ячейках, содержащих список проверки данных.
Выберите столбец, который можно скрыть на листе, и создайте список, введя по одному значению в ячейки.
Примечание: Можно также создать список на другом листе той же книги.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
Выберите тип поля со списком, которое нужно добавить:
в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);
в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).
Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.
Чтобы изменить размер поля, наведите указатель мыши на один из маркеров изменения размера и перетащите границу элемента управления до достижения нужной высоты и ширины.
Чтобы переместить поле со списком на листе, выделите его и перетащите в нужное место.
Форматирование элемента управления формы "Поле со списком"
Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.
Откройте вкладку Элемент управления и настройте следующие параметры.
Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.
Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст "Фруктовое мороженое".
Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.
Форматирование элемента ActiveX "Поле со списком"
На вкладке Разработчик нажмите кнопку Режим конструктора.
Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.
Вот как можно настроить свойства поля со списком на этом рисунке:
Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Тип, начертание или размер шрифта
Щелкните свойство Font (Шрифт), нажмите кнопку . и выберите тип, размер или начертание шрифта.
Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Связь с ячейкой для отображения значения, выбранного в списке
Щелкните свойство LinkedCell (Связанная ячейка).
Связывание поля со списком и списка элементов
Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
Изменение количества отображаемых элементов списка
Щелкните поле ListRows и введите число элементов.
Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Если кто-то неправильно вводит данные или вы считаете, что коллега может запутаться в вводе данных, добавьте метку. Простое имя, например "Телефон", позволяет другим пользователям знать, что нужно поместить в ячейку, и ваши метки также могут предоставлять более сложные инструкции.
Вы можете добавлять метки к формам и элементам ActiveX.
Добавление метки (элемент управления формы)
На вкладкеРазработчик нажмите кнопкуВставить и выберите .
Щелкните на листе место, где должен быть расположен левый верхний угол метки.
Чтобы задать свойства элемента управления, щелкните его правой кнопкой мыши и выберите пункт Формат элемента управления.
На вкладке Разработчик нажмите кнопку Вставитьи в ActiveX элементыуправления нажмите кнопку .
Щелкните на листе место, где должен быть расположен левый верхний угол метки.
Щелкните на листе место, где должен быть расположен левый верхний угол метки.
Чтобы задать свойства управления, нажмите кнопку Свойства .
Совет: Можно также щелкнуть метку правой кнопкой мыши и выбрать пункт Свойства.
Откроется диалоговое окно Свойства. Для получения дополнительных сведений о свойстве выберите его и нажмите клавишу F1, чтобы отобразить соответствующий раздел справки Visual Basic. Вы можете также ввести имя свойства в поле Поиск справки Visual Basic. Свойства указаны в данной таблице.
Сводка по свойствам метки, сгруппированным по функциональным категориям
Описание параметра
Необходимое свойство
Загружает ли его при открытом книге. (Игнорируется для ActiveX элементов управления.)
AutoLoad (Excel)
Может ли элемент управления получать фокус и реагировать на события, генерируемые пользователем
Enabled (форма)
Можно ли изменять элемент управления
Locked (форма)
Имя элемента управления
Name (форма)
Способ привязки элемента управления к расположенным под ним ячейкам (не закреплен, можно перемещать, но нельзя изменять размер, можно перемещать и изменять размер)
Placement (Excel)
Можно ли выводить элемент управления на печать
PrintObject (Excel)
Является ли элемент управления видимым или скрытым
Visible (форма)
Атрибуты шрифта (полужирный, курсив, размер, зачеркивание, подчеркивание и насыщенность)
Bold, Italic, Size, StrikeThrough, Underline, Weight (форма)
Поясняющий текст элемента управления, определяющий или описывающий его
Caption (форма)
Способ выравнивания текста в элементе управления (по левому краю, по центру или по правому краю)
TextAlign (форма)
Будет ли содержимое элемента управления переноситься по словам в конце строки
WordWrap (форма)
Размер и положение
Будет ли размер элемента управления изменяться автоматически для отображения всего содержимого
AutoSize (форма)
Высота или ширина в пунктах
Height, Width (форма)
Расстояние от элемента управления до левого или верхнего края листа
Left, Top (форма)
Форматирование
BackColor (форма)
Стиль фона (прозрачный или непрозрачный)
BackStyle (форма)
BorderColor (форма)
Тип границы (отсутствует или одинарная линия)
BorderStyle (форма)
Цвет переднего плана
ForeColor (форма)
Наличие тени элемента управления
Shadow (Excel)
Вид границы (обычная, приподнятая, утопленная, вдавленная, рельефная)
SpecialEffect (форма)
Изображение
Точечный рисунок, отображаемый в элементе управления
Picture (форма)
Положение рисунка относительно его заголовка (слева, сверху, справа и так далее)
PicturePosition (форма)
Клавиатура и мышь
Сочетание клавиш для элемента управления
Accelerator (форма)
Настраиваемый значок мыши
MouseIcon (форма)
Тип указателя, отображаемого при наведении указателя мыши на объект (например, стандартный, стрелка или I-образный)
MousePointer (форма)
На вкладке Разработчик нажмите кнопкуВставить и в ActiveX элементовуправления выберите .
Щелкните на листе место, где должен быть расположен левый верхний угол текстового поля.
Чтобы изменить ActiveX, нажмите кнопку Режим конструктора .
Чтобы задать свойства управления, нажмите кнопку Свойства .
Совет: Можно также щелкнуть текстовое поле правой кнопкой мыши и выбрать пункт Свойства.
Откроется диалоговое окно Свойства. Для получения дополнительных сведений о свойстве выберите его и нажмите клавишу F1, чтобы отобразить соответствующий раздел справки Visual Basic. Вы можете также ввести имя свойства в поле Поиск справки Visual Basic. Доступные свойства указаны в приведенной ниже таблице.
Сводка по свойствам текстового поля, сгруппированным по функциональным категориям
Описание параметра
Необходимое свойство
Загружает ли его при открытом книге. (Игнорируется для ActiveX элементов управления.)
AutoLoad (Excel)
Может ли элемент управления получать фокус и реагировать на события, генерируемые пользователем
Enabled (форма)
Можно ли изменять элемент управления
Locked (форма)
Имя элемента управления
Name (форма)
Способ привязки элемента управления к расположенным под ним ячейкам (не закреплен, можно перемещать, но нельзя изменять размер, можно перемещать и изменять размер)
Placement (Excel)
Можно ли выводить элемент управления на печать
PrintObject (Excel)
Является ли элемент управления видимым или скрытым
Visible (форма)
Будет ли в качестве элементарной единицы для расширения выделения использоваться слово или знак
AutoWordSelect (форма)
Атрибуты шрифта (полужирный, курсив, размер, зачеркивание, подчеркивание и насыщенность)
Bold, Italic, Size, StrikeThrough, Underline, Weight (форма)
Будет ли выделенный текст оставаться выделенным при потере фокуса элементом управления
HideSelection (форма)
Режим запуска редактора метода ввода (IME) по умолчанию
IMEMode (форма)
Будет ли размер элемента управления изменяться автоматически для отображения строк текста полностью или частично
IntegralHeight (форма)
Максимально возможное число вводимых знаков
MaxLength (форма)
Поддерживает ли элемент управления ввод нескольких строк текста
MultiLine (форма)
Заполнители, например звездочка (*), отображаемые вместо вводимых знаков
PasswordChar (форма)
Может ли пользователь выделить строку текста, щелкнув слева от нее
SelectionMargin (форма)
Текст в элементе управления
Text (форма)
Способ выравнивания текста в элементе управления (по левому краю, по центру или по правому краю)
TextAlign (форма)
Будет ли содержимое элемента управления переноситься по словам в конце строки
WordWrap (форма)
Данные и привязка данных
Диапазон, связанный со значением элемента управления.
LinkedCell (Excel)
Содержимое или состояние элемента управления
Value (форма)
Размер и положение
Будет ли размер элемента управления изменяться автоматически для отображения всего содержимого
AutoSize (форма)
Высота или ширина в пунктах
Height, Width (форма)
Расстояние от элемента управления до левого или верхнего края листа
Left, Top (форма)
Форматирование
BackColor (форма)
Стиль фона (прозрачный или непрозрачный)
BackStyle (форма)
BorderColor (форма)
Тип границы (отсутствует или одинарная линия)
BorderStyle (форма)
Цвет переднего плана
ForeColor (форма)
Наличие тени элемента управления
Shadow (Excel)
Вид границы (обычная, приподнятая, утопленная, вдавленная, рельефная)
SpecialEffect (форма)
Автотабуляция при вводе пользователем в элемент управления максимально допустимого количества знаков
AutoTab (форма)
Клавиатура и мышь
Включено ли перетаскивание
DragBehavior (форма)
Способ выделения при получении элементом управления фокуса (выделить все или не выделять ничего)
EnterFieldBehavior (форма)
Реакция на нажатие клавиши ВВОД (создание новой строки или перемещение фокуса)
EnterKeyBehavior (форма)
Настраиваемый значок мыши
MouseIcon (форма)
Тип указателя, отображаемого при наведении указателя мыши на объект (например, стандартный, стрелка или I-образный)
MousePointer (форма)
Разрешены ли знаки табуляции в области редактирования
TabKeyBehavior (форма)
Текстовое поле
Будет ли элемент иметь вертикальные полосы прокрутки, горизонтальные полосы прокрутки или и те, и другие
ScrollBars (форма)
Откройте вкладку Файл, выберите пункт Параметры, а затем — Настроить ленту.
В группе Основные вкладки установите флажок Разработчик и нажмите кнопку ОК.
Метка указывает назначение ячейки или текстового поля, отображает краткие инструкции либо содержит заголовки или подписи. Метка может также отображать поясняющий рисунок. Используйте метку для гибкого размещения инструкций, выделения текста, а также в тех случаях, когда нецелесообразно использовать объединенные или четко расставленные ячейки.
Текстовое поле представляет собой прямоугольник, в котором вы можете просматривать, вводить и изменять текст или связанные с ячейкой данные. Текстовое поле также может быть статическим и отображать данные, предназначенные только для чтения. Текстовое поле является альтернативой вводу текста в ячейку и позволяет отобразить объект, который можно свободно перемещать. Вы можете также использовать текстовое поле для отображения или просмотра текста, независимого от границ строк и столбцов, что позволяет сохранить на листе структуру сетки и таблицы с данными.
Метка (элемента управления формы):
Метка (элемент ActiveX):
Текстовое поле (элемент ActiveX):
Для создания текстового поля с набором заполнителей для ввода пароля используйте свойство PasswordChar. При этом связанная ячейка или иное место хранения пароля должно быть надежно защищено. Используйте надежные пароли, в которых есть буквы верхнего и нижнего регистра, а также цифры и символы (например Y6dh!et5, а не House27). Пароль должен включать не менее 8 знаков, а лучше 14.
И не забывайте свой пароль. В этом случае мы не сможем помочь вам восстановить его. Office у вас нет ключа для разблокировки. Храните пароли в безопасном месте вне сведений, которые они помогают защитить.
Чтобы создать текстовое поле из нескольких строк с горизонтальной и вертикальной полосами прокрутки, присвойте свойству MultiLine значение True, свойствам AutoSize и WordWrap — значение False, свойству ScrollBars — значение 3, а свойству LinkedCell — адрес ячейки (например, D1), в которой будет храниться текст. Чтобы ввести новую строку, пользователь должен нажать клавиши CTRL+ВВОД или SHIFT+ВВОД; при этом будет создан специальный знак, хранящийся в связанной ячейке.
Вы применяли функцию ВПР, чтобы переместить данные столбца из одной таблицы в другой? Так как в Excel теперь есть встроенная модель данных, функция ВПР устарела. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. Затем можно создать листы Power View или сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они получены из различных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге указываются в списках полей сводной таблицы и Power View.
При импорте связанных таблиц из реляционной базы данных Excel часто может создавать эти связи в модели данных, формируемой в фоновом режиме. В других случаях необходимо создавать связи вручную.
Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
Вы можете отформатировать данные как таблицу или импортировать внешние данные в виде таблицы на новом.
Присвойте каждой из таблиц понятное имя: На вкладке Работа с таблицами щелкните Конструктор > Имя таблицы и введите имя.
Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиента с логикой операций со временем, обе таблицы должны включать дату в одинаковом формате (например, 01.01.2012) и по крайней мере в одной таблице (логика операций со временем) должны быть перечислены все даты только один раз в столбце.
Щелкните Данные> Отношения.
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
В окне Управление связями нажмите кнопку Создать.
В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Вы узнаете, существуют ли связи, при перетаскивании полей из разных таблиц в список полей сводной таблицы. Если вам не будет предложено создать связь, то в Excel уже есть сведения, необходимые для связи данных.
Создание связей аналогично использованию VLOOKUP: вам нужны столбцы, содержащие совпадающие данные, чтобы Excel могли ссылаться на строки в одной таблице с строками из другой таблицы. В примере со временем в таблице Customer должны быть значения дат, которые также существуют в таблице аналитики времени.
В модели данных связи таблиц могут быть типа "один к одному" (у каждого пассажира есть один посадочный талон) или "один ко многим" (в каждом рейсе много пассажиров), но не "многие ко многим". Связи "многие ко многим" приводят к ошибкам циклической зависимости, таким как "Обнаружена циклическая зависимость". Эта ошибка может произойти, если вы создаете прямое подключение между двумя таблицами со связью "многие ко многим" или непрямые подключения (цепочку связей таблиц, в которой каждая таблица связана со следующей отношением "один ко многим", но между первой и последней образуется отношение "многие ко многим"). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
Пример. Связывание данных логики операций со временем с данными по рейсам авиакомпании
Вы можете узнать о связях обеих таблиц и логики операций со временем с помощью свободных данных на Microsoft Azure Marketplace. Некоторые из этих наборов данных очень велики, и для их загрузки за разумное время необходимо быстрое подключение к Интернету.
Нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace. В мастере импорта таблиц откроется домашняя страница Microsoft Azure Marketplace.
В разделе Price (Цена) нажмите Free (Бесплатно).
В разделе Category (Категория) нажмите Science & Statistics (Наука и статистика).
Найдите DateStream и нажмите кнопку Subscribe (Подписаться).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Чтобы импортировать данные, выберите BasicCalendarUS и нажмите Готово. При быстром подключении к Интернету импорт займет около минуты. После выполнения вы увидите отчет о состоянии перемещения 73 414 строк. Нажмите Закрыть.
Чтобы импортировать второй набор данных, нажмите Получение внешних данных > Из службы данных > Из Microsoft Azure Marketplace.
В разделе Type (Тип) нажмите Data Данные).
В разделе Price (Цена) нажмите Free (Бесплатно).
Найдите US Air Carrier Flight Delays и нажмите Select (Выбрать).
Прокрутите вниз и нажмите Select Query (Запрос на выборку).
Нажмите Готово для импорта данных. При быстром подключении к Интернету импорт займет около 15 минут. После выполнения вы увидите отчет о состоянии перемещения 2 427 284 строк. Нажмите Закрыть. Теперь у вас есть две таблицы в модели данных. Чтобы связать их, нужны совместимые столбцы в каждой таблице.
Убедитесь, что значения в столбце DateKey в таблице BasicCalendarUS указаны в формате 01.01.2012 00:00:00. В таблице On_Time_Performance также есть столбец даты и времени FlightDate, значения которого указаны в том же формате: 01.01.2012 00:00:00. Два столбца содержат совпадающие данные одинакового типа и по крайней мере один из столбцов (DateKey) содержит только уникальные значения. В следующих действиях вы будете использовать эти столбцы, чтобы связать таблицы.
В окне Power Pivot нажмите Сводная таблица, чтобы создать сводную таблицу на новом или существующем листе.
В списке полей разверните таблицу On_Time_Performance и нажмите ArrDelayMinutes, чтобы добавить их в область значений. В сводной таблице вы увидите общее время задержанных рейсов в минутах.
Разверните таблицу BasicCalendarUS и нажмите MonthInCalendar, чтобы добавить его в область строк.
Обратите внимание, что теперь в сводной таблице перечислены месяцы, но количество минут одинаковое для каждого месяца. Нужны одинаковые значения, указывающие на связь.
В списке полей, в разделе "Могут потребоваться связи между таблицами" нажмите Создать.
В поле "Связанная таблица" выберите On_Time_Performance, а в поле "Связанный столбец (первичный ключ)" — FlightDate.
В поле "Таблица" выберитеBasicCalendarUS, а в поле "Столбец (чужой)" — DateKey. Нажмите ОК для создания связи.
Обратите внимание, что время задержки в настоящее время отличается для каждого месяца.
В таблице BasicCalendarUS перетащите YearKey в область строк над пунктом MonthInCalendar.
Теперь вы можете разделить задержки прибытия по годам и месяцам, а также другим значениям в календаре.
Советы: По умолчанию месяцы перечислены в алфавитном порядке. С помощью надстройки Power Pivot вы можете изменить порядок сортировки так, чтобы они отображались в хронологическом порядке.
Таблица BasicCalendarUS должна быть открыта в окне Power Pivot.
В главной таблице нажмите Сортировка по столбцу.
В поле "Сортировать" выберите MonthInCalendar.
В поле "По" выберите MonthOfYear.
Сводная таблица теперь сортирует каждую комбинацию "месяц и год" (октябрь 2011, ноябрь 2011) по номеру месяца в году (10, 11). Изменить порядок сортировки несложно, потому что канал DateStream предоставляет все необходимые столбцы для работы этого сценария. Если вы используете другую таблицу логики операций со временем, ваши действия будут другими.
"Могут потребоваться связи между таблицами"
По мере добавления полей в сводную таблицу вы получите уведомление о необходимости связи между таблицами, чтобы разобраться с полями, выбранными в сводной таблице.
Хотя Excel может подсказать вам, когда необходима связь, он не может подсказать, какие таблицы и столбцы использовать, а также возможна ли связь между таблицами. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
После того как вы определили, какая таблица не связана с остальной частью модели, пересмотрите столбцы в ней, чтобы определить содержит ли другой столбец в другом месте модели соответствующие значения.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Для интеграции демографических данных с остальной частью своей модели вам нужно будет найти столбец в одной из демографических таблиц, соответствующий тому, который вы уже используете. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликаты. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что не может быть несколько нулей в столбце подстановок.
Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.
Читайте также: