Как подключить Excel к базе данных SQL

1. Почему соединение лучше склеивания

Перед объяснением: подумайте о последнем отчете, который вы восстановили из экспорта. Сколько из этих минут было потрачено на принятие решений и сколько на перемещение данных из одного окна в другое?

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

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

2. Найдите Power Query, как бы ни выглядел ваш Эксель.

Power Query встроен в Excel уже много лет, но он скрывается под разными именами в зависимости от того, насколько стара ваша версия. На Данные найдите один из них в следующем порядке:

• Получить данные, слева от вкладки «Данные». Это текущее имя и то, что используется в остальной части этой страницы.
• Новый запрос, в старых версиях. Тот же инструмент, то же меню внизу.
• A Получить и трансформировать группа с кнопками, а не одно меню. Тоже тот же инструмент.

Если вы ничего из этого не нашли, возможно, вы используете Excel для Мак или очень старую версию. Мак Эксель имеет меньше разъемов и другое расположение; для более старых версий может потребоваться отдельная установка надстройки Мощность запроса. Советы по экспорту файла, приведенные в разделе 5, — это путь, который работает везде.

3. Подключитесь к SQL Server, Постгрес или MySQL.

Путь Данные, Получить данные, Из базы данных, затем ваш двигатель. В качестве рабочего примера возьмем SQL Server, потому что он есть на большинстве рабочих мест.

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

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

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

4. Загрузите его и выберите в этом диалоговом окне

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

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

Этот список — настоящая награда. Очистка, выполненная в редакторе, произойдет автоматически в следующем месяце; та же самая очистка, сделанная вручную в камерах, должна быть повторена вручную в следующем месяце тем, кто держит файл. Выберите «Преобразовать данные» по умолчанию, даже если единственное, что вы делаете, — это удаляете три ненужных столбца.

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

5. SQLite и пробел в ODBC

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

В меню перечислены соединители, которые Майкрософт решила создать и поддерживать. SQLite не входит в их число, что удивляет людей, которые только что изучили SQL на SQLite, поскольку там разумно началось руководство. Ничего не сломано, и вы не пропустили настройку.

Есть два честных маршрута, и для большинства новичков лучше второй.

Установите драйвер ODBC. ODBC — это уровень трансляции: установите небольшой драйвер для вашей базы данных, зарегистрируйте его один раз, и он появится в Excel в разделе «Получить данные», «Из других источников», «Из ODBC». Он работает хорошо, и для установки программного обеспечения требуется разрешение, что на рабочей машине часто означает билет.

Экспортируйте ответ и подключитесь к файлу. Запустите запрос в инструменте базы данных, сохраните результат в формате CSV и используйте «Получить данные», «Из файла», «Из текста/CSV». Вы сохраняете механизм обновления, поскольку повторный запуск запроса и перезапись того же файла заставляет Обновить работать точно так же, как и раньше. То, от чего вы отказываетесь, — это автоматическая часть.

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

6. Пусть база данных сделает всю тяжелую работу

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

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

Вы можете записать эту сводку непосредственно в соединение: в диалоговом окне SQL Server есть SQL-оператор Коробка именно для этой цели, и все, что вы туда поместите, запускается на сервере, прежде чем что-либо попадет на вашу машину.

SELECT order_date,
       product_category,
       COUNT(*)     AS orders,
       SUM(amount)  AS revenue
FROM sales
WHERE order_date >= '2026-01-01'
GROUP BY order_date, product_category;

Если этот запрос незнаком, GROUP BY и HAVING является руководством для этого. Стоит принять во внимание одно предостережение: запрос, введенный в это поле, представляет собой фиксированный текст, поэтому Excel не сможет сложить обратно в него ваши последующие фильтры. Это честная сделка, когда запрос уже выполняет суммирование, и плохая, если вы вставляете SELECT *.

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

7. Обновляемся, и что ломается на чужих машинах

Обновление Данные, затем обновить всеили щелкните таблицу правой кнопкой мыши и выберите «Обновить». Чтобы сделать это автоматически, щелкните правой кнопкой мыши таблицу, выберите «Свойства таблицы» или «Свойства соединения» и установите флажок «Обновлять данные при открытии файла».

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

Таким образом, подключенная книга — это инструмент для людей, которые могут добраться до источника. Всем остальным вместо этого отправьте им цифры: вставьте готовое резюме в виде значений на новый лист или экспортируйте PDF-файл. Это не обходной путь, это правильный ответ на другой вопрос.

Что пошло не так и как это исправить

Шесть, на долю которых приходится большая часть боли.

«Невозможно подключиться» или ошибка входа. Спросите у коллеги точное имя сервера его рабочего соединения. Угадывание имен серверов — это то, как исчезают дни, и имя часто включает в себя экземпляр или порт, о которых никто не упоминает вслух.

Вашей базы данных нет в меню. Появляются только встроенные разъемы. Используйте ODBC с драйвером или экспортируйте в CSV и подключитесь к нему. Раздел 5.

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

Из кодов исчезли ведущие нули. Столбец почтовых индексов или номеров счетов прибыл в виде цифр. Установите тип столбца «Текст» в редакторе Power Query, а не в ячейках впоследствии. Исправление импорта CSV охватывает ту же проблему, связанную с файлами.

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

Это работает для вас, а не для кого-либо еще. Разрешения, а не файл. Раздел 7.