Хитрости » 15 Май 2015 Дмитрий 36378 просмотров
- Знать что такое формула
- Знать что такое формула массива
- Не лениться заглядывать в справку по неизвестной функции. Как это сделать: ставим курсор мыши на интересующую формулу и жмем F1(в Excel 2003 и более ранних версиях только так можно). Начиная с Excel 2007 можно еще и иначе: ставим курсор внутрь функции — появится подсказка по функции. После чего нажимаем на имя функции из подсказки:
Чем это поможет? Чтобы понять как работает формула в целом, необходимо знать, что делает каждая функция в неё вложенная и для чего предназначены её аргументы хотя бы в общих чертах. - Не обязательно, но желательно скачать файл, приложенный к статье Как получить список уникальных(не повторяющихся) значений?, чтобы наглядно пройти все шаги, описанные ниже
Скачать пример:
Tips_All_ExtractUnique.xls (108,0 KiB, 15 604 скачиваний)
Если Вы не знакомы с функциями, используемыми в приведенной выше формуле и хотите разобраться — необходимо просмотреть справку по ним, иначе работу формулы не поймете даже с пояснениями
Вот теперь можно начать потрошить формулу. В принципе, самый сложный этап уже пройден. Теперь остается только воспользоваться встроенным средством Excel — окно просмотра этапов вычислений формулы. Выделяем ячейку с нужной формулой и:
для пользователей Excel 2007 и более поздних версий:
вкладка Формулы-группа кнопок Зависимости формул-Вычислить формулу(Formulas-Formula Auditing-Evaluate Formula)
для пользователей Excel 2003:
Сервис-Зависимости формул-Вычислить формулу
Появится форма
После каждого нажатия на кнопку Вычислить(Evaluate) будет произведен очередной этап вычислений формулы и в окне формы будет отображен этот этап. Вычисляемая в текущий момент часть формулы(этап) подчеркивается одинарной линией.
Что следует знать: сначала вычисляется самая глубоко вложенная функция, а уже потом самая первая. Самая первая и основная функция у нас будет ИНДЕКС, а самая глубоко вложенная — СЧЁТЕСЛИ. Поэтому на примере нашей формулы следующим этапом будет вычисление функции СЧЁТЕСЛИ и в скобках будет показан результат для этой функции: {0:0:0:0:0 … 0:0:0}. Т.е. для каждого значения диапазона $A$2:$A$51 будет выведено количество — сколько раз это значение встречается в диапазоне $C$1:C1. Т.к. это первая строка формулы — то будут все нули:
Далее будет произведено вычисление логического выражения =0: сравнение результата функции СЧЁТЕСЛИ с нулем. Результатом будет ИСТИНА или ЛОЖЬ.
Этот результат(ИСТИНА, ЛОЖЬ) обрабатывается далее функцией ЕСЛИ. А в ЕСЛИ у нас условие: если СЧЁТЕСЛИ равно нулю (т.е. если результат ИСТИНА), то в ЕСЛИ возвращаем номер строки(СТРОКА($A$1:$A$50)), если нет — то вернет ЛОЖЬ.
Т.к. функция НАИМЕНЬШИЙ работает только с числами, игнорируя любые другие значения, то она не будет учитывать ЛОЖЬ(т.к. это логическое значение, а не число), а будет отбирать только числа — что и ложится в основу формулы.
Чтобы в этом примере было более просто разобраться(насколько это возможно), коротко расскажу о принципе работы этой формулы: если значение из диапазона $A$2:$A$51 встречается в диапазоне вывода формулы(на строку выше) $C$1:C1, то СЧЁТЕСЛИ вернет не нулевое значение и получится ЛОЖЬ. Если такого значения ещё нет — будет нуль и в НАИМЕНЬШИЙ будет передан номер строки. А уже номер строки передается в ИНДЕКС, которая возвращает непосредственно значение по номеру строки. Чтобы более точно понять подобные формулы надо рассмотреть не только формулу из первой ячейки, но и пару следующих.
Помимо кнопки Вычислить в этом окне есть и другие: Шаг с заходом(Step In) и Шаг с выходом(Step Out). Делают они почти тоже самое, но доступны не для всех видов формул, а лишь для тех, в которых участвуют ссылки на ячейки с другими функциями. Если вычисляемая в настоящий момент функция содержит внутри ссылку на ячейку, в которой записана другая функция или формула — то Шаг с заходом(Step In) выводит в окно вычисления эту функцию(формулу) и активирует ячейку с этой формулой. При этом доступна эта кнопка становится лишь тогда, когда при вычислении основной формулы шаг вычисления доходит до этой самой ссылки на вложенную формулу. Шаг с выходом(Step Out) при этом возвращает к вычислению предыдущей формулы.
Небольшой практический совет: если используете инструмент Вычислить формулу для поиска ошибки в своей формуле для поиска ошибки и в формуле используются слишком большие диапазоны, то просматривать по шагам такую формулу неудобно. Чтобы было проще — можно уменьшить диапазоны ячеек до 10, выделить ячейку с ошибочным результатом и посмотреть этап вычисления — все участвующие ячейки будут на виду и проще будет понять где ошибка.
Конечно, если формулу создал кто-то другой такой подход не всегда справедлив для сложных формул, т.к. изменение диапазонов без понимания для чего они может привести к нерабочей формуле и в этом случае смотреть этапы вычисления бесполезно.
Есть еще одна возможность анализировать этапы вычислений. Необходимо выделить ячейку с нужной формулой, перейти в строку формул и там выделить фрагмент формулы, результат вычисления которого требуется получить:
после чего, не снимая выделения нажимаем клавишу F9. Выделенный блок формулы будет вычислен и результат будет помещен на место выделенного блока формулы:
Мне этот метод нравится меньше, т.к. он не показывает именно шаги вычисления, а вычисляет разом выделенный блок. Поэтому его можно применять в случаях, когда порядок вычисления известен и надо лишь убедиться, что интересующий блок формулы работает правильно.
Статья помогла? Поделись ссылкой с друзьями! Видеоуроки
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx
пример
Комментарий отмечен апострофом ( ‘ ) и игнорируется при выполнении кода. Комментарии помогают объяснить ваш код будущим читателям, включая вас самих.
Поскольку все строки, начинающиеся с комментария, игнорируются, их также можно использовать для предотвращения выполнения кода (при отладке или рефакторе). Помещение апострофа ‘ перед тем, как ваш код превратит его в комментарий. (Это называется комментированием строки.)
Sub InlineDocumentation() ‘Comments start with an «‘» ‘They can be place before a line of code, which prevents the line from executing ‘Debug.Print «Hello World» ‘They can also be placed after a statement ‘The statement still executes, until the compiler arrives at the comment Debug.Print «Hello World» ‘Prints a welcome message ‘Comments can have 0 indention…. ‘… or as much as needed »» Comments can contain multiple apostrophes »» ‘Comments can span lines (using line continuations) _ but this can make for hard to read code ‘If you need to have mult-line comments, it is often easier to ‘use an apostrophe on each line ‘The continued statement syntax (:) is treated as part of the comment, so ‘it is not possible to place an executable statement after a comment ‘This won’t run : Debug.Print «Hello World» End Sub ‘Comments can appear inside or outside a procedure
Ну, приблизительно так
‘Работа с комментариями
‘1) меняем штрифт у комментария в заданной ячейке
Range(«D10»).Comment.Shape.TextFrame.Characters.Font.Size
‘2) изменяем размер окошка всех примечаний на листе ровно под текст (AutoSize)
Sub All_Comments_Size_Change()
Dim iComment As Comment
For Each iComment In ActiveSheet.Comments
iComment.Shape.TextFrame.AutoSize = True
Next iComment
End Sub
‘3) добавляем комментарий в ячейку и меняем его шрифт
Sub ChangeFontInComment()
With Range(«B2»)
.ClearComments
.AddComment
.Comment.Text «бла-бла-бла»
With .Comment.Shape.TextFrame.Characters.Font
.Name = «Times New Roman»
.Size = 14
.Bold = True
End With
End With
End Sub
‘4) меняем шрифт у всех комментариев
Sub All_Comments_Font_Change()
Dim iComment As Comment
For Each iComment In ActiveSheet.Comments
With iComment.Shape.TextFrame.Characters.Font
.Name = «Times New Roman»
.Size = 14
.Bold = True
End With
Next iComment
End Sub
‘5) устанавливаем высоту и ширину окна примечания
Sub Change_Size_Comment_Window()
With Range(«A1»)
.AddComment «Bla-bla-bla»
With .Comment.Shape
.Width = 100
.Height = 200
.Visible = True
End With
End With
End Sub
‘6) устанавливаем высоту и ширину для всех примечаний
Sub Размер_Комментарий()
Dim iComment As Comment
For Each iComment In ActiveSheet.Comments
iComment.Shape.TextFrame.AutoSize = True
iComment.Shape.Height = iComment.Shape.Height + 10
iComment.Shape.Width = iComment.Shape.Width + 15
Next iComment
MsgBox «Размеры комментарий исправлены!», vbInformation, «Комментарии»
End Sub
‘7 устанавливаем размер окна комментария AutoSize и меняем свойство окно на «Перемещать, но не именять размеры»
Sub AutoSizeMoveDontChangeSizeComments()
‘Свойство XlPlacement объекта Shape может быть одним из этих констант (Формат примечания/Свойства/)
‘- xlFreeFloating — не перемещать и не изменять размеры
‘- xlMove — перемещать, но не именять размеры
‘- xlMoveAndSize — перемещать и изменять объект вместе с ячейками
Dim iComment As Comment
For Each iComment In ActiveSheet.Comments
With iComment.Shape
.TextFrame.AutoSize = True
.Placement = xlMove ‘перемещать, но не именять размеры
End With
Next iComment
MsgBox «Все комментарии обработаны!», 64, «Конец»
End Sub
‘8 Добавление даты в комментарий
Private Sub Worksheet_Change(ByVal Target As Range)
‘если изменения в диапазоне A1:A10
If Not Intersect(Target, Range(«A1:A10»)) Is Nothing Then
‘если выделили больше одной ячейке, то выход
If Selection.Cells.Count > 1 Then Exit Sub
Target.NoteText Text:=Application.UserName & Chr(10) & «Дата: » & Now
End If
End Sub