Почему сводная таблица не видит столбцы

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Устранение неполадок в связях между таблицами

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

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

Сообщение. Связи не были обнаружены

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

Алгоритм обнаружения связей зависит от внешнего ключевого столбца, имя которого схоже с именем первичного ключевого столбца. Если имена столбцов недостаточно похожи, рекомендуется открыть окно Power Pivot и вручную создать необходимые связи между таблицами.
Типы данных могут не поддерживаться. Если любая из таблиц, используемых в сводной таблице, содержит столбцы только неподдерживаемых типов данных, то связи обнаружить невозможно. В этом случае необходимо создать связи между активными таблицами в сводной таблице вручную в диалоговом окне Создание связи. Дополнительные сведения см. в разделе Создание связи между двумя таблицами.

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

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

Отсутствует допустимая связь между таблицами

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

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

При автоматическом обнаружении созданы неверные связи

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

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

Изменение исходных данных сводной таблицы

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

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

Изменение исходных данных

Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных». Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из следующих действий:

Диалоговое окно

чтобы использовать другое подключение

Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение». Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?

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

Изменение источника данных сводной таблицы на другую таблицу Excel или диапазон ячеек

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

Что делать, если подключение отсутствует в списке?

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

Диалоговое окно

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

Вкладка

Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Изменение исходных данных

Щелкните Отчет сводной таблицы.
На вкладке « Параметры » в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных». Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из указанных ниже действий.
- Чтобы использовать другую таблицу или диапазон ячеек Excel, щелкните «Выбрать таблицу или диапазон «, а затем введите первую ячейку в текстовом поле «Таблица / диапазон». Кроме того, нажмите кнопку «Свернуть диалоговое окно чтобы временно скрыть диалоговое окно, выделите начальную ячейку на листе, а затем нажмите кнопку «Развернуть диалоговое окно» .
Чтобы использовать другое подключение, выберите «Использовать внешний источник данных», а затем нажмите кнопку «Выбрать подключение». Отобразится диалоговое окно «Существующие подключения».
В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть».

Что делать, если подключение отсутствует в списке?

Диалоговое окно

Вкладка

Выберите нужное подключение и нажмите кнопку Открыть.
Выберите вариант Только создать подключение.
Щелкните пункт Свойства и выберите вкладку Определение.
Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.

Изменение исходных данных

Дополнительные сведения о поддерживаемых источниках данных см. в разделе «Импорт и формирование данных в Excel для Mac (Power Query).

Щелкните Отчет сводной таблицы.
На вкладке « Анализ» в группе данных щелкните «Изменить источник данных» и выберите команду «Изменить источник данных». Отобразится диалоговое окно «Источник данных измененной сводной таблицы».
Выполните одно из указанных ниже действий.
Чтобы изменить источник данных сводной таблицы на другую таблицу Excel или диапазон ячеек, щелкните «Выбрать таблицу или диапазон«, а затем введите первую ячейку в текстовом поле «Таблица/диапазон» и нажмите кнопку «ОК».
Чтобы использовать другое подключение, выполните следующие действия.
1. Щелкните » Использовать внешний источник данных«, а затем выберите «Выбрать подключение». Отобразится диалоговое окно «Существующие подключения».
2. В раскрывающемся списке «Показать» в верхней части диалогового окна выберите категорию подключений, для которых нужно выбрать подключение, или выберите «Все существующие подключения» (по умолчанию).
3. Выберите подключение в списке «Выбор подключения» и нажмите кнопку » Открыть». Что делать, если подключение отсутствует в списке?
Примечание: Если вы выберете подключение из категории «Подключения» в этой книге, вы будете повторно использовать существующее подключение или поделиться с этим подключением. Если выбрано подключение из файлов подключения в сети или файлов подключений в этой категории компьютера, файл подключения копируется в книгу в качестве нового подключения к книге, а затем используется в качестве нового подключения для отчета сводной таблицы.

Что делать, если подключение отсутствует в списке?

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

Если сводная таблица основана на подключении к диапазону или таблице в модели данных, сменить таблицу модели или подключение можно на вкладке Таблицы. Если же сводная таблица основана на модели данных книги, сменить источник данных невозможно.
1. Выберите нужное подключение и нажмите кнопку Открыть.
2. Выберите вариант Только создать подключение.
3. Щелкните пункт Свойства и выберите вкладку Определение.
4. Если файл подключения (ODC-файл) был перемещен, найдите его новое расположение в поле Файл подключения.
5. Если необходимо изменить значения в поле Строка подключения, обратитесь к администратору базы данных.
Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

СВОДНЫЕ ТАБЛИЦЫ. ПОЧЕМУ В РЕШЕНИЯХ EXCELSOFT ОНИ ПОЧТИ НЕ ИСПОЛЬЗУЮТСЯ

Сводные таблицы Excel — отличный инструмент для анализа данных. Они позволяют быстро и просто обрабатывать большие массивы однородных данных и представить результаты в различных разрезах и форматах.

Конечно, каждый инструмент предназначен для своих задач. Например, сводные таблицы — идеальны для анализа данных о продажах. Не случайно по-моему 99% примеров применения сводных таблиц — это именно об этом.

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

1. Критичность к исходным данным

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

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

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

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

Здесь доступны лишь простейшие арифметические операции и функций не требующие обязательного применения ссылок на другие ячейки и именованные объекты. Ссылки на другие ячейки и поименованные диапазоны использовать нельзя!

Поэтому если вам нужно рассчитать что-то, чего изначально нет в выгруженных данных – придется делать вычисляемые столбцы в исходных данных и уже готовые результаты расчетов «тащить «в сводную.

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

4. Сводная таблица имеет довольно убогий внешний вид.

Конечно, можно настроить форматы чисел, ширины столбцов. Но при обновлении сводной таблицы все ваши настройки слетят.

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

Конечно проблема решается созданием таблиц-оболочек в которые с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будут подтягиваться данные из сводных таблиц. Но тогда пропадают основные достоинства сводной — простота и быстрота.

5. Для большинства отчетов придется строить сложную модель данных .

Быстро и просто сводная таблица строится для данных, находящихся на одном листе, например данные о продажах. Но, например, в программе ES: Финансовое планирование и учет управленческий баланс в формате план-факт собирается на основании данных 6 листов . Для сборки такой сводной надо строить модель данных c использованием надстройки Power Pivot. А это уже не совсем простая задача и не за пару минут.

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

Но основная причина, по которой я нечасто использую сводные таблицы в программах ExcelSoft в том, что строгие требования качеству и к структуре представления исходных данных повышают трудоемкость их ввода в разы и делают невозможным использование некоторых юзабилити- «фишек» программ ExcelSoft.
1. Исходные данные для сводных таблиц не должны содержать пустых строк. Но работать со сплошным массивом цифр визуально очень тяжело. Поэтому в программах ExcelSoft можно визуально разделять данные строками с произвольными текстовыми данными, оставлять пустые строки. Работоспособность программы от этого никак не пострадает.
2. В исходных данных для сводной не должно быть пустых ячеек, иначе можно получить некорректный результат. Но тогда пользователю придется аккуратно заполнять все ячейки таблицы.
3. Программы ExcelSoft стремятся обеспечить пользователям минимальную трудоемкость при вводе данных. Там, где можно обойтись без ввода данных – обходимся.
4. Для сводных все данные в одной колонке должны быть одного типа. Программы ExceSoft позволяют в одной колонке использовать и текстовые и числовые значение. Иногда так надо. Например, в журналах учета в колонке Дата можно вводит названия закладок для быстрого перемещения по таблице.
5. Для создания сводной таблицы требуется, чтобы в первой строке исходных данных были заголовки столбцов. В программах ExcelSoft первой строкой исходных данных может быть строка суперфильтра (инструмент, который позволяет быстро фильтровать данные), строка для расчета промежуточных итогов или строка с номерами столбцов (для поиска ошибок). На рисунке ниже представлена реальная таблица для планирования доходов и расходов из которой потом формулами массива собирается плановый бюджет доходов и расходов. Здесь есть все вышеперечисленные примеры вольного заполнения данных.
В таблице имеются:
- визуальные разделители (пустые строки);
- закладки с помощью которых можно быстро перемещаться по таблице, например «Транспортные 2019», «Коммунальные 2019» в колонке Дата;
- данные разных типов в одной колонке (например колонка НДС,%);
- строка суперфильтра (оранжевая строка);
- пустые ячейки в табличной части;
Тем не менее, все работает. В случае со сводной таблицей такие вольности недопустимы. Пользователю пришлось бы аккуратно заполнить все ячейки в колонках.

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

Почему сводная таблица не видит столбцы