Вопрос к специалистам по MS Excel

С надеждой — ведь среди биржевиков не может не быть опытных юзеров Экселя — прошу помощи. (Рунет не помог.)
В ячейках столбца А гиперссылки в виде текста. Ячейки могут быть пустыми.
В ячейках столбца В некий текст.
Как вставить гиперссылку по условию? Либо ссылка, либо текст без ссылки.
=ЕСЛИ(A1<>''"; ГИПЕРССЫЛКА(A1;B1);B1) — эта формула вставляет гиперссылку в любом случае. То же и с функцией ВЫБОР.
Подошла бы и юзерская функция, сам так и не придумал.
Макросы не годятся.

---------------------
Дописываю программу (книгу Эксель) для помощи при отборе облигаций. Полностью автоматизированная, данные загружает из Квика, совершает виртуальные покупки и продажи и показывает, как при той или иной сделке изменится доходность вашего портфеля и др. Отслеживает три счёта: обычный биржевой, ИИС и Список наблюдения — в последний помещаются облигации, которые вы ещё не купили, но присматриваетесь к ним на предмет покупки.
Кто поможет с формулой — получит в подарок, как будет готова. (Она на стадии последнего «вылавливания блох», но надо ещё написать хелп-файл и сделать обучающий ролик).

Salamandra

Санкт-Петербург

114

с 18 августа 2011

11 Комментариев

autotrade
21 августа 2024, 23:46
билу напиши может он знает
0
Дюша Метелкин
22 августа 2024, 00:06
Увы, не настолько хорошо знаю MS Excel
Для себя делаю что-то похожее, но не столь масштабное и вручную.
Подход внушает!
0
Дмитрий Можаев
22 августа 2024, 01:23
Написал что-то подобное без покупки и продажи для акций и даже телеграмм бота прикручивал к таблице, который мог бы оповещать меня о нужных ценах на активы по моей стратегии = )
Но научитесь вы уже пользоваться простыми AI инструментами, чтобы уменьшить время разработки!

Вариант 1:
Function ConditionalHyperlink(link As String, display_text As String) As Variant If link <> "" Then ConditionalHyperlink = Hyperlink(link, display_text) Else ConditionalHyperlink = display_text End If End Function
и используем как: =ConditionalHyperlink(A1; B1)
Вариант 2:
Шаги:
1. Пусть у вас есть данные в столбцах A и B.
2. В столбце C вставьте формулу для гиперссылки:
  =ЕСЛИ(A1<>""; ГИПЕРССЫЛКА(A1; B1); "")
  - В этой формуле, если ссылка в A1 существует, вставляется гиперссылка, иначе ячейка остается пустой.
  - В столбце D вставьте текст без гиперссылки:
    =ЕСЛИ(A1<>""; ""; B1)
    
    Эта формула вставит текст, только если гиперссылки нет.
    Примените условное форматирование, чтобы скрыть либо столбец C, либо D, в зависимости от того, существует ли ссылка:
    Для столбца C: если ячейка D не пустая, цвет текста сделать белым (для белого фона).
    Для столбца D: если ячейка C не пустая, цвет текста сделать белым.
    
    Извините, но текст съехал тк на Смртлабе убогое форматирование текста
+1
- Salamandra
  22 августа 2024, 12:39
  Дмитрий Можаев, спасибо.
  Нечто подобное я проверял. В таком виде выдается #ЗНАЧ!
  Если переделать так:
  
  Function ConditionalHyperlink(link As Range, display_text As Range) As Variant
  If link.Value <> "" Then
  ConditionalHyperlink = Hyperlink(link, display_text)
  Else
  ConditionalHyperlink = display_text.Value
  End If
  End Function
  
  — то начинает работать, но выдает ошибку, что не знает, что такое Hyperlink. Такое свойство есть у Shape, а у Range нету.
  
  А если так (где ААА – сама ячейка с формулой):
  
  Function ConditionalHyperlink(link As Range, display_text As Range, AAA As Range) As Variant
  If link.Value <> "" Then
  Hyperlinks.Add Anchor:=AAA, Address:=link.Value, TextToDisplay:=display_text.Value
  Else
  ConditionalHyperlink = display_text.Value
  End If
  End Function
  
  — то снова ошибка значения в случае с гиперссылкой (с текстом работает).
  0
- Salamandra
  22 августа 2024, 12:58
  Дмитрий Можаев, Естественная идея: поместить всю эту контрукцию в другой столбец и уже оттуда менять нужные нам ячейки. Тогда работает. Но вот беда: Эксель запрещает менять какие-либо иные ячейки, кроме той, откуда была вызвана функция. И при дальнейших телодвижениях на листе происходит аварийное закрытие книги.
  
  Со столбцами хорошая идея, спасибо! Приму на будущее. Сейчас мне придется вставлять новый столбец, и собьются все номера и буквы на листе. Придется переделывать макросы — на это мне увы, уже пороху не хватит. :(
  
  Есть ли тут система личных сообщений, чтоб Вы могли сообщить, куда Вам выслать моё творение, когда будет готово?
  0
Андрей &
22 августа 2024, 03:29
Ради интереса. Не особо вникая в тему, кинул этот вопрос мозгу GPT-4 и вот его ответ. К стати, попробовал на офисе 2007 — работает.
Для решения вашей задачи в Excel можно использовать пользовательскую функцию (UDF), написанную на VBA. Это позволит вставлять гиперссылку только в том случае, если в ячейке столбца A есть ссылка. Вот как это можно сделать:
1. Откройте Excel и нажмите Alt + F11, чтобы открыть редактор VBA.
2. В редакторе VBA выберите Insert -> Module, чтобы создать новый модуль.
3. Вставьте следующий код в модуль:
```
<code class="l<span style="color: #000000;">anguage-vba">Function ConditionalHyperlink(link As String, displayText As String) As Variant
    If link <> "" Then
        ConditionalHyperlink = Hyperlink(link, displayText)
    Else
        ConditionalHyperlink = displayText
    End If
End Function
</code></span>
```
4. Закройте редактор VBA и вернитесь в Excel.
Теперь вы можете использовать эту функцию в вашем Excel листе. Например, в ячейке C1 вы можете ввести следующую формулу:
```
<span style="color: #000000;"><code class="language-excel">=ConditionalHyperlink(A1, B1)
</code></span>
```
Эта формула будет проверять, есть ли в ячейке A1 ссылка. Если да, будет создана гиперссылка с текстом из ячейки B1. Если нет, будет отображаться просто текст из ячейки B1 без гиперссылки.
Обратите внимание, что для работы пользовательских функций необходимо, чтобы макросы были включены в вашей книге Excel.
+1
- Salamandra
  22 августа 2024, 12:56
  Андрей &, Вы серьезно насчёт того, что проверяли и работает? У меня, кстати, как раз Эксель 2007. Постом выше я подробно отписался про эту функцию.
  Передача аргументов как As String вообще сомнительна, а если как As Range то что такое Hyperlink& — VBA такого не знает.
  0
Краснов Геннадий
22 августа 2024, 09:33
Как вариант:
=ЕСЛИ(ПРОПИСН(ЛЕВСИМВ(A1;4))=«HTTP»; ГИПЕРССЫЛКА(A1;B1);B1)
0
- Salamandra
  22 августа 2024, 13:00
  Краснов Геннадий, во всех такого рода конструкциях ячейка всё равно превращается в ссылку — правда, пустая ссылка никуда не ведет, но курсор над ячейкой превращается в ладонь с пальцем, а это раздражает.
  0