Как извлечь текст примечания в ячейку excel

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

К сожалению, встроенной формулы в Экселе на этот случай нет, но нам поможет Visual Basic for Application. В пошаговой инструкции я расскажу Вам, как создать пользовательскую функцию и как ее использовать. Итак, пункт первый:

1.Создание пользовательской функции

Ее создание, аналогично созданию макроса в VBA, поэтому проделайте многим знакомые операции:

• Нажмите Alt+F11. Должен открыться редактор Microsoft Visual Basic for Application
• Создайте новый модель. Для этого зайдите в меню Insert и выберите пункт Module.
• В открывшийся модель вставьте код пользовательской функции:

Function Get_Text_from_Comment(rCell As Range) As String
    Dim sTxt As String
    Application.Volatile True
    On Error Resume Next
    sTxt = rCell.Comment.Text
    Get_Text_from_Comment = Mid(sTxt, InStr(sTxt, «:») + 2)
End Function

2.Использование пользовательской функции

• Перейдите назад в книгу Excel.
• Выделите любую свободную ячейку и вызовите мастер вставки функции
• Перейдите в категорию «Определенные пользователем», выберите функцию Get_Text_from_Comment и нажмите Ок.
• Далее укажите ту ячейку, которая содержит комментарий и нажмите Ок.

Готово. Мы получили текст из примечания в ячейку.

Важное замечание: Если Вы изменили текст примечания, то нажмите после этого F9, для того что бы обновилось содержание ячейки в соответствии с корректировкой примечания.

Не забывайте оставлять Ваши комментарии. Они очень важны для нас.

Хитрости »

24 Май 2011              46279 просмотров

Как получить текст примечания в ячейку?

Есть таблица по платежам. В ячейках определенных столбцов в примечаниях(вкладка Рецензирование —Создать примечание) записывается дополнительная информация по платежу. Например, номер договора, на основании которого был произведен платеж. И теперь необходимо отобрать записи только по определенным договорам. Ячеек несколько сотен, просматривать и выписывать договора из комментариев вручную похоже на одну из разновидностей древнеримских пыток. Однако при помощи VBA сделать это совсем просто.

Создадим простую функцию пользователя:

Function Get_Text_from_Comment(rCell As Range)
    On Error Resume Next
    Get_Text_from_Comment = rCell.Comment.Text
End Function

Синтаксис вызова функции с листа Excel:
=Get_Text_from_Comment(A1)
A1 — ячейка с примечанием, текст которого необходимо получить. Если комментарий в ячейке отсутствует, то функция вернет пусто.
Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: необходимо скопировать текст кода выше, перейти в редактор VBA(Alt+F11) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций, отыскав её в категории Определенные пользователем (User Defined Functions).

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

Function Get_Text_from_Comment(rCell As Range) As String
    Dim sTxt As String
    On Error Resume Next
    sTxt = rCell.Comment.Text
    Get_Text_from_Comment = Mid(sTxt, InStr(sTxt, ":") + 2)
End Function

Синтаксис вызова с листа Excel такой же, как и в функции выше. Просто указываете внутри функции ссылку на ячейку.
=Get_Text_from_Comment(A1)

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

Если текст из примечаний необходимо записать в те же ячейки одним махом и удалить после этого все примечания, можно использовать следующий код:

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          http://www.excel-vba.ru
' Purpose: Процедура записи текста из примечаний в ячейки выделенного диапазона
'---------------------------------------------------------------------------------------
Sub CommentsToCell()
    Dim sTxt As String, res As String, rc As Range, rr As Range
    Dim IsDelAuthor As Boolean, IsDelComment As Boolean, IsReplaceCellVal As Boolean
    'запрашиваем параметры
    If MsgBox("Оставлять автора комментария?", vbQuestion + vbYesNo, "www.excel-vba.ru") = vbNo Then
        IsDelAuthor = True
    End If
    If MsgBox("Заменять значение, если в ячейке с комментариями уже есть текст?" & vbNewLine & _
              "ДА(Yes) - значения ячеек будут заменены текстом комментариев" & vbNewLine & _
              "НЕТ(No) - к имеющимся значениям будет добавлен текст комментария", vbQuestion + vbYesNo, "www.excel-vba.ru") = vbYes Then
        IsReplaceCellVal = True
    End If
    If MsgBox("Удалять комментарии после обработки?", vbQuestion + vbYesNo, "www.excel-vba.ru") = vbYes Then
        IsDelComment = True
    End If
    On Error Resume Next
    'получаем в выделенном диапазоне только ячейки с комментариями
    Set rr = Selection.SpecialCells(xlCellTypeComments)
    If rr Is Nothing Then
        MsgBox "В выделенном диапазоне нет ячеек с комментариями", vbCritical, "www.excel-vba.ru"
        Exit Sub
    End If
    On Error GoTo 0
    Application.ScreenUpdating = False
    'цикл по всем ячейкам с комментариями
    For Each rc In rr.Cells
        sTxt = rc.Comment.Text
        If IsDelAuthor Then
            res = Mid(sTxt, InStr(sTxt, ":") + 2)
        Else
            res = sTxt
        End If
        If IsReplaceCellVal Then
            rc.Value = res
        Else
            rc.Value = rc.Value & Chr(10) & res
        End If
    Next
    If IsDelComment Then
        rr.ClearComments
    End If
    Application.ScreenUpdating = True
    MsgBox "Комментарии записаны", vbCritical, "www.excel-vba.ru"
End Sub

Код необходимо так же скопировать и вставить в стандартный модуль(Переходим в редактор VBA(Alt+F11) —Insert —Module).
Выделить диапазон ячеек, комментарии из которых необходимо перенести, нажать Alt+F8 и выбрать код CommentsToCell. Код содержит несколько параметров:

• Сначала необходимо будет выбрать оставить ли автора комментария при считывании текста из комментария. Если выбрать да — весь текст примечания будет перенесен как есть. Если выбрать Нет — то из комментария будет отсечена первая строка до символа двоеточия(:). Именно так по умолчанию Excel обозначает автора.
  Этот параметр нужен, если в ячейках нет автора.
• Далее будет запрос: оставить значение в ячейках и дописать к ним текст примечания или заменить существующие значения в ячейке на текст комментария. Может пригодиться, если в ячейках записаны суммы платежей и надо добавить к ним из комментария номер договора, не убирая сами суммы.
• И последний запрос будет: удалять комментарии из ячеек после записи текста из них в ячейки или оставить. Если выбрать да — то после обработки всех выделенных ячеек комментарии будут удалены. Это может пригодиться, если комментариев много. Их удаление может существенно облегчить файл.

Также см.:
Что такое функция пользователя(UDF)?
Как скопировать картинку из примечания?
Создание примечаний

Статья помогла? Поделись ссылкой с друзьями!

    Видеоуроки

Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

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

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

Как сделать поиск по примечаниям в Excel?

Вызываете окно Найти и заменить (ctrl + f), нажимаете кнопку Параметры

В открывшемся поле Область поиска выбираете Примечания, как показано на картинке в заголовке страницы.

Жмете Найти далее или Найти Все — находим нужную информацию.

Вот в принципе и все.

Но! Примечания — это средство рецензирования, а не хранения информации. Поэтому если примечаний много, то их лучше хранить в отдельных ячейках. Что делать если примечаний уже много?

Как скопировать примечание в ячейку?

Чтобы копировать примечания между ячейками вам достаточно просто скопировать ячейку.

Но для того, чтобы скопировать значения примечания ячейки A1 в другую ячейку, например А2, придется это делать руками — т.е. копируем текст примечания и вставляем в ячейку. Как быть если примечаний сотни? Не вручную само собой.

Наверное самое простое — это создать пользовательскую формулу в VBA.

Включаем макросы, заходим в редактор макросов Вкладка Разработчик — Просмотр кода и вставляем в открывшееся окно код по примеру ниже

Function prim (Ячейка As Range)    prim =Ячейка.Resize(1, 1).comment.Text End Function

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

Теперь в нужную ячейку введите формулу

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

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

Удачи!

Извлечение текста комментариев в ячейку

Добрый день, товарищи! Сегодня поговорим о том, как работать с комментариями в ячейке.

Все мы знаем, что примечание добавляется очень просто — щелчок на ячейке правой кнопкой мыши и выбор пункта «Добавить примечание».

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

Добавление комментариев по условию мы рассматривали в этой статье.

Добавление комментария в ячейку с помощью Vba.

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

Sub add_comment()
With Worksheets(1).Range(«a2»).AddComment
.Visible = False
.Text «просмотрел » & Date
End With
End Sub

Что происходит в этом случае — на листе 1 в диапазоне ячеек A2 добавляется комментарий, далее зовутся свойства Visible (комментарий будет скрыт до наведения), Text собственно сам текст примечания, параметр Date проставит текущую дату.

После выполнения макроса получится вот это.

Удобно, но как быть если нужно вставить комментарий для нескольких ячеек или области? Изменим код.

Sub add_comments()
For xNum = 1 To 10
Range(«A» & xNum).AddComment
Range(«A» & xNum).Comment.Text Text:=»Комментарий » & xNum
Next
End Sub

Этот код добавит примечание «Комментарий» ко всем ячейкам от А1 до А10.

Извлечение текста комментария в ячейку

Для извлечения текста комментария в ячейку воспользуемся пользовательской функцией.

Function GetComment(rCell As Range) As String
GetComment = rCell.Comment.Text
End Function

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

Текст ячейки в комментарий

Sub CellTextToComment()
 Dim c As Range
 If TypeName(Selection) <> «Range» Then Exit Sub
 For Each c In Selection
 c.Comment.Text Text:=c.Text
 Next c
End Sub

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

Конечно, код можно усовершенствовать — дерзайте! Если что — вот тут хорошая статья, может вам пригодиться.

Всем удачи!

�������� ������ ������ � Excel