Нарыл в загашнике еще одну интересную функцию для Excel. Возникла у меня нужда дать пользователю самому составить простенькую формулу с одной переменной (их, на самом деле строго две, но принцип понятен и на одной). Составить прямо на листе, не после знака «=», а чтобы видно было. Результат вычисления используется в многих других местах.
' strVar - значение переменной
' strFormula - строка, содержащая саму формулу, место, куда надо
' подставить переменную, обозначено символами VAR
Function sheetFormula(strVar As String, strFormula As Variant) As Variant
' Если формула пустая, не тратим ресурсы
If strFormula = "" Then Exit Function
' Юзер - дурак, уберем крайние пробелы
strFormula = Trim(strFormula)
' Чем возиться с параметрами замены, проще все перевести
' в верхний регистр
strFormula = UCase(strFormula)
' Подставляем переменную простой заменой
strFormula = Replace(strFormula, "VAR", CStr(strVar))
' Мой (ну почему только мой?!) фирменный финт:
' заменяем запятую на точку, позволяя пользователю
' использовать любой из этих знаков
strFormula = Replace(strFormula, ",", ".")
' Вычисляем все с помощью функции Evaluate.
' Конструкция выглядит загадочно, но только так я
' добился от этой функции стабильной работы.
' Что она там себе думает, не знаю.
sheetFormula = Evaluate(CStr(strFormula) + "+0")
End Function
Теперь можно попросить пользователя в одной ячейке вбить значение переменной, в другой — формулу, а результат использовать по всей книге.
Между прочим, кого-то из вас поджидает вакансия в Проф-Медиа Business Solutions. Кого?
Также читайте всю серию постов по VBA.
Посмотрим, как делать собственные функции в дополнение к встроенным в Excel.
Предположим, у нас есть таблица с двумя столбиками с парами взаимосвязанных параметров. Понаблюдали мы раз десять за чем там надо наблюдать, записали результаты, увидели, что зависимость линейная, и стало нам лень остальные двести измерений делать. Первый столбик мы заполним от балды, введем какой-нибудь коэффициент, а для второго значения напишем функцию.
Читать запись полностью »
Продолжим разбирать простенькие примеры скриптов на VBA для Excel. Первая часть.
Иногда возникает надобность добавить одно число к куче разных ячеек, или домножить их на какой-то коэффициент.
Читать запись полностью »
Пришла мысль выложить мои маленькие скрипты для Excel. Постараюсь идти от простого к сложному (ну... от маленького к большему) - вдруг кому изучить поможет. Впрочем, скриптов у меня мало.
Итак, в приложения Microsoft Office встроен язык Visual Basic for Application (VBA) - близнец обычного Visual Basic (по крайней мере до 6 версии VB включительно). Его можно и нужно использовать для автоматизации рутинных операций. Все нижеизложенное относится к Excel 2003.
Читать запись полностью »
Кто-нибудь из читателей разбирается в исправлениях при совместной работе в Word? Вот здесь мы зашли в тупик.
Что делать, если вам нужно найти на листе Excel звездочку или знак вопроса? Проблема в том, что они являются подстановочными знаками и выражение в строке Найти «*123?» означает «любое количество любых символов, потом 123, потом один какой-нибудь символ». Чтобы Excel не считал эти символы подстановочными, а искал как есть, их надо экранировать. У Microsoft свой собственный путь, поэтому экранируются они с помощью тильды, то есть выражение «~?» означает знак вопроса сам по себе.
У Microsoft не один путь. Возьмите Word. Здесь обработка подстановочных знаков по умолчанию отключена, включить ее можно, нажав Больше и отметив галку Подстановочные знаки в диалоге поиска. А если вам нужно смешивать подстановку и знаки вопроса, то экранируются они обратным слешем, вот так: «\?». Предатели писали Word.
У Microsoft не один путь, а целый лабиринт (они патентуют результаты работы генератора случайных чисел, что ли?). В Visio наоборот, чтобы символ стал знаком подстановки, его надо экранировать так: «^?». Впрочем, они могут отмазаться тем, что не успели Visio переписать, продукт-то изначально не их.
Боюсь смотреть другие программы.
Вы помните, что стили в MS Word (как минимум 2003) можно назначать выделенному тексту с помощью горячих клавиш? В боковой панели Стили и форматирование (Формат > Стили и форматирование...) выберите в контекстном меню стиля пункт Изменить..., в появившемся окне Формат > Сочетание клавиш... Разгуляева фантазии тут не выйдет: многие сочетания уже заняты, а собирать конструкции типа Ctrl + Alt + Shift (это массаж для пальцев, не обращайте внимания) Word не позволяет.
И вы помните, что прежде, чем форматировать что-то напрямую, без применения стилей, надо шесть раз подумать, потом выйти на улицу и обежать вокруг фонарного столба, выкрикивая мантру «да, это действительно нужно»? Иначе щастя не будет.
Вспомнилось, что были у меня запутки с округлением в Excel поначалу. Так что для только открывших двери в мир ячеек и формул это напоминание может быть полезным. Не путайте округление с отображением. Если посмотреть на вкладку Число окна Формат ячеек, можно заметить, что для числового формата задается произвольное количество десятичных знаков. Если установлен ноль, то отображаться в ячейке будет число, округленное до целых, какая бы дробь там на самом деле не была. Но только отображаться! В дальнейшие расчеты пойдет число в первозданном виде, дробь - так дробь. И не удивляйтесь потом, если где-нибудь окажется, что 459 не равно 459. Потому что на самом деле это 458,93 и 459,2.
Отсюда вывод: всегда, когда речь идет о целых числах (или числах с заданной точностью) используйте округление. Это формула ОКРУГЛ в русской версии Excel. Она имеет два аргумента: само число, которое требуется округлить и число разрядов. С числом разрядов просто: 2 - до сотых, 1 - до десятых, 0 - до целых, -1 - до десятков и так далее в обе стороны. Единственное исключение из этого правила - промежуточные итоги, которые будут использоваться в дальнейших расчетах. Если округлять каждый результат в цепочке вычислений, может накопиться заметная ошибка, так что округляйте с умом.
Настроить внешний вид Outlook или быстро выполнить какое-нибудь действие из арсенала его возможностей можно и спомощью командной строки. Переведенная на русский таблица поможет вам в этом. Правда, для себя я ничего кроме Outlook.exe /c ipm.note /m emailname для создания письма с заполненным полем «Кому» так и не придумал. Видимо, оттого, что почти не пользуюсь этой мощной софтиной.
Маленький такой трюк для читателей, которым вместо чтения приходится набивать вручную таблицы Excel. Предположим есть таблица такого вида:
множество строк которой надо заполнить собственными пальцами (посредством оных, я имею в виду).
Облом номер раз: подитог посередине, он вычисляется сам, и нам прыгать через него надо. Решение: выделяем все ячейки на листе (сойдет Ctrl + A), идем в Формат ячеек, и на вкладке Защита отключаем пункт Защищаемая ячейка. Затем выделяем столбцы, содержащие формулы (и все, что менять не надо, но под ноги лезет), и там же включаем пункт Защищаемая ячейка обратно. Теперь Сервис > Защита > Защитить лист… Смотрим, чтобы выделение незаблокированных ячеек было разрешено, а заблокированных запрещено и нажимаем ОК. Теперь через заблокированные ячейки можно перепрыгивать, если перемещаться, нажимая на курсорные клавиши.
Облом номер два: неудобно как-то. Особенно к началу следующей строки возвращаться. Но если мы защиту листа не ставили или - внимание! - разрешили выделение заблокированных ячеек, то очень удобным становиться перемещение с помощью клавиши Tab. Выделяем левую верхнюю ячейку из тех, что надо заполнять, и к следующей ячейке переходим, нажимая Tab, а к началу следующей строки - Enter. В случае использования защиты, заблокированные ячейки по-прежнему перепрыгиваются.
Все сказанное относится к Excel 2003. В предыдущих версиях в общих чертах тоже должно работать.
Ексельная функция для отображения дня недели. ДЕНЬНЕД выдает номер дня недели, а ВЫБОР превращает номер в значение из списка. А1 - ваша ссылка на ячейку, содержащую дату.
=ВЫБОР(ДЕНЬНЕД(А1;2);"понедельник";"вторник";"среда";"четверг"; "пятница";"суббота";"воскресенье")
По тому же принципу можно задать имя месяца.
Впрочем, если вам не нужно строковое представление дня недели для каких-либо операций (мне нужно для сортировки, чтобы собрать в кучку дни недели), а требуется только показать слово «понедельник» на экране, то все проще. В контекстном меню ячейки выберите Формат ячеек, вкладку Число, в левом списке (все форматы) и добавьте строку «ДДДД» в нужное место в поле ввода Тип.
Случается, что нужно получить текст в виде растра. У меня случается, по крайней мере. Что для этого использовать? Ничего удобнее - для эпизодической работы - MS Excel я так и не нашел. Из него вы можете копировать ячейки и вставлять их в программу, которая согласна принимать из буфера обмена растры. Если согласна, но не принимает, так как считает (и правильно, вообще-то), что вы вставляете таблицу или текст, то можно воспользоваться промежуточным звеном, тем же Paint, например.
Теперь дьявольщина деталей. Не забывайте отключать сетку, если требуется. В Excel Сервис > Параметры... > вкладка Вид, снять галочку с пункта сетка. Если в операционной системе используется сглаживание шрифтов ClearType, то растр получится размытым. Чтобы отключить сглаживание, идите в Свойства: Экран, вкладка Оформление, щелкните по кнопке Эффекты... и в появившемся окне выберите метод сглаживания шрифтов Обычный. Готовить форматированные тексты удобнее в Word, а потом копировать в Excel, иначе вам легко будет изменить оформление целой ячейки, но отдельного слова - замучаетесь.
Для екселянтов. Пользователей MS Excel, конечно, а вы что подумали? «Экий вы екселянт, сударь!»
Читать запись полностью »
Всенародно любимый Microsoft тоже использует wiki-разметку. Но, как это принято в данной корпорации, не всю, не всегда и не везде. Однако если вам приходится быстро набивать текст, сразу раскидывая выделение жирным и курсивом по словам, то это становится удобным (при условии, что вы используете Word). Вы можете печатать *жирный* или _курсив_ и получите соответственно жирный или курсив.
Впрочем, секунду! В версии 2003 эта функция по умолчанию отключена. Выберите пункт меню Формат > Автоформат и в появившемся диалоговом окне щелкните на кнопке Параметры... На вкладке Автоформат отметьте пункт *полужирный* и _курсив_. Вот теперь можно печатать.
Кроме того не забывайте про возможность набрать в новой строке три дефиса (---) и нажать Enter. Дефисы превратятся в горизонтальную линию. Также попробуйте такие сочетания: ***, ===, ___.
Ну и конечно всегда стоит использовать великие и ужасные Ctrl + b, Ctrl + i и Ctrl + u. Это работает почти везде, где есть форматированный текст.
Часто ли вам приходится копировать табличные данные из каких-нибудь буржуинских источников в Excel? Что-нибудь в таком духе:
Попробуйте скопировать эти числа и вставить в таблицу Excel. Ага! Даты и текстовые строки. Вообще-то это были числа с десятичной точкой...
Все это результат недопонимания, которого во времена наших дедов и отцов было ничуть не меньше, чем теперь. Не договорились они, каким символом отделять целую часть от дробной - точкой или запятой. А кроме того у некоторых табличных процессоров ума палата, и они переконвертируют при вставке все, что видят. И не всегда правильно.
Отменим эту вставку и сходим в Сервис > Параметры... На вкладке Международные уберем галочку с пункта Использовать системные разделители и запишем точку в качестве разделителя целой и дробной частей. Нажмем ОК и вставим, наконец, наши цифирки на лист. А потом сразу включим Использовать системные разделители обратно. Вставленные значения сохранились как числа, и теперь будут отображаться с заданным в системе разделителем. Хоть со смайликом, если вам это нравится.
Откопал недавно две интересные штучки в VBA Excel 2003. Первая - свойство ThisCell объекта Application. используется внутри функции, вызываемой с листа (то есть записанной в ячейке, как обычная формула листа). Указывает на ячейку, из которой и вызывалась функция. Очень удобно, если надо формировать многоэтажные ссылки относительно положения самой формулы на листе. И вторая - метод Evaluate, позволяющий вычислять выражения, заданные строкой. Переменные только не подставляет, но можно просто в строке заменять имя переменной на значение. Что-то он там еще умеет…
Также читайте всю серию постов по VBA.
