Недопустимая ссылка в эксель как убрать
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Ошибка сводной таблицы Excel «Недопустимая ссылка на источник данных»
При открытии книги Excel, содержащей квадратные скобки, в имени (например, «foo [1]») при попытке создания сводных таблиц с использованием данных из книги пользователь получит следующее сообщение об ошибке:ссылка на источник данных недействительна
Причина
Причина из-за того, что квадратные скобки являются недопустимыми символами в имени файла книги Excel.
Решение
Это поведение ожидается, если выполняются эти условия. Для решения этой проблемы пользователю потребуется удалить все недопустимые символы Excel из имени файла.
Дополнительная информация
Эта проблема может возблюдаться при попытке открыть книгу Excel из Internet Explorer. Internet Explorer создает копию файла во временной папке, переименование файла с помощью квадратных скобок. Это можно избежать, если нажать кнопку «Сохранить», а не «Открыть», когда Internet Explorer выведет файл пользователю.
Как убрать ошибки в ячейках Excel
При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
Ошибки в формуле Excel отображаемые в ячейках
В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.
Как убрать #ДЕЛ/0 в Excel
Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.
В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.
Результат ошибочного вычисления – #ЧИСЛО!
Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.
Несколько практических примеров:
Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).
В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.
В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.
В ячейке А3 – квадратный корень не может быть с отрицательного числа, а программа отобразила данный результат этой же ошибкой.
Как убрать НД в Excel
Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:
Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).
Ошибка #ИМЯ! в Excel
Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:
Ошибка #ПУСТО! в Excel
Пустое множество: #ПУСТО! – это ошибки оператора пересечения множеств. В Excel существует такое понятие как пересечение множеств. Оно применяется для быстрого получения данных из больших таблиц по запросу точки пересечения вертикального и горизонтального диапазона ячеек. Если диапазоны не пересекаются, программа отображает ошибочное значение – #ПУСТО! Оператором пересечения множеств является одиночный пробел. Им разделяются вертикальные и горизонтальные диапазоны, заданные в аргументах функции.
В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.
Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!
#ССЫЛКА! – ошибка ссылок на ячейки Excel
Неправильная ссылка на ячейку: #ССЫЛКА! – значит, что аргументы формулы ссылаются на ошибочный адрес. Чаще всего это несуществующая ячейка.
В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.
Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.
Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).
Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.
Так же ошибка #ССЫЛКА! часто возникает при неправильном указании имени листа в адресе трехмерных ссылок.
Как исправить ЗНАЧ в Excel
#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:
Решетки в ячейке Excel
Ряд решеток вместо значения ячейки ###### – данное значение не является ошибкой. Просто это информация о том, что ширина столбца слишком узкая для того, чтобы вместить корректно отображаемое содержимое ячейки. Нужно просто расширить столбец. Например, сделайте двойной щелчок левой кнопкой мышки на границе заголовков столбцов данной ячейки.
Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).
Неправильный формат ячейки так же может отображать вместо значений ряд символов решетки (######).
Выпадающий список с показом изображений
Если у вас Excel 2010 или древнее, то можно почитать вот эту статью, где было описано как реализовать такой выпадающий список с помощью функции СМЕЩ (OFFSET) . Если же у вас Excel 2013, 2016, 2019 или новее, то описанная техника в этих версиях, к сожалению, уже не работает и нужен другой подход, разобранный в этой статье далее.
Шаг 1. Готовим каталог изображений
Для начала нужно создать таблицу-справочник, где будет храниться информация о наших объектах (товарах, людях и т.д.) и их изображения. Выглядеть это должно примерно так:
При создании имеет смысл держать в голове несколько моментов:
- Если в будущем планируется добавление новых строк (товаров), то лучше сразу оформить такой каталог в виде «умной» динамической таблицы с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) . Имя созданной «умной» таблицы можно задать на вкладке Конструктор (Design) — я назвал её в этом примере Каталог.
- Картинки должны полностью вписываться в соответствующие ячейки и не выходить за их границы (иначе они будут обрезаны). Для упорядочивания изображений после их выделения удобно использовать команды выравнивания и распределения на вкладке Формат (Format) . Там же можно задать размеры (высоту и ширину) изображений, чтобы быстро вписать их в ячейки:
Шаг 2. Создаем именованные диапазоны для ячеек с картинками
Чтобы массово и быстро присвоить ячейкам с изображениями в столбце Картинка придуманные имена из столбца ИД используем следующий трюк:
- Выделяем столбцы ИД и Картинка (диапазон C2:D7 в нашем примере)
- Открываем вкладку Формулы (Formulas)
- Выбираем команду Создать из выделенного (Create from selection) .
- Ставим флажок В столбце слева (Left column) , остальные флажки выключаем и жмем ОК.
Проверить получившиеся результаты можно в Диспетчере имен там же на вкладке Формулы (Formulas — Name manager) .
Шаг 3. Создаем выпадающий список товаров
Теперь нам нужно создать выпадающий список с названиями товаров, откуда пользователь впоследствии будет их выбирать. Он может быть на том же или на другом листе (но в этой же книге!).
Техника тут простая:
- Выделяем ячейку для выпадающего списка.
- Выбираем на вкладке Данные команду Проверка данных (Data — Data Validation) .
- Из выпадающего списка Тип (Allow) выбираем вариант Список (List) и в поле Источник (Source) вводим формулу:
Здесь «Каталог[Название]» — это текстовая (в кавычках!) ссылка на содержимое столбца с названиями товаров в нашей «умной» таблице-каталоге, а функция ДВССЫЛ (INDIRECT) нужна, чтобы превратить эту текстовую ссылку в настоящую.
Делать так приходится потому, что в поле Источник, к сожалению, нельзя прописывать прямые ссылки на строки или столбцы «умных» таблиц. Однако с помощью «костыля» в виде ДВССЫЛ мы это ограничение успешно обходим.
Шаг 4. Вытаскиваем имя диапазона для выбранного товара
Здесь же давайте получим имя диапазона из столбца ИД для выбранного в выпадающем списке товара. Проще всего это сделать классической функцией ВПР (VLOOKUP) :
В английской версии это будет:
- С2 — адрес ячейки с выпадающим списком, содержимой которой мы ищем
- Каталог — имя нашей «умной» таблицы
- 3 — порядковый номер столбца ИД в «умной» таблице
- ЛОЖЬ (или 0) — точный режим поиска
Шаг 5. Создаем именованный диапазон с ДВССЫЛ
Как вы уже, наверное, догадались, мы будем использовать содержимое ячейки E2 как ссылку на нужное нам изображение. Однако напрямую сделать это не получится, т.к. для Excel её содержимое формально является текстом. Чтобы оживить текстовую ссылку и превратить её в настоящую — используем уже знакомую нам функцию ДВССЫЛ (INDIRECT) . Для этого:
- Открываем Диспетчер имён на вкладке Формулы и жмём кнопку Создать (Formulas — Name Manager — Create) .
- Вводим любое подходящее имя для создаваемого диапазона, например Фото.
- В поле Ссылка (Reference) вводим формулу: =ДВССЫЛ(Лист2!$E$2)
Шаг 6. Добавляем картинку и привязываем её к списку
Остался последний шаг:
- Выделим в нашей «умной» таблице любую ячейку с картинкой (например D2).
- На вкладке Главная развернём выпадающий список Копировать и выберем опцию Копировать как рисунок (Home — Copy — Copy as Picture) . В появившемся затем окне можно выбрать вариант Как на экране (As on screen) и Растовый (Bitmap) :
Нужно сделать именно так, чтобы скопировались не отдельно ячейка и картинка, а всё содержимое ячейки вместе с картинкой как единое целое изображение.
Вот, собственно, и всё 🙂 Можно наслаждаться результатом:
Примечания
- Ячейку С2, где мы с помощью ВПР получали имя нужного нам изображения можно, само-собой, скрыть.
- Чтобы не было видно уродливых рамок вокруг картинки — нужно отключить все границы и убрать заливку на листе с каталогом. Для «умных» таблиц это делается на вкладке Конструктор (Design) . Также можно отключить на листе каталога и тонкие серые линии сетки через Вид — Сетка (View — Gridlines) .
- Если в будущем вы будете дописывать новые товары и добавлять новые изображения в каталог — не забудьте присвоить новым ячейкам имена через команду Формулы — Создать из выделенного.
Ссылки по теме
- Как создать выпадающий список с картинками в старыхверсиях Excel (2010 и старше)
- Выпадающий список с автоматическим пополнением
- Выпадающий список с быстрым поиском по первым буквам