MS, Libreoffice & Google docs

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

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

Наша форма приобрела аккуратный вид.

Дополним нашу базу данными о компаниях в которых работают наши контакты. Создадим таблицу company с полями [id] – счетчик, [name] – текстовый, [adress] – текстовый. Заполнять таблицу будем импортируя из внешнего источника (файла), можно импортировать данные многими способами (из Excel, файлов Access, файлов других баз данных и др.).

Подготовим простой текстовый файл с набором вымышленных компаний и адресов. Назовем файл company.txt. В первой строчке файла через точку с запятой перечислим название полей, соответствующих нашим полям в таблице company - [name] и [adress]. Перечислим наименования компаний и их адреса отделяя их точкой с запятой.

---------------------------------------------------------- company.txt---------------------------------------------------------------------

name;adress

ООО Мотор;Россия, г. Москва, Космонавтов ул., 76, корп. 1

ЗАО ЖелДор;Россия, г. Москва, Шоссейная ул., 19, корп. 5

ООО ТверьВектор;Россия, г. Тверь, Мирная ул., 87, оф. 30

ОАО СофтОрионЛизинг;Россия, г. Москва, Калинина ул., 12, корп. 2

МКК ГорМобайл;Россия, г. Самара, Луговой пер., 35, оф. 2

ПАО МикроГлавМаш;Россия, г. Москва, Совхозная ул., 12, корп. 2

ПАО Нефть;Россия, г. Уфа, Пушкина ул., дом 32

ОАО МеталВостокОрион;Россия, г. Казань, Озерная ул., 90, корп. 7

ООО Компания РемСбыт;Россия, г. Москва, Вокзальная ул., 86, корп. 6

ПАО ГазВостокМор;Россия, г. Москва, Молодежный пер., 11, корп. 4

ПАО Теле;Россия, г. Москва, Юбилейная ул., 20, корп. 9

ОАО МеталВектор;Россия, г. Хабаровск, Южная ул., 55, оф. 7

ООО Компания ВодСантех;Россия, г. Москва, Заслонова ул., 21, корп. 7

----------------------------------------------------------------------------------------------------------------------------------------------------

Красная линия показывает границу полей.

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

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

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

Завершаем процесс импорта. Переходим в таблицу company и убеждаемся, что наши данные импортировались корректно.

В таблице contacts добавим поле [companyid], в выпадающем списке типов данных выберем Мастер подстановок

Выберем первый пункт – Значения из другой таблицы, далее.

Выбираем таблицу company в качестве источника данных, далее.

Выбираем поле [name] в качестве источника списка, завершим работу мастера соглашаясь на условия по умолчания.

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

Перейдем в уже знакомую нам Схему данных со вкладки Работа с базами данных и отобразим таблицу company. Курсором мыши перетянем поле [companyid] на поле [id] таблицы company. Теперь наши таблицы связаны связью Один ко многим, что означает: одному контакту соответствует одна компания из множества компаний.

Добавим возможность выбора компании для контакта на форме contacts. Перейдем в режим конструктора формы и на панели элементов управления выберем элемент управления – Поле со списком. Поместим его на свободное место области данных.

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

Источник данных таблица company.

Источник списка поле [name].

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

Для удобной работы добавим на форму кнопку сохранения записи. В режиме конструктора из панели элементов управления перетянем элемент управления – Кнопка.

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

Зададим название кнопки как “Сохранить”, завершим работу мастера.

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

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

Добавим в таблицу contacts новое поле [active], которое будет характеризовать активность контакта (активен или не активен). Тип данных установим Логический. Логический (или булевый от англ. boolean) тип данных имеет всего два значения ИСТИНА или ЛОЖЬ (также может обозначаться: как 1 или 0, да или нет, true или false) применяется в тех случаях, где состояние объекта может быть охарактеризовано этими двумя значениями, во многих случаях этого достаточно. В дальнейшем этот признак позволит фильтровать выборку данных и строить логику работы. Например, показать список только активных контактов.

В настройках Свойства поля, которое появляется в нижней части экрана при редактировании поля, в свойство Значение по умолчанию запишем =1, теперь каждая новая запись по умолчанию будет считаться активной, однако все предыдущие записи будут неактивными, так как это поле мы добавили уже после заполнения таблицы данными. В идеальном случае поля таблиц следует проектировать так, чтобы в последующем не приходилось их добавлять, или добавлять по минимуму.

В таблице contacts, поле [active] получило возможность взаимодействия с ним через элемент управления Checkbox,во многих случаях это удобно, хотя в программе за ним скрывается символ 1 или 0, в зависимости от наличия или отсутствия “галочки”.  Мы бы могли удалять записи неактивных контактов, но удалять какие-либо записи в базах данных нужно с особой осторожностью, как правило записи особым образом отмечаются или переносятся, и при необходимости к ним всегда можно вернуться.

Теперь обратим внимание на поля [phone1] и [phone2]. Исходя из опыта мы предположили, что у человека может быть более одного телефонного номера и создали два поля под эти цели, но а если у человека три телефонных номера и даже четыре? Логично предположить и создать под эти цели максимальное количество полей (помним, что вносить все номера в одно поле через запятую противоречит принципу минимальной неделимости), допустим создадим десять полей для хранения десяти телефонных номеров. И делать мы этого не будем по причине того, что система управления базами данных резервирует память под хранение данных и даже при наличии одного человека с десятью телефонными номерами большое количество пустых мест (рисунок ниже) будет замедлять работу нашей базы, когда она начнет разрастаться данными.

Создадим таблицу phones.

В ней же создадим ключевое поле [id] с типом данных Счетчик, поле [userid] – тип данных Числовой и [phone] – тип данных Текстовый. Поле [userid] является внешним ключевым полем, сюда мы будем записывать значение поля [id](которое соответствует конкретному человеку) из таблицы contacts и его номер телефона, столько раз сколько номеров телефонов у него есть.

Перенесем номера телефонов из таблицы contacts в таблицу phones и удалим поля [phone1] и [phone2].

На панели инструментов откроем вкладку Работа с базами данных и вызовем Схему данных. В конструкторе Схемы данных вызовем Отобразить таблицу и выберем из списка таблиц таблицу contacts и phone. Мы видим модели наших таблиц с соответствующими полями.

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

Теперь наши таблицы связаны отношением – Один ко многим, это значит, что один человек из таблицы contacts может иметь много телефонных номеров в таблице phones. Таким образом наша таблица будет расти вниз, а не в ширь, как это предполагалось у нас изначально.

Откроем форму contacts из панели объектов базы. Теперь наши поля формы [phone1] и [phone2] ссылаются на несуществующие поля таблицы contacts, все верно, мы их удалили.

Откроем форму contacts в режиме Конструктора уже знакомым для нас способом. Здесь мы имеем возможность в графическом режиме изменять размеры и расположение наших полей, а так же менять их свойства, и много чего другого. Выделим область расположения “битых” полей и удалим их.

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

В появившемся Мастере подчиненных форм выберем источником подчиненных данных Имеющиеся таблицы и запросы, далее во втором окне из выпадающего списка таблиц выберем таблицу phones, из левого списка полей таблицы phones выберем поле [phone] и переместим его в правый список соответствующей кнопкой.

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

На панели инструментов, в подразделе Сервис нажмем Добавить поля и перетащим поле [active] на макет формы. Сохраним изменения и выйдем из режима конструктора.

Теперь на форме contacts, в окне подчиненной таблицы,  перемещаясь по записям контактов сможем видеть и добавлять необходимое количество телефонов для конкретного контакта, все добавления будут записываться в таблицу phones. Добавим новый телефон для первого контакта и в таблице phones появится еще один телефон, заметьте поле [userid] таблицы phones заполняется автоматически соответствующим [id] (которое идентифицирует конкретного пользователя) из таблицы contacts. Также у нас есть возможность изменять “галочкой” активность контакта и эти изменения тоже будут отражаться в таблице contacts.

А что если бы была такая программа, где бы мы централизованно и структурированно хранили наши однотипные данные и при необходимости вставляли их в типовые шаблоны документов? Есть такая программа и это система управления базами данных (СУБД) Microsoft Access из пакета Microsoft Office. Простыми словами это программа которая хранит ваши данные в таблицах, в удобном для последующей работы виде. В отличии от Excel в базах данных более строгие условия по структуре данных и работе с ними, что дает преимущества при их использовании. Ну не думает же вы, что ваш банковский счет ведется в Excel.

Если ваш пакет офиса не включает программу MS Access придется ее доустановить.

Версия 2010 и выше принципиально не отличаются, по крайней мере для наших ознакомительных целей.

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

_____________________________________________________________________________________________________

_____________________________________________________________________________________________________

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

С таблицей мы можем работать в режиме “Таблица” и “Конструктор”

Перейдем в режим “Конструктор”, при этом Access попросит нас задать имя таблицы. Помним, что такие вещи лучше именовать латиницей и без пробелов. Назовем нашу таблицу – contacts. Правилом хорошего тона является именование таблиц именем, которое явно отражает то, что в них хранится.

В реляционных базах данных приняты следующие правила и стандарты:

-поле содержит отдельную сущность (характеристику) описываемого объекта - [фамилия] [имя] [дата_рождения] [телефон] [адрес_эл_почты]

-строки таблиц называются записями, хотя и строкой их называть допустимо

-поля должны быть атомарными (неделимыми), то есть – фамилия, имя и отчество должны быть в разных полях

-каждое поле имеет только один тип данных (текст, дата, число)

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

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

Как уже было сказано – поле имеет Тип данных. Его мы выбираем в зависимости от того, какими данными мы будем характеризовать наши сущности. Для поля – [фамилия] мы явно выберем текст, для поля [дата_рождения] мы явно выберем дату/время. И если для поля - [фамилия] мы выберем число, то при заполнении таблицы программа не даст нам записать текст, а только числовое значение, а нам этого не надо, значит правильно выбираем типы данных.

Создадим поля нашего справочника контактов:

[id] – тип данных Счетчик (ключевое поле)

[name] – тип данных Текстовый

[surname] – тип данных Текстовый

[email] – тип данных Текстовый

[phone1] – тип данных Текстовый

[phone2] – тип данных Текстовый

[regdate] – тип данных Дата/время

При выборе типа данных, в нижней части окна конструктора мы можем настроить дополнительные свойства поля. Например, мы можем вставлять текущую дату в поле [regdate] (дата регистрации) при создании записи в таблице contacts. При нажатии на кнопку свойства – “Значение по умолчанию” откроется Построитель выражений.

Выберем из встроенных функций Даты и времени функцию Date(). Сохраним изменения в Построителе выражений и конструкторе таблиц и перейдем в режим заполнения таблицы.

Наша таблица готова к заполнению данными.

Заполним таблицу вымышленными персонажами. Поле [id] и [regdate] заполняются автоматически.

Создадим для нашей таблицы форму для заполнения. На вкладке Создание вызовем Мастер форм и в диалоговом окне мастера выберем те поля, которые нам понадобятся на форме, выберем все кроме полей [id] и [regdate], они у нас заполняются автоматически. Жмем далее и соглашаемся на предложенные мастером значения по умолчанию.

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

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

Перейдем в нашу таблицу и увидим добавление новой записи.

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

Теперь мы можем работать с этим файлом удобным для нас способом, а наши данные надежно хранятся в базе данных. Это лишь знакомство с системой Access.

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

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

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

Системные источники. Те самые 1с выгрузки, как и из любой другой системы/пользователя. Здесь два правила: а) хранить, заменяя или накапливая, в отведенной папке б) один источник всегда (обязательно) имеет неизменную структуру. Это залог успеха при передаче данных в Личный обработчик. Не обеспечите этого - продолжите бесцельно тратить годы, ровно как по Островскому)

Презентационный Продукт. Ваши релизы, наполняемые информацией из Личных обработчиков. Вот вам краски (УФ), софт - как хочешь разукрашивай (с)

Рабочее место
Это ваша книга, его необходимо обустроить - вывести в панель быстрого доступа а) очень часто используемые команды из разных закладок б) сигнализацию, например состояние пересчёта вычислений
Включите бесплатную надстройку Power Query (если у вас не версия с уже встроенной на закладке Данные) - это для работы с запросами к Системным источникам
Есть платные русифицированные решения, прежде всего Надстройки. Советы как тратить деньги неблагодарное дело, но речь об обустройстве Рабочего места. Коротко о единственной приобретенной 7 лет назад за 500 рублей надстройке, которая пздц как помогает каждый день. Plex
За рабочим местом нужно следить. Чистить от лишних стилей, напрочь забыть об условном форматировании (иногда актуально для Презентационного Продукта), удалять форматы в неиспользуемых областях, сжимать фоточки с котиками, при возможности сохранять результаты запросов как подключения без выгрузки данных на лист, не плодить десятки сводных таблиц.
То есть, обеспечить быстродействие книги

Самообразование
Здесь один путь, по моему опять же скромному мнению. Есть одна проблема / вопрос к себе / подозрение, что можно что-то автоматизировать или решить гораздо менее затратным путём - не спрашиваем у коллег, а ищем решение / ответ / подтверждение в сети.
Находим, применяем, тестируем, ошибаемся, уточняем, тестируем, проверяем, запоминаем, применяем на постоянной основе. Не останавливаемся, со временем проходим тот же путь для ранее найденных решений. Бенефитов два: а) Excel в вашем исполнении все краше и краше б) знания
На последок расскажу про Морфиуса, который покажет - как глубока кроличья нора. Николай Павлов, а именно его продукт Планета Эксель. Лучшее, что есть в русском сегменте сети. Форум заслуживает особого внимания. Но не забывать - главное слово Самообразование