Применить имена в Excel прослушивается?

Question

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

В моем листе у меня есть эти именованные диапазоны:
A1 - "name1"
B1 - "name2"
C1 - "name3"

В некоторых других 3 ячейках у меня есть эти формулы:

=A1
=A1+B1
=COUNT(A1:C1)

После запуска Применить имена ... Я ожидаю иметь это:

=name1
=name1+name2
=COUNT(name1:name3)

Я никогда не получу это независимо от того, какие варианты я выберу. Более того, обычно мои формулы полностью разрушаются ...

=name1
=name1
=name1

Как я могу получить результат, который мне нужен?
Я хорошо разбираюсь в VBA, поэтому я пытался разработать решение этой проблемы с помощью VBA, но код также не работал.

Я использую Excel 2013, 32-разрядная версия (MS Office Professional Plus). ОС - Windows 7 Enterprise, 64-битная.

Answer 1

Я не могу повторить это (с моей версией Excel на Windows 7) -

Если я определю ваши имена, а затем создаю формулы

Затем примените имена

Я получаю желаемый результат -

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

---

С твоим -

применять имена -

Как показано здесь

---

Без изменений без "показа формул" -

применять имена

---

Ладно, поехали. Офис 2013, Windows 7

применять имена

Похоже, повторяется сбой.

---

Хорошо, давайте попробуем Excel 2016 на OSX Yosemite

Давайте определим наши имена и формулы -

Хорошо, хорошо, давайте применим наши имена

Какие? Оповещение Формула слишком длинная

Теперь он выбрал мой count и .. что? Предупреждение Microsoft Excel не может найти ссылки для замены

И это .. частичный сбой?

Хорошо, тогда давайте сделаем это вручную -

Странно, это не выделяет диапазон, только две ячейки?

Но это работает?

Просто для сравнения, регулярный count выделяет диапазон -

Answer 2

Скажем, мы начнем с:

и мы уже присвоили имена A2 и B2. На вкладке "Формулы" раскрывающийся список:

Определить имя > Применить имена ...

Убедитесь, что мы подсвечиваем оба имени и нажимаем ОК

и мы получаем:

и поэтому имена применяются!

Answer 3

Как было отмечено в обзоре кода, это вызовет проблемы, если, например, он ищет "А1", находит "А10" и т.д.

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

Обратите внимание, что я ищу usedrange - но вы можете сузить это, как вам нравится, сбросив srchRng .

Option Explicit
Sub FixNames()

Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names

Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String

Dim c As Range
Dim n As Integer

'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange

'For each name (n) in the collection
For n = 1 To ClctNames.Count

    'I'm storing the Named Range's name and address as strings to use below
    rngName = ClctNames(n).Name
    rngNameLoc = ClctNames(n).RefersToRange.Address

    '--Should I break this out into a function? If so, at what point?
    For Each c In srchRng
        'We only want to test cells with formulas
        If c.HasFormula = True Then
           'We have to check if the cell contains the current named range's address
           If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
              'Since these are perfect matches, no need to look for length or location, just replace
              strFrmla = Replace(c.Formula, rngNameLoc, rngName)
              c.Formula = strFrmla
           End If
        End If
    Next
Next

'No error handling should be needed

End Sub

Вам нужно использовать абсолютные ссылки, потому что, когда я вытягиваю именованный диапазон RefersToRange.Address возвращает объект диапазона, а не диапазон, поэтому я устанавливаю его как строку. Я думаю, вы могли бы написать функцию, которая удаляет абсолютные ссылки $ если хотите.

_{это было весело}