Мы смотрел о ловушках использования приложения для работы с электронными таблицами, такого как Excel, для хранения списков данных. Поначалу этот подход может показаться лучшим решением, но вы можете столкнуться с проблемами при совместном использовании этих данных с несколькими пользователями, проверке содержимого или даже при навигации по вашим данным. Почему? Потому что вы используете инструмент, не предназначенный для этой работы.
Теперь мы рассмотрим воображаемый (но типичный) случай бизнеса, использующего список на основе электронной таблицы, и посмотрим, как его можно преобразовать в приложение базы данных для решения таких проблем.
Как книги выходят из-под контроля
Наш список начинался с простой записи проектов, предпринятых для клиентов. По мере роста компании увеличивалось и количество клиентов, имена и контактные данные которых были добавлены в рабочую книгу. Кроме того, требовался способ записи того, что различные сотрудники делали в этих проектах, поэтому в эту рабочую книгу было добавлено еще больше данных.
В этот момент табличный подход стал неработоспособным: слишком много людей пытались поддерживать его в актуальном состоянии, часто в одно и то же время. Компания попыталась ввести ротацию, чтобы люди по очереди обновляли рабочую книгу, но это означало, что некоторые задачи были забыты до того, как они были записаны.
В конце концов, люди создают свои собственные рабочие книги, чтобы отслеживать свои задачи, иногда не забывая копировать данные в основную книгу в конце недели. Сотрудники разработали свои собственные сокращения для этих книг, а некоторые изменили форматирование и порядок столбцов в соответствии со своим стилем работы. Копирование этих данных в основную книгу привело к ужасному беспорядку.
Это может быть выдуманный пример, но я действительно видел все эти практики в реальной жизни. Давайте подробнее рассмотрим некоторые проблемы, возникающие при использовании этого метода работы.
Множество проблем
Вы можете увидеть первый лист нашей воображаемой таблицы. В первом столбце указывается название проекта, к которому относится каждая запись. Однако некоторые из этих имен длинные, поэтому у сотрудников, возможно, возникло искушение использовать сокращения; в результате вкрались опечатки. Это затрудняет увязку задач, которые относятся к какому проекту. Решение не должно быть сложным: вы можете выбрать короткое название для каждого проекта, с которым все согласны, или дать каждому проекту идентификационный номер и автоматически преобразовать его в название проекта.
Аналогичная проблема со столбцом 'Начато'. Некоторые ячейки содержат дату, но другие записывают только месяц - и одна или две записи просто говорят «Да». Excel поддерживает проверку данных, поэтому можно гарантировать, что определенные ячейки всегда содержат данные определенного типа, но когда электронная таблица разрабатывается специальным образом, она редко используется.
На этом этапе метод электронных таблиц становится неработоспособным: слишком много людей пытались поддерживать его в актуальном состоянии.
У вас не будет этой проблемы в приложении базы данных, поскольку тип данных поля будет фиксирован с самого начала. Если вы не знаете точную дату начала работы, вы можете использовать первое число месяца или 1 января, если вы знаете только год. Если проект еще не запущен, вы можете оставить поле пустым - NULL в терминах базы данных. Если вы знали, что проект был начат, но не знали, когда, вы можете использовать дату, которая обычно была бы невозможна для ваших данных, например 1/1/1900. Сразу становится легко сортировать проекты и получать хронологический обзор активности.
Более тонкий вызов представляет столбец с названием «Клиент». Записи в этом столбце не связаны ни с чем другим в книге, но на листе 1 есть список клиентов, к которому, вероятно, он и относится. Хранение нескольких списков одних и тех же элементов, имеющих разные имена, сбивает с толку. Вам необходимо уточнить наименования и выбрать однозначное название для этой организации: они клиенты или покупатели?
Столбец «Статус» - это еще один столбец, в котором не было проверки, поэтому люди снова решили писать все, что захотят. Лучше составить краткий список всех допустимых значений.
Второй лист - Лист 1 - не менее проблематичен. Во-первых, название листа не является описательным. На самом деле он содержит список клиентов, но он не отформатирован в виде таблицы в Excel: адрес находится в одном поле, что ограничивает ваши возможности использовать встроенные инструменты Excel для поиска или сортировки. Вы можете, например, отфильтровать адреса, содержащие Cardiff, но результаты также будут включать адреса на Cardiff Road в Ньюпорте.
Когда дело доходит до адресов, лучше всего использовать отдельные поля для почтового индекса, округа, города и улицы (хотя информация о округе не является обязательной для адресов в Великобритании - см. Без округов, пожалуйста, мы британцы). Улица должна содержать все, чего нет в других частях адреса.
Есть поле 'Контакт', в котором тоже есть проблемы. Если у нас есть несколько контактов в рамках бизнеса с одним клиентом, все их имена помещены в это поле, а их номера телефонов и адреса электронной почты аналогичным образом помещены в другие поля. Разделить их будет непросто, особенно если в поле «Контакт» есть три имени, но только два телефонных номера.
Последний столбец на этом листе озаглавлен «Последний контакт»: сотрудники должны обновлять его каждый раз, когда они вступают в контакт с клиентом. Поскольку эта информация является дополнительной вещью, которую нужно запомнить сотруднику, и нет никакой гарантии, что они запомнят ее - тем более, что она спрятана на втором листе, - она ненадежна. Это действительно то, что компьютер должен отслеживать автоматически.
Наконец, мы подошли к листам задач, в которых подробно описаны задачи и комментарии для каждого работника. У них разные названия и разные столбцы в разных порядках. Хотя для отдельных пользователей имеет смысл вводить свои данные на своих собственных таблицах, отсутствие согласованности затрудняет сопоставление и анализ данных. Например, когда менеджер хочет увидеть, какая работа была проделана по каждому проекту, все задачи необходимо вручную скопировать с отдельных листов в один список, прежде чем их можно будет отсортировать и составить отчет.
Создание вашей базы данных
Чтобы решить эти проблемы, потребуется определенная работа, возможно, несколько дней. Поскольку пользователям, вероятно, придется продолжать использовать старую систему, пока мы создаем новую, лучше всего сделать копию существующих книг, чтобы с ними работать. Это означает, что нам нужно задокументировать каждый шаг преобразования данных, чтобы мы могли быстро сделать это снова, когда придет время перейти на новую систему.
Первое, что вам нужно сделать, это очистить данные в книге Excel. Использование функции «Найти и заменить» может помочь, и вам следует удалить любой столбец или строку, не содержащую данных (за исключением строки заголовка столбца, которую необходимо сохранить). Добавьте столбец идентификатора на каждый лист в столбце A и заполните его инкрементными числами, набрав 1 в первой ячейке, выделив данные в нижней части (Shift + End, Down), а затем используя команду Fill Down (Ctrl + D ). Создайте главный список имен проектов и везде, где записано имя проекта, используйте функцию VLookup (), чтобы подтвердить его главный идентификационный номер; если номера нет, значит, в ваших данных есть несоответствие.
Как только ваши данные станут чистыми, пора создать новую базу данных для их хранения. Мы будем использовать Access 2013, потому что в нашем теоретическом примере он доступен всем нашим пользователям через нашу подписку на Office 365. Когда вы создаете новую базу данных Access, у вас есть выбор: создать ее как веб-приложение Access или базу данных Access Desktop. Веб-приложения имеют упрощенный интерфейс и могут использоваться только в том случае, если у вас есть Office 365 с SharePoint Online или SharePoint Server 2013 со службами Access и SQL Server 2012. Мы будем использовать традиционную базу данных рабочего стола, поскольку она предлагает больше возможностей и больший контроль над Пользовательский опыт.
Выберите создание новой базы данных рабочего стола и назовите ее: Access создает новую таблицу с именем Таблица 1 и помещает вас в представление «Дизайн» с одним столбцом с именем ID. Здесь вы можете создать таблицы, которые понадобятся вам в базе данных. Каждая таблица должна иметь поле идентификатора (автоматически увеличивающееся целое число), но во избежание путаницы лучше дать ей более информативное имя. В таблице Projects это будет ProjectID, CustomerID в таблице Customers и так далее.
Вы можете установить тип данных для каждого созданного столбца, и вам нужно дать каждому столбцу имя и установить любые другие свойства и форматирование в соответствии с полем. Как и в случае с полем ID, убедитесь, что имена столбцов дают понять, какие данные должны быть в поле - поэтому, например, используйте ProjectName, а не просто Name, DueDate, а не Due. Вы можете использовать кнопку «Имя и заголовок» на ленте, чтобы создать сокращенный заголовок, а также явное имя. Вы можете использовать пробелы в именах столбцов, но при написании запросов и отчетов вам придется заключать их в квадратные скобки.
Хотя пользователям имеет смысл вводить свои данные на собственных таблицах, отсутствие согласованности затрудняет анализ.
Установите форматирование столбцов, таких как PercentageComplete, на Percent, а для дат - на ShortDate, а также максимальную длину текстовых полей на разумное значение, иначе все они будут содержать 255 символов. Помните, что некоторые слова (например, Дата) зарезервированы, поэтому вы не можете использовать их в качестве имен столбцов: вместо этого используйте TaskDate или что-то еще более описательное.
Когда дело доходит до столбцов, в которых вы хотите найти значение в другой таблице (например, в столбце «Клиент» в таблице «Проекты»), определите эти другие таблицы в Access, прежде чем добавлять столбец подстановки. Когда дело доходит до статуса, самый простой вариант - просто ввести значения, которые будут отображаться в раскрывающемся списке, но это затрудняет добавление или редактирование списка возможных значений позже. Если вы не имеете дело с коротким списком, в котором возможные значения вряд ли изменятся - например, поле, записывающее чей-то пол, - лучше создать другую таблицу для таких записей, как ProjectStatus. Это позволяет вам легко добавлять дополнительные параметры в список в будущем без изменения программирования.
Улучшения
Пока мы разрабатываем нашу базу данных, мы можем внести улучшения по сравнению со старым способом работы на основе электронных таблиц. Одна из жалоб наших пользователей на свои книги Excel заключалась в том, что каждая задача содержала только одну ячейку для комментариев, и иногда им нужно было сделать более одного комментария к задаче - или супервизору нужно было сделать комментарий о задаче, а затем пользователю ответьте на это. Запихивание всего в одну ячейку затрудняло понимание, когда и кем были сделаны комментарии. Мы можем добиться большего, создав отдельную таблицу для комментариев, связанную с таблицей задач. Таким образом, каждая задача может иметь столько комментариев, сколько необходимо, с отдельными полями для даты, имени пользователя и текста каждого из них.
Еще одно усовершенствование, которое мы можем сделать, - это настроить такие записи, как ProjectStatus, для отображения в определенном порядке, а не в алфавитном порядке - например, вы можете захотеть, чтобы Completed находился внизу списка. Для этого добавьте столбец DisplayOrder и используйте его для сортировки списка поиска. Не поддавайтесь соблазну использовать поле идентификатора; при этом любые новые записи могут попадать только в конец списка.
Чтобы наши данные оставались чистыми, мы можем пометить поля, которые пользователь должен заполнить, как обязательные, и добавить проверку, чтобы гарантировать, что введенные данные находятся в правильной форме. Вы можете облегчить жизнь, установив разумные значения по умолчанию: для поля CommentDate в таблице комментариев может быть установлено значение по умолчанию = Date (), которое автоматически установит сегодняшнюю дату при создании нового комментария. Вы можете использовать проверку вместе со столбцом Withdrawn в таблице (логическим), чтобы пользователи не добавляли новые записи с определенными значениями. Это позволяет сохранить исторические значения, которые раньше были действительными, но больше не используются. Все эти функции можно найти в Инструментах для таблиц | Вкладка «Поля» на ленте или в свойствах поля в режиме конструктора таблиц.
Импорт ваших данных
После того, как ваши таблицы настроены, вы можете использовать External Data | Импорт и ссылка | Нажмите кнопку Excel на ленте, чтобы добавить данные из книги Excel в таблицы в базе данных Access. Перед началом работы сделайте резервную копию вашей пустой базы данных Access на случай, если что-то пойдет не так, и начните с заполнения небольших таблиц вручную, если это необходимо. Сделайте еще одну резервную копию, как только это будет сделано, чтобы вы могли вернуться к этому моменту, если что-то пойдет не так в следующих шагах.
Теперь импортируйте основные таблицы, которые не зависят от каких-либо других таблиц, например «Клиенты», прежде чем закончить работу с таблицами, которые имеют связи, например «Проекты» и «Задачи». Если вы измените порядок и переименуете столбцы в книге Excel, чтобы они максимально соответствовали полям в базе данных Access, у вас не должно возникнуть проблем с импортом данных. Не забудьте записывать все, что вы делаете, чтобы вы могли повторить это позже, если вам нужно снова преобразовать данные.
После импорта данных таблицы в представлении таблицы должны работать так же, как и рабочие листы Excel, но с гораздо лучшей проверкой, поиском и сортировкой данных. При желании теперь вы можете приступить к разработке новых форм и отчетов на основе этих данных: например, форма Master / Detail для проектов может отображать данные одного проекта в верхней части формы и сетку задач для этого проект внизу.
Вы также можете настроить форму «Мои задачи», в которой перечислены все невыполненные задачи для текущего пользователя, и отчет «Просроченные задачи», в котором перечислены все невыполненные задачи для всех пользователей, срок выполнения которых истек.
Нет округов, пожалуйста, мы британцы
Если вы храните адреса в своей базе данных, важно понимать, какая информация вам действительно нужна. Хотя информация об округе может быть полезна для маркетинга - и может потребоваться для некоторых зарубежных адресов, - она больше не используется официально в британских адресах.
что такое кеш жесткого диска
Причина в том, что почтовые адреса Великобритании основаны на концепции почтового города, где почта для вас отправляется и сортируется, прежде чем она будет доставлена к вашей двери. Не все города или деревни обслуживаются почтовыми городками в одном и том же графстве - например, Мельбурн (в Кембриджшире) получает почту через Ройстон (в Хартфордшире), поэтому указание округа в адресе не обязательно никому поможет.
Чтобы избежать путаницы, почтовое отделение перестало использовать округа в адресах еще в 1996 году, полагаясь вместо этого на информацию о почтовом индексе, а к 2016 году оно планирует удалить названия округов из файла данных псевдонимов дополнительной адресной информации. Таким образом, если вы включите округ в адрес Великобритании, он будет просто проигнорирован.
