Невозможно создать или использовать ссылку на диапазон данных так как она слишком сложна excel
Обновлено: 04.07.2024
например. Я назвал A10 до A20 как возраст, теперь как я могу получить Возраст[5], который совпадает с A14.
Я могу написать "=A14", но мне нравится писать "=Age$5 " или что-то в этом роде.
Вы можете использовать Excel Index функция:
" знаете ли вы, есть ли способ сделать эту работу с относительными выборками, чтобы формула могла быть "перетащена"/применена через несколько ячеек в одном столбце?"
чтобы сделать такой выбор относительным, просто используйте формулу строки для номера строки в Формуле индекса и формулу столбца для номера столбца в Формуле индекса. Чтобы сделать это более ясным, вот пример:
предполагая, что именованный диапазон начинается с A1, эта формула просто индексирует этот диапазон по строкам и номер столбца ссылочной ячейки и поскольку эта ссылка относительна, она изменяется при перетаскивании ячейки вниз или в сторону, что позволяет легко создавать весь массив ячеек.
добавьте столбец слева, чтобы B10 - B20 был вашим возрастом именованного диапазона. Установите A10 в A20 так, чтобы A10 = 1, A11= 2. A20 = 11 и дайте диапазону A10 для A20 имя, например AgeIndex. Затем 5-й элемент можно найти с помощью формулы массива " = sum (Age * (1 * (AgeIndex = 5) ) ". Поскольку это формула массива, вам нужно нажать shift + ctrl + return, чтобы она работала, а не просто возвращалась.
есть несколько разных способов сделать это:
1) имитировать таблицы Excel с использованием именованного диапазона
в вашем примере вы назвали диапазон A10:A20 "возраст". В зависимости от того, как вы хотите ссылаться на ячейку в этом диапазоне, вы можете использовать =INDEX(Age, 5) или если вы хотите ссылаться на ячейку в диапазоне "Возраст" , которая находится в той же строке, что и Ваша формула, просто используйте:
это имитирует относительные ссылочные функции встроенный в таблицы Excel, но является альтернативой, если вы не хотите использовать таблицу.
если именованный диапазон является целым столбцом, формула упрощает как:
2) используйте таблицу Excel
кроме того, если вы настроили это как таблицу Excel и введите "возраст" в качестве заголовка столбца возраста, то ваша формула в Столбцах справа от столбца возраста может использовать формулу, подобную этой:
Я был готов использовать что - то вроде этого на листе, где все строки идентичны и обычно ссылаются на другие ячейки в одной строке, но поскольку формулы становятся сложными, ссылки на другие столбцы становятся трудными для чтения. Я попробовал трюк, приведенный в других ответах, например, столбец A с именем "Sales" я могу ссылаться на него как INDEX(Sales;row()) но я нашел, что это слишком долго на мой вкус.
однако в этом конкретном случае я обнаружил, что с помощью Sales один работает так же хорошо - Excel (2010 здесь) просто получает соответствующую строку автоматически.
похоже, что он работает и с другими диапазонами; например, скажем, у меня есть значения в A2:A11 который я называю Sales , Я могу просто использовать =Sales*0.21 на B2:11 и он будет использовать то же значение строки, выдавая десять разных результатов.
Я также нашел хороший трюк на на этой странице: именованные диапазоны также могут быть относительными. Возвращаясь к вашему первоначальному вопросу, если ваша ценность "возраст" в столбце A и предполагая, что вы используете это значение в формулах в той же строке, вы можете определить возраст как $A2 вместо $A$2, так что при использовании в B5 или C5, например, он будет фактически ссылаться на $A5. (Диспетчер имен всегда показывает ссылку относительно выбранной ячейки)
Некоторые пользователи получают «Ссылка на источник данных недействительна”При попытке создать сводную таблицу из диапазона в Excel. Сообщается, что эта конкретная ошибка возникает в нескольких версиях Excel и Windows, поэтому проблема не связана с конкретной версией ОС или Excel.
Ссылка на источник не верна
Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel?
Для достижения наилучших результатов следуйте методам в том порядке, в котором они представлены. В конечном итоге вы должны наткнуться на исправление, которое будет эффективно в вашем конкретном сценарии.
Способ 1: удаление скобок из имени файла
Если этот сценарий применим к вашей текущей ситуации, вы сможете решить проблему, изменив имя файла .xlsx, удалив запрещенные символы. Вот краткое руководство по этому:
- Закройте окно Excel, которое в данный момент использует файл. Если файл используется, вы не сможете его переименовать.
- Используйте проводник, чтобы перейти к местоположению файла Excel. Как только вы попадете туда, щелкните по нему правой кнопкой мыши и выберите Переименовать.
- Далее, удалите скобки из имени файла, так как таблица Pivots не настроена для их поддержки.
Снятие скобок с имени файла - Попытайтесь заново создать сводную таблицу и посмотрите, не возникла ли еще ошибка.
Если вы все еще сталкиваетесь с Ссылка на источник данных недействительна Ошибка или этот метод не применим к вашему конкретному сценарию, перейдите к следующему способу ниже.
Способ 2: сохранение файла на локальном диске
Эта проблема также может возникнуть, если вы открываете файл непосредственно с веб-сайта или непосредственно из вложения электронной почты. В этом случае файл будет открыт из временного файла, который в конечном итоге вызовет Ссылка на источник данных недействительна ошибка.
Если этот сценарий применим к вашему текущему сценарию, вы сможете решить эту проблему, предварительно сохранив файл Excel на локальном диске. Итак, прежде чем пытаться создать сводную таблицу, перейдите к Файл> Сохранить как и сохраните файл в физическом месте (на локальном диске).
Сохранение файла Excel на локальном диске
Однажды превосходить файл был сохранен локально, воссоздайте шаги, которые ранее вызывали Ссылка на источник данных недействительна ошибки и посмотрите, можете ли вы создать таблицу Pivots, не обнаружив ошибки.
Если этот метод не подходит для вашей текущей ситуации, перейдите к следующему способу ниже.
Метод 3: Обеспечение того, что диапазон существует и он определен
Видя ошибку из-за несуществующего диапазона
Если этот сценарий применим, вы сможете обойти проблему, определив диапазон, прежде чем пытаться создать сводную таблицу. Вот краткое руководство о том, как это сделать.
Если этот сценарий не применим или вы все еще сталкиваетесь с Недопустимая ссылка на источник данных даже после выполнения действий, описанных выше, перейдите к следующему способу ниже.
Метод 4: Обеспечение того, чтобы ссылка для именованного диапазона была действительной
Обеспечение того, что ссылки действительны
Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».
Вы не даёте заголовки столбцам таблиц
Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.
Пустые столбцы и строки внутри ваших таблиц
Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.
На одном листе располагается несколько таблиц
Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.
Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?
Данные одного типа искусственно располагаются в разных столбцах
Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:
Казалось бы, перед нами безобидный формат для накопления информации по продажам агентов и их штрафах. Подобная компоновка таблицы хорошо воспринимается человеком визуально, так как она компактна. Однако, поверьте, что это сущий кошмар — пытаться извлекать из таких таблиц данные и получать промежуточные итоги (агрегировать информацию).
Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.
Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.
Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.
Рекомендуемый формат таблицы выглядит так:
Разнесение информации по разным листам книги «для удобства»
Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.
Информация в комментариях
Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.
Бардак с форматированием
Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:
- Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».
- Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
- Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.
Объединение ячеек
Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.
Объединение текста и чисел в одной ячейке
Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.
Числа в виде текста в ячейке
Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.
Если ваша таблица будет презентоваться через LCD проектор
Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.
Страничный режим листа в Excel
Это тот самый режим, при котором Excel показывает, как лист будет разбит на страницы при печати. Границы страниц выделяются голубым цветом. Не рекомендую постоянно работать в этом режиме, что многие делают, так как в процессе вывода данных на экран участвует драйвер принтера, а это в зависимости от многих причин (например, принтер сетевой и в данный момент недоступен) чревато подвисаниями процесса визуализации и пересчёта формул. Работайте в обычном режиме.
Вполне вероятно, вы уже хорошо знакомы с этими мелкими ошибками. Одно случайное удаление, один неверный щелчок могут вывести электронную таблицу из строя. И приходится заново собирать/вычислять данные, расставлять их по местам, что само по себе может быть сложным занятием, а зачастую, невозможным, не говоря уже о том, что это отнимает много времени.
И здесь вы не одиноки: даже самые продвинутые пользователи Эксель время от времени сталкиваются с этими ошибками. По этой причине мы собрали несколько советов, которые помогут вам сэкономить несколько минут (часов) при решении проблем с ошибками Excel.
В зависимости от сложности электронной таблицы, наличия в ней формул и других параметров, быть может не все удастся изменить, на какие-то мелкие несоответствия, если это уместно, можно закрыть глаза. При этом уменьшить количество таких ошибок вполне под силу даже начинающим пользователям.
Несколько полезных приемов в Excel
Прежде чем перейти к ошибкам и советам, мы хотели бы поделиться несколькими полезными приемами, которые помогут избежать ошибок в дальнейшем, сэкономить массу времени и сделать вашу жизнь намного проще:
Всем знакома маленькая зеленая стрелочка в верхнем левом углу ячейки. Вы знаете, этот противный флажок, который Excel использует, чтобы указать, что что-то пошло не так со значениями в ячейке.
Во многих случаях, нажав на эту стрелку, вы получите достаточно информации, чтобы решить проблему на месте. Вот так это выглядит:
Эксель требует, чтобы формулы содержали только цифры, и не будет отвечать на формулы, связанные с текстом, поэтому он покажет вам ошибку.
Более сложная ошибка. Вот краткое изложение того, почему это может появиться в ячейке, в которой вы работаете.
Допустим, вы правильно написали формулу, но недостаточно информации, введенной в отдельные ее записи. Запись в массиве таблиц неполная. Требуется фактическое имя таблицы, чтобы узнать, где искать желаемое значение.
После этого, в правой части вашей электронной таблицы появится Мастер функций, где вы сможете выбрать нужную формулу. Затем Excel проведет вас через каждый шаг формулы в отдельных полях, чтобы избежать ошибок и программа могла правильно прочитать вашу ячейку.
Как в Excel убрать решетки из ячейки?
Нажмите на правую границу заголовка столбца и увеличьте ширину столбца.
Вы можете дважды щелкнуть по правой границе заголовка, чтобы автоматически разместить самую широкую ячейку в этом столбце.Лечится довольно просто. Измените значение на значение, не равное 0, или добавьте значение, если ваша ячейка была пустой.
Теперь, что на самом деле означает эта ошибка? Вы могли случайно удалить или вставить данные поверх ячейки, используемой формулой. Например, ячейка B16 содержит формулу =A14/F16/F17.
Здесь важно отметить, что не данные из ячейки удаляются, но сама строка или столбец.
Прежде чем вставлять набор ячеек, убедитесь, что нет формул, которые ссылаются на удаляемые ячейки. Кроме того, при удалении строк, столбцов, важно дважды проверить, какие формулы в них используются.
Чтобы дать вам некоторый дополнительный контекст, вот как работают справочные операторы Excel:
- Оператор диапазона (точка с запятой): определяет ссылки на диапазон ячеек.
- Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
- Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.
Прежде всего, убедитесь, что вы используете правильный синтаксис в формуле. Используйте двоеточие, чтобы отделить первую ячейку от последней, когда вы ссылаетесь на непрерывный диапазон ячеек в формуле. С другой стороны, использование запятой поможет правильно вывести формулу, если вы ссылаетесь на две ячейки, которые не пересекаются.
Как устранить эту ошибку
3 раза проверьте все свои формулы и внимательно посмотрите, какие листы или строки могут быть удалены или неправильно указаны. Если у вас есть несколько формул, связанных вместе, убедитесь, что в каждой формуле присутствуют значения.
И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).
Как устранить эту ошибку
Проверьте, не ввели ли вы какую-либо отформатированную валюту, дату или спецсимвол. Обязательно удалите их из формулы, сохранив только цифры.
Заключение
Напишите в комментариях, а что вы думаете по этому поводу. Хотите узнать больше советов по Excel? Обязательно поделитесь этой статьей с друзьями.
Читайте также: