Auditportal.ru

Аудит Портал
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как извлечь ссылку на ячейку из формул в Excel?

Получение формулы другой ячейки в целевой ячейке

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

Однако я нашел статью, опубликованную в 2006 году, в которой утверждалось, что было найдено решение, отличное от VBA, но ссылка, указанная в статье, теперь не работает.

В данном случае можно обойтись без V BA. Используются макросы XL4 (это макрос, а не VBA).

В ячейках A2: A4 на рисунке 1 содержатся обычные формулы.

enter image description here

Переход к формулам → Определить имя, я определил два именованных диапазона (см. рис .2), с информацией, отображаемой в ячейках A6: B8.

enter image description here

Введите в ячейку B2 =FormulaAsText . В этом случае формула в ячейке A2 будет получена в виде текста.

Пояснение: Диапазон с именем FormulaAsText использует =GET.CELL(info_type,reference) . В данном случае info_type = 6 извлекает формулу, а reference = OFFSET(INDIRECT(«RC»,FALSE),0,-1) использует ячейку с 0 строками и -1 столбцом, смещенную относительно той, в которой используется формула.

Скопируйте B2 и вставьте его в B3: B4. Формулы можно увидеть в A3: A4. Функция рабочего листа CELL извлекает только значения из рабочего листа, а не формулы (в отличие от GET). CELL ).

Поскольку FormulaAsText извлекает формулу из ячейки с фиксированным смещением (0, -1) относительно текущей ячейки, я определил другой диапазон FormulaAsText2, который использует смещение (строки, столбцы), считанное из самого листа. Ячейки D2 : D4 содержат =FormulaAsText2 . Таким образом, в ячейке D2 отображается содержимое ячейки B3 ( =OffSET(D2,1,-2) ), которое равно FormulaAsText . В ячейках D3 и D4 отображается их собственное содержимое. Это добавляет некоторую гибкость. ЭТО ДЕЛО ВКУСА.

» Старый XLM GET», — отметил Тим Уильямс в комментарии. FORMULA() «. Этот ответ (не GET), вероятно, уместен. CELL() ).

Этот превосходный ответ завершен благодаря этой информации: Рабочие листы FormulaText доступны в Excel 2013 и более поздних версиях.

=FormulaText(Reference) выполнит трюк с документами

Есть способ сделать это. В качестве примера, у меня есть таблица с датой в ней. Эта дата взята из листа G91. В моей таблице также было имя листа. К таблице я добавил еще два столбца. Каждый столбец содержал колонку (Лист! Он возвращал число 7, потому что G, седьмая буква алфавита, представлена числом 7. Преобразовав число в букву (G), я перешел к другой таблице в моей книге. Добавив строку формул в столбец 2 (Лист! Число 91 возвращает (G91). Примечание. Когда рабочая книга вычисляется, строка и столбец могут отображаться как переменные формулы.

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

Читайте так же:
Как изменить ось X в диаграмме Excel?

«=» и AJ9 и «!» и AM9 и AN9

Элементы, разделенные амперсандом, конкатенируются. В моем примере AJ9 содержит имя листа, AM9 — номер столбца, а AN9 — номер строки.

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

Это предложение может быть полезно для тех, кто после извлечения блока формул и переноса их в новую таблицу хочет снова заставить их работать. Функция Excels FORMULATEXT отлично подходит для подбора формул, но оставляет их как непригодные текстовые строки. Если вы хотите вернуть их в качестве полностью функционирующих формул, вы должны отредактировать каждый из них отдельно, чтобы удалить строковый символ, но вот ярлык для больших блоков. Перейдите в позицию, где у вас есть необходимые формулы в виде текста (другими словами, после использования FORMULATEXT — вы сделали копию и (только значение)). Следующий шаг включает выделение всех ячеек, которые вы хотите преобразовать, а затем переход к пункту меню [Text-To-Columns] ( в Excel 2016). Вы можете выбрать «Разделить», но на следующем экране просто убедитесь, что вы отменили отметки, которые появляются в ваших формулах. Затем «Готово». Excel должен автоматически анализировать ячейки как содержащие формулы, и теперь вы должны снова заставить их работать.

Excel скопировать значения без формул

Где храниться информация после копирования? В буфере обмена. А как изменить данные под нужные условия из буфера обмена перед вставкой в ячейку ? Для этого применяется инструмент специальная вставка в Excel. Т.е. если вам нужно вставить данные без форматирования, транспонировать или вставить только формулы, то инструмент для вас. Функция крайне полезна — настоятельно рекомендую присмотреться.

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

Специальная вставка в Excel. Пример использования

Если вы скопировали данные с расчетами и хотите вставить только значения этих данных, уже без формул. Сделайте — Копировать (Ctrl+ C) нужные ячейки — выберите ячейку/диапазон для вставки — кликаем правой кнопкой мыши и выбираем Специальную вставку (Ctrl + Alt + V) .

Затем появиться таблица с картинки номер 1. Выбираем галочку «только значения».

Это был пример, а теперь разберем все предлагаемые варианты.

Здесь вроде ясно, можно вставить отдельно то, что содержится в ячейке. Вставляли значения мы в примере выше.

То, что следует далее, еще более интересно

— вставить условия на значения. Понимаем буквально, поверх значений вставляем условия. Это либо проверка данных ( выпадающий список ) или условное форматирование.

Таким образом, если потребуется наложить дополнительные условия.

Читайте так же:
Как изменить или увеличить размер шрифта в строке формул?

— вставить с исходной темой. Тема в Excel это формат всей книги или листа. Применяется для единообразной цветой схемы или форматов диаграмм. Если для этого листа применена одна тема, а вставить нужно другую. применяется этот пункт. Честно говоря, я пользуюсь редко этой функцией.

— вставить без рамки. Вставляем без формата границ

— вставить ширины столбцов. Вставляем форматирование Ширина столбца.

— формулы и форматы чисел. Вставляются и формулы и форматы

— значения и форматы чисел. Очень удобно. Вставляется не только значения, но и форматы чисел.

Раздел Операция (сложить, вычесть, разделить, умножить)

Классная штука! Например, у вас есть два диапазона

А вам нужно разделить данные из столбца D на данные из столбца B (см. картинку). Без формул. Здесь нам и пригождается Специальная вставка в Excel.

Выделяем диапазон B3:B5. Копируем. Выделяем диапазон D3:D5. Нажимаем Специальная вставка. И отмечаем галочку «разделить»

Вуаля. Попробуйте сами — понравится.

Напоследок три бомбы специальной вставки

Я гарантирую, что эти функции помогут вам сэкономить много времени

1. пропустите пустые клетки

У вас есть диапазон из столбца D. А надо его вставить в В без потери данных в В.

Ну вы поняли. Если не использовать функцию «Пропускать пустые ячейки» специальной вставки, то пустыми значениями из столбца D затрутся непустые значение из B. Это огорчает. Выручает Специальная вставка.

2. Транспонировать. Когда столбцы надо сделать строками и наоборот. Я даже отдельную статью написал

3. В левом нижнем углу есть кнопка Вставить связь. Каюсь, узнал о ней только несколько месяцев назад. Все просто, вставляются связи на те ячейки (по-простому ссылки), которые вы выделили. Если у вас разрывный диапазон супер-удобно!

Функции ссылок и массивов Excel

В этой статье мы рассмотрим некоторые функции для работы с ссылками и массивами:

  1. ВПР;
  2. ГПР;
  3. СТРОКА;
  4. СТОЛБЕЦ;
  5. АДРЕС;
  6. ДВССЫЛ;
  7. ЧСТРОК;
  8. ЧИСЛСТОЛБ;
  9. СМЕЩ;
  10. ПОИСКПОЗ.

Функция ВПР

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

Синтаксис: =VPR(ключ; диапазон; номер_столбца; [интервал_просмотра]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
    • FALSE — функция ищет точное совпадение в первом столбце таблицы. Если возможно более одного совпадения, возвращается первое совпадение. Если совпадение не найдено, функция возвращает ошибку # N/A.
    • TRUE — функция ищет приблизительное совпадение. Это значение по умолчанию. Приблизительное совпадение означает, что если совпадение не найдено, функция возвращает значение предыдущего ключа. В этом случае предыдущим ключом будет тот, который предшествует искомому ключу в порядке от наименьшего к наибольшему или от A к Z. Поэтому, прежде чем применять функцию с поиском по диапазону, отсортируйте первый столбец таблицы по возрастанию, так как в противном случае функция может вернуть неверный результат. Если найдено несколько совпадений, возвращается последнее совпадение.

    Номер столбца привязан не к индексу на листе, а к порядку в диапазоне.

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

    Различие интервальных просмотров ВПР

    При определении цены используйте функцию FIR с точным совпадением (интервальный вид FALSE), поскольку этот параметр определяется для всех продуктов и не предусматривает использование цены другого продукта, если таковая еще не определена.

    Для определения позиции продукта используется БПФ с приблизительным совпадением (отображение интервала TRUE), поскольку продукты назначаются по категориям. Поскольку название продукта состоит из названия категории и дополнительного текста, при сортировке от A до Z названия продуктов будут появляться сразу после названия категории, поэтому, когда таблица не найдет соответствия ключу, будет извлечено первое значение.

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

    Избавиться от этого эффекта можно, определив категорию из названия продукта с помощью текстовых функций LEVSIMB(C11;SEARCH(«;C11)-1), которые вернут все символы до первого пробела, а также изменят интервальный вид на точный.

    В дополнение к вышесказанному, функция FFT позволяет использовать подстановочные знаки — * (звездочка — любое количество символов) и ? (любой символ). Например, для значения «*» & «ivan» & «*» могут соответствовать строки Ivan, Ivanov, sofa и т.д.

    Также данная функция может искать значения в массивах – =ВПР(1;<2;"Два":1;"Один">;2;ЛОЖЬ) – результат выполнения строка «Два».

    Функция ГПР

    Горизонтальное первое равенство. Находит совпадение ключа в первой строке определенного диапазона и возвращает значение, совпадающее с этим ключом в столбце первой строки этого диапазона.

    Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).

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

    Функция СТРОКА

    Эта функция возвращает номер строки указанной ссылки ячейки.

    Синтаксис: =STROCK([ссылка]), где аргумент «ссылка» является необязательным. Если опущено, возвращается номер текущей строки.

    =СТРОКА(D4) – результат 4.
    =СТРОКА() – функция вернет номер строки, в которой она расположена.

    Функция СТОЛБЕЦ

    Получает номер колонки ячейки, на которую указывает ссылка.

    Он записывается как =Column([ссылка]), где «ссылка» не является обязательным аргументом. По умолчанию возвращается номер текущего столбца.

    =СТОЛБЕЦ(C4) – формула вернет значение 3.
    =СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.

    Функция АДРЕС

    Возвращается текст, представляющий собой адрес ячейки.

    Этот код представляет собой формулу: =ADRES(row, column, [fix_type], [link_style], [sheet_name], где:

    • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
    • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
    • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
      • 1 — значение по умолчанию, когда все индексы закреплены;
      • 2 — закрепление индекса строки;
      • 3 — закрепление индекса столбца;
      • 4 — направление без закрепления.
      • TRUE — формат ссылки «A1»;
      • FALSE — формат ссылки «R1C1».

      =АДРЕС(1;1) – возвращает $A$1.
      =АДРЕС(1;1;4) – возвращает A1.
      =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
      =АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

      Функция ДВССЫЛ

      Эта функция преобразует текстовую строку в ссылку на данный адрес.

      Синтаксис: =DWSSL(ссылка_адрес; [ссылка_стиль]), где

      • адрес_ссылки – обязательный аргумент. Строка, представляющая адрес ссылки на ячейку или диапазон. Например, «C3», «R3C3» или «D8:D9».
      • стиль_ссылки – необязательный аргумент. Логическое значение, определяющее стиль ссылки:
        • True — стиль A1. Это значение по умолчанию;
        • FALSE — стиль R1C1.

        =ДВССЫЛ(«a3») – возвращает ссылку на ячейку A3.
        =ДВССЫЛ(«r3c3») – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
        =ДВССЫЛ(«r3c3»; ЛОЖЬ) – возвращает ссылку на ячейку C3.
        =ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
        Вложение функции ДВССЫЛ со ссылкой на диапазон:

        Вложение ДВССЫЛ

        Функция ЧСТРОК

        Количество строк в диапазоне или массиве.

        = CROSS(link), где «link» — ссылка на ячейку, диапазон или массив.

        Пример использования:
        =ЧСТРОК(D1:D8) – функция возвращает результат 8.
        =ЧСТРОК(<1:2:3:4:5>) – функция определят, что в массиве 5 строк.

        Функция ЧИСЛСТОЛБ

        Возвращает число столбцов в указанном диапазоне или массиве.
        Синтаксис: =ЧИСЛСТОЛБ(ссылка), где «ссылка» обязательный аргумент, являющийся ссылкой на ячейку, диапазон либо массив.
        Пример использования:
        =ЧИСЛСТОЛБ(A5:D5) – результат функции 4.
        =ЧИСЛСТОЛБ(<1;2;3;4;5>) – функция определят, что в массиве 5 столбцов.

        Функция СМЕЩ

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

        Синтаксис: =MEMB (ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина]), hvor

        • Ссылка является обязательным аргументом. Ссылка на ячейку или область, относительно которой будет происходить сдвиг.
        • Shift_by_lines является обязательным аргументом. Отрицательное или положительное число, указывающее, на сколько строк должен быть сдвинут диапазон.
        • Shift_by_columns — обязательный аргумент. Отрицательное или положительное число, указывающее, на сколько столбцов должен быть сдвинут диапазон.
        • Высота — необязательный аргумент. Натуральное число (кроме нуля), указывающее количество строк в новом (сдвинутом) диапазоне.
        • Width — необязательный аргумент. Натуральное число (отличное от нуля), указывающее количество столбцов в новом диапазоне.

        Если в результате смещения диапазон выходит за границы листа, функция возвращает ошибку #SLIP!

        Для примера будем использовать вложение функции СМЕЩ в функцию СУММ.
        Изначально ссылаемся на диапазон из 10 строк и 1 столбца, где все ячейки имеют значение 2. Таким образом получает результат выполнения формулы – 20.
        Вложение СМЕЩ

        Теперь сместим диапазон на один столбец влево, т.е. на -1.

        Смещение диапазона

        Нам нужно еще раз изменить справочник, а именно расширить его до 4 колонок. После этого вы получите ссылку A3:D12. Результат показан на изображении.

        Расширение диапазона

        Функция ПОИСКПОЗ

        Данный метод возвращает позицию элемента в диапазоне или массиве, учитывая его значение.

        (искомое_значение, массив, [match_type]); где:

        • искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
        • Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
        • тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
          • 1 — значение по умолчанию. Если совпадение не найдено, возвращается позиция ближайшего значения к искомому элементу. Массив или диапазон должен быть отсортирован от наименьшего к наибольшему или от A к Z.
          • 0 — функция ищет точное совпадение. Если совпадение не найдено, возвращается ошибка #N/D.
          • -1 — Если совпадение не найдено, возвращается позиция элемента, наиболее близкого по значению к искомому. Массив или диапазон должен быть отсортирован в порядке убывания.

          Пример использования:
          =ПОИСКПОЗ(«Г»; <"а";"б";"в";"г";"д">) – функция возвращает результат 4. При этом регистр не учитывается.
          =ПОИСКПОЗ(«е»; <"а";"б";"в";"г";"д">; 1) – результат 5, т.к. элемента не найдено, поэтому возвращается ближайший меньший по значению элемент. Элементы массива записаны по возрастанию.
          =ПОИСКПОЗ(«е»; <"а";"б";"в";"г";"д">; 0) – возвращается ошибка, т.к. элемент не найден, а тип сопоставления указан на точное совпадение.
          =ПОИСКПОЗ(«в»; <"д";"г";"в";"б";"а">; -1) – результат 3.
          =ПОИСКПОЗ(«д»; <"а";"б";"в";"г";"д">; -1) – элемент не найден, хотя присутствует в массиве. Функция возвращает неверный результат, так как последний аргумент принимает значение -1, а элементы НЕ расположены по убыванию.

          Функция позволяет использовать подстановочные знаки «*» и «? «, когда речь идет о текстовых значениях. «.

          голоса
          Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector