Что делает ДОЛЯГОДА?

Функция ДОЛЯГОДА в Excel возвращает долю года между двумя датами. Это особенно полезно в финансовых расчетах, где нужно учитывать, сколько времени прошло между двумя событиями в виде части года: например, при начислении процентов, амортизации или расчете ставок доходности.

В отличие от обычного подсчета дней между датами, ДОЛЯГОДА позволяет точно учитывать, как именно рассчитываются дни в году — по фактическому количеству дней, по 360-дневному году или другим методам.

Синтаксис

=ДОЛЯГОДА(нач_дата; кон_дата; [базис])

• нач_дата — дата начала периода.
• кон_дата — дата окончания периода.
• [базис] — (необязательный) способ расчета дней в году. Может принимать значения от 0 до 4:

Примеры

• =ДОЛЯГОДА(“01.01.2024”; “01.07.2024”) — вернёт 0,5, если используется базис по умолчанию.
• =ДОЛЯГОДА(“01.01.2024”; “01.07.2024”; 3) — вернёт 0,4986, так как год считается 365 дней.

Полезно знать

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

Применение

• Расчет процентов по займам и кредитам, когда ставка указана в годовых процентах.
• Определение доли амортизации при неполном году использования актива.
• Финансовое моделирование в инвестиционных проектах.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

ВРЕМЗНАЧ — это функция Excel, которая преобразует текстовое значение времени в числовой формат. Это особенно полезно, когда вы работаете с импортированными или вручную введёнными значениями времени, представленными как текст.

Что делает функция ВРЕМЗНАЧ

Excel хранит дату и время как числа: целая часть — это дата, дробная — это время. Функция ВРЕМЗНАЧ помогает превратить текст вроде “12:30” в соответствующее дробное число 0,520833, которое Excel распознаёт как 12 часов 30 минут.

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

Значение не может равняться единице или быть больше нее. Для значений 24:00:00 и более время будет взято как остаток от целочисленного деления на 24 часа, т.е. для 24:00:00 – 0, для 36:00:00 – 0,5.

Синтаксис

=ВРЕМЗНАЧ(текст_времени)

• текст_времени — строка, содержащая время в формате, который Excel может распознать, например “14:45”, “7:15:30”.

Примеры

• =ВРЕМЗНАЧ(“12:00”) вернёт 0,5 — это половина суток, т.е. 12:00 дня.
• =ВРЕМЗНАЧ(“6:30 AM”) вернёт 0,270833.
• =ВРЕМЗНАЧ(“18:00:00”) вернёт 0,75.

Полезно знать

• Если вы видите ошибку #ЗНАЧ!, Excel не смог распознать формат времени. Убедитесь, что текст соответствует допустимому шаблону.
• Вы можете использовать ВРЕМЗНАЧ в сочетании с ДАТАЗНАЧ для преобразования полной даты и времени, разделённых в тексте.

Практическое применение

• Преобразование данных из внешних источников, где время хранится как текст.
• Расчёт интервалов между событиями, началом и концом работы, приёма пищи и пр.
• Автоматизация отчётности, где важно корректное отображение времени.

После применения ВРЕМЗНАЧ вы можете отформатировать ячейку как время, чтобы видеть привычное отображение (например, 08:30), несмотря на то, что в ячейке будет дробное число.

Другие функции “Дата и время”

ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Горячие клавиши в Excel — это быстрый способ выполнять действия, не отвлекаясь на меню и вкладки. Они позволяют буквально в два нажатия выполнить десятки рутинных операций: вставить или удалить строки, выделить ячейки, вставить таблицу, открыть фильтр, заменить значения, сделать формат жирным, курсивом или с границами, а также скрыть или показать данные.

Например, сочетания Ctrl + F и Ctrl + H быстро вызывают окна поиска и замены, Ctrl + Shift + + — помогает вставить новые ячейки, а Ctrl + - — удалить их. А чтобы быстро отформатировать таблицу, достаточно нажать Ctrl + 1.

Знание таких комбинаций позволяет работать в Excel быстрее и увереннее, минимизируя количество кликов мышью и значительно ускоряя выполнение повседневных задач.

Пользователи надстройки !SEMTools (даже бесплатной версии) могут в один клик получить полный список всех сочетаний клавиш. Специальная процедура выводит на новый лист Excel всю таблицу горячих клавиш с описаниями для быстрого ознакомления и печати. Лист создается автоматически и не меняет текущие данные.

Lite-версия надстройки крайне легкая, меньше 400 килобайт, но добавляет множество полезных бесплатных функций, как эта. Также можно познакомиться и с другими процедурами надстройки в тестовом режиме.

А теперь давайте познакомимся с самими горячими сочетаниями клавиш.

Ввод данных

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

Инструменты

Горячие клавиши Excel помогают быстро работать с инструментами: фильтрами, объектами, макросами, таблицами и многим другим. Вот ключевые комбинации, которые экономят время и делают вашу работу эффективнее.

Интерфейс

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

Навигация и выделение

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

Режим редактирования ячейки

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

Сводные таблицы

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

Форматирование

Оформление данных — важная часть работы в Excel. Горячие клавиши позволяют быстро применить нужный стиль или изменить свойства ячеек без использования ленты.

Что делает функция РАЗНДАТ?

Функция РАЗНДАТ возвращает разницу между двумя датами в заданных единицах: годах, месяцах, днях или их комбинациях. Удобна для расчёта возраста, стажа, длительности проектов, «чистых» месяцев без учёта лет и т. д. В русской версии Excel функция есть, но может не отображаться в мастере функций — вводите её вручную.

Синтаксис функции

=РАЗНДАТ(Дата_начала;Дата_конца;"интервал")

• Дата_начала — более ранняя дата
• Дата_конца — более поздняя дата
• “интервал” — строковый код единиц измерения разницы (см. ниже)

Интервалы

• “Y” — полные годы между датами
• “M” — полные месяцы между датами
• “D” — дни между датами
• “YM” — месяцы без учёта полных лет
• “YD” — дни без учёта полных лет
• “MD” — дни без учёта полных месяцев и лет

Когда использовать

• Возраст сотрудника или клиента в годах, месяцах и днях
• Стаж работы (полные годы и месяцы)
• Длительность проекта в месяцах или днях
• Сколько полных месяцев прошло с начала периода (без учёта лет)

Примеры с РАЗНДАТ

1. Полные годы между датами

=РАЗНДАТ(A2;B2;"Y")

Если A2 = 15.03.2000, B2 = 01.02.2025 → результат: 24

2. Полные месяцы между датами

=РАЗНДАТ(A2;B2;"M")

3. Дни между датами

=РАЗНДАТ(A2;B2;"D")

4. Возраст в формате «годы + месяцы»

=РАЗНДАТ(A2;B2;"Y") & " лет " & РАЗНДАТ(A2;B2;"YM") & " мес."

5. Возраст в формате «годы, месяцы, дни»

=РАЗНДАТ(A2;B2;"Y") & " г. " & РАЗНДАТ(A2;B2;"YM") & " мес. " & РАЗНДАТ(A2;B2;"MD") & " д."

РАЗНДАТ не работает?

В отличие от большинства функций Excel, РАЗНДАТ не отображается в списке автоподсказок при наборе формулы и не имеет всплывающей подсказки с описанием аргументов. Это может ввести в заблуждение: кажется, что функция не работает, хотя на самом деле она доступна и исправно выполняет расчёты при правильном вводе.

Если начальная дата больше конечной, функция возвращает ошибку #ЧИСЛО!. Пользователь может подумать, что функция не работает, хотя причина — в неправильном порядке дат.

Почему нет формулы РАЗНДАТ?

Функция РАЗДНДАТ изначально появилась в Lotus 1-2-3 — популярной таблице 90-х годов. Microsoft добавила её в Excel ради обратной совместимости, но никогда официально не продвигала как полноценную функцию. Её считали вспомогательной, устаревшей и предназначенной для тех, кто переходит с других систем.

Тем не менее, из-за широкой популярности и полезности (особенно для расчётов возраста, стажа и т.п.), её оставили в Excel, но скрыли из интерфейса:

• не отображается в списке функций;
• нет подсказки по аргументам;
• нет справки F1;
• нет описания в «Мастере функций».

Форматирование

Если даты форматированы как текст, предварительно преобразуйте их через ДАТАЗНАЧ или убедитесь, что Excel их распознаёт как даты.

Полезные комбинации

• Полные месяцы без учёта лет: “YM”
• Дни с игнорированием лет: “YD”
• Разбивка на годы-месяцы-дни (см. пример выше) — самый частый кейс

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция

Функция ВРЕМЯ используется для создания значения времени в формате Excel на основе заданных часов, минут и секунд. Это удобно, когда вы хотите собрать значение времени из отдельных чисел или вычислений.

Синтаксис:

=ВРЕМЯ(часы; минуты; секунды)

Аргументы:

• часы — число от 0 до 32767. Значения, превышающие 23, преобразуются в дни. Например, 25 часов превращается в 1 день и 1 час (01:00 следующего дня).
• минуты — число от 0 до 32767. Аналогично, 60 минут превращается в 1 час.
• секунды — число от 0 до 32767. 60 секунд — это 1 минута и так далее.

Примеры

=ВРЕМЯ(14; 30; 0)

Результат: 14:30 — отображается как 2:30 PM в числовом формате времени.

Если ввести:

=ВРЕМЯ(27;0;0)

Результат: 03:00 (1 день + 3 часа).

Практическое применение:

• Создание времени из чисел, полученных расчётным путём
• Формирование временных меток из данных
• Упрощение построения расписаний и таймингов

Другие функции “Дата и время”

ВРЕМЗНАЧ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает функция ДАТА

Функция ДАТА собирает дату из трёх чисел: года, месяца и дня. Это самый надёжный способ создать корректную дату в Excel, особенно когда компоненты даты находятся в разных ячейках.

=ДАТА(Год;Месяц;День)

Когда использовать

• Когда год, месяц и день находятся в разных ячейках или вычисляются формулами
• При сборке начальных и конечных дат периодов

Примеры

Несколько простых примеров.

1. Простая дата

=ДАТА(2025;7;25)

Вернёт дату 25.07.2025

2. Дата из ячеек

=ДАТА(A1;B1;C1)

Если в A1 = 2025, B1 = 12, C1 = 31 → результат: 31.12.2025

3. Последний день месяца

0-й день месяца Excel интерпретирует как последний день предыдущего месяца. В этом смысле она является альтернативой функции КОНМЕСЯЦА.

=ДАТА(2024;2+1;0)

Альтернативы

• ДАТАЗНАЧ(текст) — если дата дана в текстовом виде
• СЕГОДНЯ() — текущая дата
• КОНМЕСЯЦА — конец месяца (текущего)

Ошибки и грабли

• Месяц может быть больше 12 или меньше 1 — Excel пересчитает дату. Например:

=ДАТА(2024;13;1)

Результат: 01.01.2025

• При отрицательных значениях Excel может вернуть неожиданный результат
• ДАТА всегда возвращает дату, даже если значения некорректны

Другие функции даты и времени

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

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

Даты, выраженные в виде текста, часто создаются, когда данные импортируются из других программ.

Форматирование

Результат обычно отображается в виде числа, представляющего дату. Это число может быть отформатировано в любой из обычных форматов даты с использованием процедуры “Формат ячеек”.

Синтаксис

=ДАТАЗНАЧ(Дата_в_текстовом_формате)

Пример

В примере используются функции ДАТАЗНАЧ и СЕГОДНЯ для расчета количества дней, оставшихся до окончания срока годности товара.

Функция ДАТАЗНАЧ использовалась потому, что дата была введена в ячейку в виде фрагмента текста, вероятно, после импорта из внешней программы.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

• 1. Через контекстное меню
  • Как вставить ссылку в Excel с помощью контекстного меню
  • Пример
• 2. Горячим сочетанием клавиш
• 3. Функция ГИПЕРССЫЛКА
  • Преимущества
• 4. “Вставить как гиперссылку”
  • Как включить команду
  • Как использовать
• 5. Ссылки на почту, Telegram и WhatsApp в Excel
  • 1. Почта (mailto:)
  • 2. Telegram (tg://resolve)
  • 3. WhatsApp (whatsapp://send)
• 6. Ссылки к объектам Excel со вкладки “Вставка”
  • Ссылки на фигурах
  • Картинки и значки
  • Формулы
  • Ссылки на диаграммах
  • Ссылки на элементах SmartArt
• 8. Ссылки на элементах разгруппированных рисунков
• 9. Ссылки на именованные диапазоны и таблицы
• 10. Вставка ссылок на все файлы в папке
• 11. Бонус. Ссылки на все листы книги (содержание)
• Файл с примерами

В работе со сложными таблицами часто может потребоваться вставить ссылку на ячейку другого листа, сделать множество ссылок на все листы книги или сделать все ссылки активными (кликабельными). В этой статье я собрал 10 способов (и 1 бонусный) сделать ссылку в Excel (или несколько).

1. Через контекстное меню

Один из самых быстрых и понятных способов вставить ссылку в Excel — использовать контекстное меню. Этот метод работает как с ячейками, так и с графическими объектами.

Как вставить ссылку в Excel с помощью контекстного меню

Так выглядит подсказка при наведении на объект со ссылкой:

Гиперссылку можно применить к множеству объектов в Excel, среди которых:

• Обычные ячейки
• Изображения
• Фигуры и формы
• Объекты SmartArt (отдельные элементы внутри тоже)

Пример

Допустим, у вас есть логотип компании в Excel, и вы хотите, чтобы при клике он вёл на ваш сайт:

1. Вставьте изображение логотипа
2. Кликните по нему правой кнопкой
3. Выберите «Гиперссылка…» и вставьте адрес сайта

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

2. Горячим сочетанием клавиш

Процедуру вставки гиперссылки можно вызвать без вызова контекстного меню. В этом поможет сочетание клавиш Ctrl + K. Это может быть удобнее, если вы привыкли к использованию горячих клавиш.

В остальном все шаги соответствуют предыдущему пункту — откроется то же окно “Вставка гиперссылки”:

3. Функция ГИПЕРССЫЛКА

Функция ГИПЕРССЫЛКА позволяет создавать динамические ссылки, которые ведут на веб-сайты, файлы, листы и даже запускают приложения. Это самый гибкий способ вставки ссылок в Excel. Также позволяет быстро сделать все ссылки активными, чтобы не прокликивать их все.

Преимущества

• Работает с любыми типами адресов: сайты, tg://, mailto:, файлы, папки
• Позволяет формировать ссылку на основе значений в других ячейках
• Можно использовать внутри массивов, списков, фильтров
• Выглядит как обычный текст с активной ссылкой

Формула особенно полезна, если нужно массово сформировать ссылки по шаблону, например, подставляя параметры запроса или имена файлов. Это незаменимый инструмент для чек-листов, таблиц с отчётами, каталогов.

Подробнее обо всех способах применения: функция ГИПЕРССЫЛКА.

4. “Вставить как гиперссылку”

Команда «Вставить как гиперссылку» позволяет быстро вставить ссылку на ячейку другого листа, диапазон, лист или файл Excel. В отличие от формулы ГИПЕРССЫЛКА, здесь всё делается в два клика — но только после добавления этой команды на ленту или панель быстрого доступа.

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

Как включить команду

• Откройте Файл → Параметры → Настройка ленты или Панель быстрого доступа.
• В списке «Команды не на ленте» найдите «Вставить как гиперссылку».
• Добавьте её в нужную вкладку или панель.

Как использовать

• Скопируйте любую ячейку или диапазон (Ctrl + C).
• Выделите ячейку, куда нужно добавить ссылку.
• Нажмите «Вставить как гиперссылку» на добавленной панели.

В результате получится активная гиперссылка на скопированную ячейку, диапазон или лист. Если копировался диапазон из другой книги — Excel автоматически вставит ссылку на внешний файл.

Если копируемая ячейка была пустой, текстом ссылки станет её адрес, если непустой – текстом ссылки станет её значение.

5. Ссылки на почту, Telegram и WhatsApp в Excel

В Excel можно создавать ссылки, которые откроют нужное приложение с уже заполненными данными. Такие ссылки полезны для общения с клиентами, коллегами, быстрой отправки отчетов, создания чек-листов и дашбордов. Ниже — три популярных типа: почта, Telegram и WhatsApp.

1. Почта (mailto:)

С помощью префикса mailto: можно добавить в ячейку ссылку, при нажатии на которую откроется почтовый клиент с уже подставленным получателем, темой и текстом письма.

mailto:admin@semtools.guru?subject=Вопрос&body=Добрый%20день!%0AУ%20меня%20есть%20вопрос:

Параметры:

• subject= — тема письма
• body= — текст письма
• Пробелы кодируются как %20, перенос строки — %0A

Можно воспользоваться стандартным пунктом “Электронная почта”. Отнюдь не очевидно, но содержание можно вбивать через &body=… в самой теме письма:

Клик по ссылке откроет ваш почтовый клиент по умолчанию (у меня Thunderbird) и автоматически создаст в нём письмо с указанной темой и содержанием:

2. Telegram (tg://resolve)

Ссылки вида tg://resolve?domain=имя открывают профиль Telegram в приложении. Также поддерживается формат для создания ссылки с текстом.

tg://resolve?domain=tiendi&text=Привет!

Можно привязывать такие ссылки к тексту, фигурам и изображениям. Например, в галерее значков можно найти значок, похожий на логотип чата. При клике откроется чат с указанным логином (у меня открываются сохраненные сообщения :)

3. WhatsApp (whatsapp://send)

Формат ссылки whatsapp://send?phone=7XXXXXXXXXX?text=сообщение открывает чат WhatsApp с заранее введенным сообщением.

Например, такая ссылка на ячейке или объекте позволит отправить мне сообщение через десктопное приложение:

whatsapp://send?phone=79015052302&text=Спасибо за крутую инструкцию!

Номер телефона должен быть в международном формате, без плюса.

6. Ссылки к объектам Excel со вкладки “Вставка”

Через вкладку Вставка в Excel можно добавить фигуры, изображения, элементы SmartArt и другие объекты. К большинству из них можно прикрепить гиперссылку. Алгоритм простой: кликаем по объекту правой кнопкой мыши и выбираем в контекстном меню “Ссылка”.

Ниже — какие именно объекты поддерживают ссылки и в каких случаях это может быть полезно.

Ссылки на фигурах

Фигуры – их можно использовать как кнопки для навигации по листам и ячейкам. Идеально подходят в этом случае стрелки. Но могут быть полезны и другие фигуры — всё зависит от вашей фантазии.

Картинки и значки

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

Формулы

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

Ссылки на диаграммах

Мало кто знает, но можно размещать ссылки и на диаграммах. Выделите диаграмму и вызовите окно “Вставка гиперссылки” сочетанием клавиш.

Ссылки на элементах SmartArt

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

• Кликаем правой кнопкой по нужному элементу SmartArt → Гиперссылка
• Можно использовать разные ссылки в разных блоках одного SmartArt-объекта

Выше структура этого сайта со ссылками на ключевые разделы, оформленная как SmartArt.

8. Ссылки на элементах разгруппированных рисунков

Если схематичный рисунок разгруппировать, можно использовать ссылки на каждой его части. Это позволяет создавать элементы интерактивной визуальной навигации по сложным объектам. Удобно для создания практических руководств, в преподавании естественных наук (декомпозиция сложных объектов).

Подход позволяет совместить презентационную часть с объёмными таблицами, которыми сложно было бы оперировать в программах презентации (как PowerPoint).

9. Ссылки на именованные диапазоны и таблицы

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

10. Вставка ссылок на все файлы в папке

Если нужно сделать много ссылок на большое количество файлов в папке (например, картинок, текстовых файлов или других книг), есть несложный способ:

1. Выделите все файлы в папке и нажмите Ctrl + Shift + C (копировать как путь).
2. Теперь все пути к файлам можно вставить на лист Excel.

Это все еще не совсем ссылки, а просто текстовое представление пути к файлам, но ячейки с ним легко сделать кликабельными ссылками – функцией ГИПЕРССЫЛКА или с помощью инструмента “Собрать ссылки” в моей надстройке, который позволяет сделать все ссылки активными, при этом не задействует эту функцию, а делает обычные ссылки.

11. Бонус. Ссылки на все листы книги (содержание)

Пользователи надстройки !SEMTools (Pro, Lite, даже бесплатной версии) могут воспользоваться процедурой, которая значительно упрощает навигацию по книгам с большим количеством листов. Процедура создает в начале книги сводный лист “Содержание” со ссылками на все её листы. Находится в группе инструментов “Обнаружить”:

Файл с примерами

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

Что делает эта функция?

Эта функция использует как минимум два диапазона или массива, перемножая и суммируя их элементы.

Значения в первом диапазоне умножаются на соответствующие по порядку значения во втором и так далее.

Сумма всех таких произведений является результатом расчета.

Наиболее часто функция используется как аналог функции СУММЕСЛИ с множественными условиями.

1. Преимущество СУММПРОИЗВ перед сводными таблицами заключается в том, что в сводных таблицах фильтр для построения среза всегда точное соответствие (как в примере 1). А “фильтр по подписи”, если выводить фильтруемые значения в строки, позволяет фильтровать только по одному условию. СУММПРОИЗВ позволяет использовать все варианты как строкового сравнения (содержит, начинается с, заканчивается на, совпадает), так и численного (больше, меньше, равно, не равно, больше или равно, меньше или равно).
2. Второе преимущество СУММПРОИЗВ перед сводными таблицами в том, что один критерий фильтрации в сводных таблицах нельзя использовать дважды. СУММПРОИЗВ позволяет обращаться к одному и тому же столбцу многократно.
3. Преимущество СУММПРОИЗВ перед функцией СУММЕСЛИМН в том, что условная логика СУММЕСЛИМН – строгое выполнение всех условий, по типу “И”. Возможность использовать любые логические операции над массивами булевых значений дает полнейшую свободу действий по совмещению условий в любых логических комбинациях – И, ИЛИ, ЕСЛИ, НЕ.

Форматирование

Диапазоны должны быть однонаправленными, содержать одинаковое количество элементов и иметь одинаковую размерность по вертикали или горизонтали. Конкретные адреса диапазонов при этом не имеют значения.

Диапазоны могут иметь несколько столбцов и строк одновременно. Обычно используются вертикальные диапазоны шириной 1 столбец.

Синтаксис функции

=СУММПРОИЗВ(ДиапазонИлиМассив1;ДиапазонИлиМассив2;…)

Пример использования

Рассмотрим подробно сложный пример с 4 условиями.

Задача маркетолога – посчитать сумму по четырем условиям:

1. Дата начала недели – 13 апреля или позднее
2. В названии кампании присутствует слово Brand
3. При этом кампания заканчивается на BY (Белоруссия)
4. Или заканчивается на UA (Украина)

Строки, удовлетворяющие всем четырем условиям, выделены зеленым цветом. Как составить формулу, которая учла бы только их?

Формулу можно наблюдать на скриншоте в ячейке H9.

Фактически она является суммой произведения двух диапазонов – массива чисел D3:D34 и массива нулей и единиц. Рассмотрим ее поближе:

Массив нулей и единиц рождается произведением трех формул массива, третья из которых получается сложением двух других формул массива и применением функции ЗНАК.

В каждой строке указано одно из четырех условий, на основе которых эти формулы создаются:

(ЗНАЧЕН(ЛЕВСИМВ($A$3:$A$34;8))>=$H$2)

это выражение создает массив булевых значений ИСТИНА и ЛОЖЬ. Функция ЛЕВСИМВ($A$3:$A$34;8) извлекает из диапазона дат в столбце A первые 8 символов, обозначающие дату, а функция ЗНАЧЕН преобразует их в числовой формат, иначе сравнение с ячейкой H2 не будет происходить корректно, т.к. функция ЛЕВСИМВ – текстовая, и возвращает строку.

НЕ(ЕОШ(ПОИСК($H$4;$B$3:$B$34)))

– это выражение также создает массив булевых значений. Функция ПОИСК ищет внутри каждой ячейки столбца слово “BRAND”, если оно не найдено, возвращается ошибка, а если найдено – позиция его первого символа в строке. Функцией ЕОШ мы превращаем ошибки в единицы, а их отсутствия – в нули. И далее функцией отрицания НЕ получаем нужный нам массив.

Функций “Заканчивается на” в Excel нет, но можно делать простую проверку – если последние символы в строке совпадают со словом, что это, как не вхождение? Эту операцию и производит выражение

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$2))=$J$2)

Тут нам помогают функции ДЛСТР и ПРАВСИМВ. Проверяя, совпадают ли последние 2 символа с ячейкой J2, мы также возвращаем массив булевых значений.

Четвертое условие аналогично третьему, как и его выражение:

(ПРАВСИМВ($B$3:$B$34;ДЛСТР($J$4))=$J$4)

Условная логика ИЛИ воспроизводится методом сложения двух булевых массивов. При этом они становятся числами. ИСТИНА+ЛОЖЬ (и наоборот) дают 1, ЛОЖЬ+ЛОЖЬ дают 0, ИСТИНА+ИСТИНА дают 2. Чтобы не исказить данные, мы используем функцию ЗНАК – она возвращает для положительных чисел 1, а для 0 – 0. Таким образом, мы получаем массив нулей и единиц на основе двух булевых массивов.

Произведение трех массивов дает нам единый массив нулей и единиц, умножая который на значения показов, кликов и скачиваний, мы получаем суммы отфильтрованных таким образом значений. Числа, где условия не соблюдены, “уничтожаются” нулями, а числа, где соблюдены – умножаются на 1 и складываются.

У наблюдательного читателя может возникнуть вопрос, почему мы не пользуемся функцией ИЛИ для объединения последних двух булевых массивов. Ответ: к сожалению, она не сочетает массивы попарно (как и функция И), а рассматривает каждую ячейку массива как равноправный элемент.

Именно поэтому используются арифметические функции.

• вместо И – умножение
• а вместо ИЛИ – сложение + функция ЗНАК.

Другие примеры с СУММПРОИЗВ:

Найти повторяющиеся значения в Excel

Это статья о функции. Смотрите также статью о похожей процедуре и других инструментах: “10 способов сделать гиперссылку в Excel“.

• Что делает функция ГИПЕРССЫЛКА
• Синтаксис
• Примеры использования
  • Гиперссылка на сайт
  • Гиперссылки на файлы и папки
  • Гиперссылки внутри Excel
  • Ссылки на email, мессенджеры, приложения и макросы
  • Динамические гиперссылки
  • Вставка гиперссылок в формулах, массивах и таблицах
    • Гиперссылки с выпадающими списками
• Работа с относительными и абсолютными путями
  • Абсолютный путь
  • Относительный путь
  • Подводные камни
• Интерактивность и оформление
  • Цвет и форматирование
  • Скрытые гиперссылки
  • Гиперссылки с иконками
  • Ссылки с подписями и пояснениями
• Массовое создание ссылок с помощью !SEMTools
• Кейсы и нестандартные применения
  • Ссылки в чек-листах и реестрах
  • Ссылки на фильтрованные отчёты
  • Гиперссылка с переменной логикой (через ВПР)
    • Шаг 1: Таблица ссылок
    • Шаг 2: Выпадающий список
    • Шаг 3: Формула гиперссылки

Что делает функция ГИПЕРССЫЛКА

Функция ГИПЕРССЫЛКА (англ. HYPERLINK) создаёт кликабельную гиперссылку в ячейке Excel — на сайт, документ, лист, файл, папку, мессенджер или даже исполняемый файл. Она удобна тем, что позволяет не просто указать путь, но и задать свой текст гиперссылки — более понятный и красивый.

При клике Excel передаёт путь операционной системе, и та открывает нужный ресурс — будь то сайт, документ, папка или приложение.

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

Синтаксис

=ГИПЕРССЫЛКА(адрес; [подпись])

• адрес — путь к ресурсу (сайт, файл, папка, диапазон, лист, макрос и т. д.).
• подпись — необязательный текст, который будет отображаться в ячейке вместо самого адреса.

Если не указать подпись, Excel отобразит сам адрес. Оба аргумента можно формировать с помощью других функций Excel — подставлять значения из ячеек, собирать URL из частей и пр.

Примеры использования

Гиперссылка на сайт

Какую функцию вы будете использовать, чтобы добавить в ячейку ссылку на веб сайт? Конечно же, ГИПЕРССЫЛКА:

=ГИПЕРССЫЛКА("https://semtools.guru";"Перейти на сайт")

Гиперссылки на файлы и папки

Функция ГИПЕРССЫЛКА позволяет открывать папки и даже запускать `.exe`-файлы — без макросов или надстроек. Это может быть удобно при создании панели управления внутри книги. Однако, поскольку это небезопасно, Excel предупредит вас всплывающим уведомлением.

Гиперссылка на файл Excel:

=ГИПЕРССЫЛКА("C:\Users\User\Documents\Отчет.xlsx";"Открыть отчёт")

На сетевой файл:

=ГИПЕРССЫЛКА("\\Server01\Проекты\План.xlsx";"План проекта")

Ссылка на папку на компьютере:

=ГИПЕРССЫЛКА("C:\Users\User\Downloads\";"Открыть папку загрузок")

Гиперссылки внутри Excel

Гиперссылка на ячейку текущего листа:

=ГИПЕРССЫЛКА("#A1";"К началу")

Гиперссылка на диапазон текущего листа:

=ГИПЕРССЫЛКА("#B2:B10";"К таблице")

Гиперссылка на другой лист:

=ГИПЕРССЫЛКА("#Лист2!A1";"Открыть Лист2")

С указанием имени листа в кавычках (если есть пробелы):

=ГИПЕРССЫЛКА("#'Аналитика 2024'!C5";"Перейти к аналитике")

Гиперссылка на другую книгу, если она открыта:

=ГИПЕРССЫЛКА("[Финансы.xlsx]Данные!A1";"К финансам")

Если книга закрыта — указывается полный путь:

=ГИПЕРССЫЛКА("C:\Отчёты\[Финансы.xlsx]Данные!A1";"Открыть ячейку в файле")

Гиперссылка с динамической вставкой имени листа из ячейки:

=ГИПЕРССЫЛКА("#'"&A1&"'!A1";"Перейти на лист "&A1)

Гиперссылка на именованный диапазон:

=ГИПЕРССЫЛКА("#Продажи_Январь";"К январским продажам")

На именованный диапазон с переходом на другой лист:

=ГИПЕРССЫЛКА("#'Лист1'!ИтогГода";"Итог по году")

На структурированную таблицу (умную таблицу):

=ГИПЕРССЫЛКА("#Таблица1";"Открыть Таблицу1")

Гиперссылка на столбец в умной таблице:

=ГИПЕРССЫЛКА("#Таблица1[Клиент]";"Столбец Клиент")

Гиперссылка на ячейку внутри таблицы по координатам:

=ГИПЕРССЫЛКА("#Таблица1[[#Итого],[Сумма]]";"Итого по сумме")

Гиперссылка на сводную таблицу (если у неё есть имя):

=ГИПЕРССЫЛКА("#Сводная1";"К сводной таблице")

Если имя отсутствует — можно дать имя диапазону вручную и ссылаться на него:

=ГИПЕРССЫЛКА("#PivotRange";"К сводной вручную")

Также можно дать имя области в сводной таблице (например, «Фильтры», «Значения») и ссылаться на неё по имени диапазона.

Ссылки на email, мессенджеры, приложения и макросы

Excel поддерживает не только гиперссылки на сайты или документы, но и специальные протоколы:

mailto:
tg://
viber://

и другие.

Гиперссылка на отправку письма:

=ГИПЕРССЫЛКА("mailto:admin@semtools.guru";"Написать в поддержку")

Гиперссылка на письмо с темой и телом сообщения:

=ГИПЕРССЫЛКА("mailto:admin@semtools.guru?subject=Ошибка&body=Здравствуйте! У меня ошибка!";"Сообщить об ошибке")

Гиперссылка на Телеграм-канал:

=ГИПЕРССЫЛКА("tg://resolve/?domain=semtools";"Телеграм-канал !SEMTools")

Написать личное сообщение пользователю в Telegram:

=ГИПЕРССЫЛКА("tg://resolve/?domain=tiendi&text=Привет, Дмитрий!";"Написать в телеграм автору")

Гиперссылка на WhatsApp-чат по номеру:

=ГИПЕРССЫЛКА("https://wa.me/79015052302";"Написать автору в WhatsApp")

Отправить сообщение через десктопное приложение WhatsApp:

=ГИПЕРССЫЛКА("whatsapp://send?phone=79015052302&text=Спасибо за крутую инструкцию!";"Поблагодарить Дмитрия Тумайкина за инструкцию")

Гиперссылка на звонок (на мобильных устройствах или если настроена связь Windows с телефоном):

=ГИПЕРССЫЛКА("tel:+74951234567";"Позвонить в офис")

Гиперссылка на Viber (если установлен):

=ГИПЕРССЫЛКА("viber://chat?number=%2B79876543210";"Связаться в Viber")

Гиперссылка на приложение, если путь указан явно (например, браузер):

=ГИПЕРССЫЛКА("C:\Program Files\Mozilla Firefox\firefox.exe";"Открыть Firefox")

Запуск системной программы, например, калькулятора:

=ГИПЕРССЫЛКА("C:\Windows\System32\calc.exe";"Открыть калькулятор")

Гиперссылка на макрос (если макрос доступен и определён в книге):

=ГИПЕРССЫЛКА("#Макрос1";"Открыть Макрос1 в текущей книге")

Гиперссылка на макрос в личной книге макросов:

=ГИПЕРССЫЛКА("#PERSONAL.XLSB!Макрос1";"Открыть Макрос1 в личной книге макросов")

Динамические гиперссылки

Гиперссылки можно собирать динамически — в зависимости от значений в ячейках, формул и условий. Это особенно полезно для создания ссылок на поиск, профили, документы, отчёты и другие ресурсы, которые меняются от строки к строке.

Пример: формируем ссылку на поиск сайта по значению в ячейке A2:

=ГИПЕРССЫЛКА("https://ya.ru/search?q="&A2;"Найти в Яндексе: "&A2)

То же с использованием функции ТЕКСТ для форматирования даты и получения курсов валют на заданную дату с сайта Центробанка:

=ГИПЕРССЫЛКА(
"https://cbr.ru/currency_base/daily/?UniDbQuery.Posted=True&UniDbQuery.To="&ТЕКСТ(C1;"дд.ММ.гггг")
;
"Курсы валют на "&ТЕКСТ(C1;"дд МММ гггг")
)

Или гиперссылка в зависимости от условия (с помощью функции ЕСЛИ):

=ЕСЛИ(B2="PDF";
ГИПЕРССЫЛКА("https://site.ru/files/"&A2&".pdf";"PDF-файл");
"")

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

Вставка гиперссылок в формулах, массивах и таблицах

Функция ГИПЕРССЫЛКА отлично работает внутри формул, в том числе в массивах, структурированных таблицах Excel и даже в сочетании с динамическими функциями, такими как ФИЛЬТР, ПОДСТАВИТЬ.

В таблицах Excel (умных таблицах) можно вставлять гиперссылки построчно, используя ссылки на значения из текущей строки:

=ГИПЕРССЫЛКА(
"https://site.ru/card?id="&[@ID]
;
"Карточка "&[@ID]
)

Через ФИЛЬТР можно отобрать строки и вывести только те, где гиперссылки удовлетворяют условию:

=ФИЛЬТР(
A2:A100;
(B2:B100="PDF") * (C2:C100>100)
)

А рядом генерировать гиперссылки на документы:

=ГИПЕРССЫЛКА(
"https://site.ru/files/"&A2
;
"Скачать PDF"
)

Гиперссылки с выпадающими списками

Выбранное значение выпадающего списка (Данные → Проверка данных) можно использовать как параметр в формуле:

=ГИПЕРССЫЛКА(
"https://site.ru/category/"&A1
;
"Открыть "&A1
)

Где A1 — ячейка с выбранным значением. Таким образом, ссылка автоматически меняется при выборе категории или параметра.

Работа с относительными и абсолютными путями

Функция ГИПЕРССЫЛКА может использовать как абсолютные, так и относительные пути к файлам и папкам. От этого зависит, будет ли ссылка работать при перемещении книги или отправке её другим пользователям.

Абсолютный путь

Пример абсолютного пути:

=ГИПЕРССЫЛКА(
"C:\Users\User\Documents\Отчет.xlsx";
"Открыть файл"
)

Такая ссылка работает только на том компьютере, где по указанному пути действительно существует файл. Перенесете книгу на другой компьютер — путь «сломается».

Относительный путь

Если файл, на который ведёт ссылка, находится в той же папке, что и сама книга Excel, можно указать только имя файла:

=ГИПЕРССЫЛКА(
"Отчет.xlsx";
"Открыть соседний файл"
)

Или перейти в подпапку:

=ГИПЕРССЫЛКА(
"архив\2024.xlsx";
"Файл в папке архив"
)

Такие ссылки продолжают работать, даже если папка с книгой будет перемещена или переименована, — главное, чтобы относительное расположение файлов осталось прежним.

Подводные камни

• Если открыть файл Excel не с диска, а из архива или email — относительные пути не сработают.
• Некоторые облачные хранилища (например, OneDrive) подставляют свои абсолютные пути — и ссылки могут перестать работать при скачивании локальной копии.
• Excel может «запомнить» абсолютный путь к файлу при первом открытии, даже если формула указывает относительный — особенно в старых версиях Office.

Рекомендуется тестировать относительные ссылки при передаче файлов другим пользователям или при работе в командах.

Интерактивность и оформление

Гиперссылки в Excel не ограничиваются только текстом с подчёркиванием. Существует множество способов визуального оформления ссылок, чтобы они стали более заметными, понятными или, наоборот, скрытыми от пользователя.

Цвет и форматирование

Excel по умолчанию применяет к гиперссылке стиль “Гиперссылка” — синий цвет и подчёркивание. Его можно изменить:

• Вкладка Главная → Стили → Стили ячеек
• ПКМ на стиле «Гиперссылка» → Изменить

Здесь можно задать цвет, шрифт, подчёркивание и другие параметры для обычного и посещённого состояния.

Скрытые гиперссылки

Иногда нужно не показывать гиперссылку как ссылку. Это можно сделать так:

• Задать ссылке тот же цвет, что и фон (например, белый текст на белом фоне)
• Убрать подчёркивание вручную (через формат)
• Вставить гиперссылку в ячейку с символом, иконкой или числом

Гиперссылки с иконками

Можно использовать гиперссылки со значениями в виде символа или иконки (например, 🔗 или 📎):

=ГИПЕРССЫЛКА(
"https://semtools.guru";
"🔗"
)

Так же можно использовать эмодзи, спецсимволы Unicode.

Ссылки с подписями и пояснениями

Чтобы дать пояснение к ссылке, можно использовать комментарий (примечание):

• ПКМ → Новое примечание
• Впишите назначение, источник, дату или инструкцию

Массовое создание ссылок с помощью !SEMTools

Если у вас установлена надстройка !SEMTools, вы можете генерировать гиперссылки на основе выбранного диапазона буквально в один клик:

• Создайте диапазон со значениями — гиперссылки, адрес, подсказка (если не нужны, можно оставить пустыми)
• Выделите 3 указанных столбца
• Откройте вкладку !SEMTools
• Выберите инструмент Создать гиперссылки

Надстройка автоматически объединит тексты, адреса и подсказки ссылок. Работает со всеми перечисленными выше вариантами – ссылками на сайты, Telegram, файлы, папки и внутренние элементы книги.

Основные преимущества автоматизации перед ручным использованием формул —

• Скорость генерации
• Отсутствие ошибок в ссылках даже при больших объёмах данных.
• Всплывающие подсказки у ссылок

Процедура доступна в бесплатной версии !SEMTools.

Кейсы и нестандартные применения

Гиперссылки в Excel — это не только ссылки на сайты. Они становятся особенно полезными в комбинации с аналитикой, фильтрами, отчётами и интерактивными таблицами.

Ссылки в чек-листах и реестрах

Если у вас есть таблица задач, чек-лист или журнал, гиперссылки помогут быстро открывать связанные документы, шаблоны, формы, папки или даже запускать нужные процессы. Пример:

=ГИПЕРССЫЛКА(
"https://docs.site.ru/task?id="&A2
;
"Задача #"&A2
)

Ссылки на фильтрованные отчёты

Если в вашей системе отчетов есть поддержка фильтрации по URL (например, Power BI, Looker Studio, Metabase), вы можете динамически формировать ссылки с фильтрами:

=ГИПЕРССЫЛКА(
"https://dashboard.site.ru/report?manager="&A2
;
"Отчёт по менеджеру "&A2
)

Гиперссылка с переменной логикой (через ВПР)

Один из самых удобных способов — использовать выпадающий список с текстами ссылок, а URL брать из справочной таблицы поможет функция ВПР. Это удобно для создания навигационных меню внутри Excel-книги и экономии пространства.

Шаг 1: Таблица ссылок

Создайте вспомогательную таблицу где-нибудь рядом или на отдельном листе (его можно даже скрыть):

Шаг 2: Выпадающий список

Создайте в ячейке, например A2, список выбора:

• Данные → Проверка данных → Список
• Источник: Купить,Скачать,Автор,Как работает,История версий

Шаг 3: Формула гиперссылки

В ячейке рядом (например, C2) используйте формулу:

=ЕСЛИОШИБКА(
ГИПЕРССЫЛКА(
ВПР(A2;$F$2:$G$6;2;ЛОЖЬ)
;A2)
;"")

Здесь $E$2:$F$6 — диапазон таблицы ссылок, A2 — выбранный текст. В результате в C2 появится активная гиперссылка с подписью, совпадающей с выбранным пунктом.

Если пользователь выбирает «История версий» — формируется гиперссылка: https://semtools.guru/ru/version-history