Excel - макросы на заказ

Сцепки в Excel для сортировки и суммирования

2013-03-29T17:33:00.003+02:00

Привет!

Если вы работаете с большими объемами данных в Excel, уверен, что вы встречались с задачей выбора данных, соответствующих одновременно нескольких условиям. Решить эту задачу можно несколькими способами: написать макрос, сделать сводную таблицу или ...
Я хочу рассказать вам о несложном и весьма эффективном приеме для выбора, подсчета и суммирования данных по большому количеству условий.
Почему не воспользоваться сводной таблицей? Сводной таблицей удобно пользоваться, когда нужно выбрать данные по 3-5 условиям, не более. Далее сводная таблица становится весьма громоздкой, да и дальнейшая обработка записей, соответствующих всем условиям, становится невозможной в сводной таблице.
Почему мы не будем решать этот вопрос написанием несложного макроса? Предлагаемое в этой статье решение будет работать на основании формул, т.е. пересчет будет совершаться полностью и автоматически при изменении любой ячейки с данными. Конечно, можно то же самое проделать и макросом... но на мой взгляд это дольше и менее удобно.

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

Предположим, у нас есть база депозитов некоего банка, и нам нужно получить сумму всех действительных гривневых депозитов, открытых менее 7 месяцев назад на 19-м отделении сроком не менее, чем на 6 месяцев (ну, отчеты разные бывают - даже такое иногда нужно).
Для этого, соответственно, сделаем отдельный столбец, в котором попытаемся вместить все эти условия.

Вот такая несложная формула позволяет совместить все условия в одном столбце.
Еще капельку усложнив формулу, мы сразу сможем получить сумму или 0 в этом же столбце

Обратите внимание на искусственно введенный в формулу знак-разделитель "-". Именно он отделяет числа между собой и не позволяет спутать результаты в формуле. Хотя конкретно эта формула работала бы и без этого знака, но я настоятельно рекомендую использовать его!

Удачи вам!

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

2012-08-24T16:55:00.001+03:00

Привет!

У вас есть данные и из них часто приходится делать самые различные отчеты? Если отчет подразумевает собой простую таблицу, то нет ничего проще - достаточно сделать сводную таблицу на основании имеющихся данных.

Предположим, есть у нас подобная таблица, каждая запись в которой соответствует 1 продаже. Как быстро сформировать из такой таблицы, например, отчет о том, какой сайт и в какой стране получил сколько доходов со статусом "Оплачено"? Как всегда, есть несколько путей решения задачи, но когда количество интересующих нас параметров для отчета ограничено 3-4 (сайт/страна/статус), сводная таблица как нельзя более кстати подойдет для решения.

Итак, приступим у формированию таблицы: перейдем на чистый лист и выберем меню Вставка -> вкладка Таблицы -> Сводная таблица.

Далее перейдем на лист с данными и выделим полностью все столбцы таблицы.

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

Активное поле -> Операция - здесь нам необходимо выбрать результат, который будет выдаваться таблицей. Нас интересует СУММА полученного дохода.

Далее просто выбираем по статусу Оплачено и получаем требуемый отчет. Все, таблица готова и на ее создание ушла лишь минута (если заранее знать что делать).
Выбрав сверху Работа со сводными таблицами, вы можете попробовать преобразить ваш отчет. Также следует отметить, что в сводную таблицу как в поля столбцов и строк, так и в поле страниц можно перетягивать справа не один параметр, как это сделано выше, а больше. Если, например, у сайта есть поддомены или для стран отмечены еще и регионы, или же отчеты нужно не только со статусом оплачено, но и по датам...

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

Удачи!

Работа команды GoTo в Excel

2012-06-05T18:05:00.002+03:00

Приветствую вас!

Вот я и добрался до интереснейшей команды GoTo. Пост будет недлинным, но я попытаюсь подробно объяснить работу и полезность GoTo.
Почему я решил сделать отдельный пост, чтобы подчеркнуть преимущества использования этой команды?
Если вам предстоит писать не линейный макрос, а решить достаточно сложную задачу с помощью макроса, в котором имеются циклы, много условий или есть необходимость вернуться назад, к уже проделанным операциям - GoTo станет незаменимой!

Панель инструментов Формы

2012-03-17T15:35:00.001+02:00

Приветствую вас!

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

Из этой панели мы рассмотрим те элементы формы, к которым мы уже привыкли, а точнее флажки, переключатели, группы элементов, списки, выпадающие списки, счетчики, полосы прокрутки и, конечно же, кнопки! Но все по-порядку...

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

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

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

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

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

Если в форме необходимо выбрать число из заданного интервала значений с определенным шагом между соседними значениями, вы можете использовать счетчик (на скриншоте справа), или же, если таких значений достаточно много, полосу прокрутки, с которой выбрать подходящее возможное значение, скажем, из сотни, займет буквально 3-5 сек. Опять же нужно задать связанную ячейку, находящуюся рядом с этими элементами. Значение ее для данных элементов, в отличии от предыдущих, прятать не нужно - оно должно быть видно. Далее задаем минимальное, максимальное допустимые значения счетчика или полосы прокрутки, шаг его изменения, и все готово.

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

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

Дальше будет интереснее ))

Пример 3: задача про коварного маленького мальчика и 2 числа

2012-02-26T00:38:00.002+02:00

Приветствую!

Один маленький мальчик загадал два различных числа. Оба — строго больше 1 и строго меньше 100, натуральные. Одному мальчику он сказал сумму этих чисел, другому — их произведение. Прошла неделя, и два мальчика встретились. Тот, кто знал произведение, говорит: «Ты знаешь, мне не хватает данных, чтобы определить, что за числа загадал маленький мальчик». — «А я знал, что тебе не хватит данных!» — ответил тот, кто знал сумму. — «Тогда я понял, что за числа он загадал», — сказал тот, кто знал произведение. — «Тогда и я понял...» — сказал тот, кто знал сумму. Что за числа загадал маленький мальчик? Ход решения обосновать.

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

Итак, не будем медлить и перейдем к делу:
1. Мы сразу имеем явное ограничение обоих чисел - они больше 1 и меньше 100, натуральные т.е. 2, 3, 4...99. (Таким образом мы имеем 9604 комбинации)
2. Числа различные, т.е. они не равны между собой. таким образом 98 вариантов из 9604 сразу можно исключить.
И из оставшихся комбинаций половина дублируются, так что рассматриваем только 4753 комбинации )))))))
3. Мальчику, который знал произведение чисел, данных не хватило. А это значит, что комбинации, в которых оба числа простые, мы тоже отметаем, иначе бы мальчик, знавший произведение, легко бы узнал числа. (простые числа: 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97)
4. Мальчик, который знал сумму, знал, что данных второму не хватило. Знать он это мог только в том случае, если сумма, которую знал он, не может быть суммой 2-х простых чисел.

Дальше условия все запутаннее и запутаннее, так как каждое строится на предыдущем по ходу разговора мальчиков! страшно?

5. Мальчик, который знал произведение чисел, по реплике собеседника понял, какие числа были загаданы.значит у него было несколько вариантов, на которые можно было разложить произведение этих чисел. Но только один из них давал такую сумму, которая не могла быть суммой двух простых чисел.
6. Второй мальчик тоже понял, что это были за числа. Он знал сумму этих чисел, и рассмотрел все пары чисел, на которые эта сумма раскладывается. И только одна такая пара давала произведение чисел, которое при разложении давало бы только одну пару чисел, сумма которых не могла быть суммой двух простых чисел!

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

Как вы можете видеть, данный пример действительно вышел несколько объемным. При этом были использованы и условные блоки, и циклы, и массивы, и операторы goto...

Как вы видите, я не шел напрямую, разбирая все произведения на все возможные составляющие пары чисел, да и суммы я многие обошел (разбор на составляющие). Вместо этого я предварительно перебирал все возможные варианты и результаты (суммы или произведения) записывал в отдельные массивы, которые уже при необходимости просто перебирал и сравнивал с интересующими меня значениями. Я думаю, так рациональнее.
Также в отображенном результате сразу видно, что половину вариантов (дублирующую половину) и диагональ, на которой числа равны, я сразу отсек, перебирая второе число не от 2 до 98, а от (i+1).

Результаты, выбранные макросом, как правильные, видны на скриншоте. Их 2, но подходит из них только 1 (13 и 4). Второй результат не подходит потому, что мальчик, знавший произведение этих чисел, мог сразу определить эти числа, хоть одно из них (4) и не является простым. А все потому, что возможны лишь 2 варианта: 2 и 4 или 4 и 2. Таким образом, мы видим, что произведения, которые являются кубом простого числа (это 4 числа, подходящих в условия задачи: 8, 27, 125, 343) следовало сразу исключить. Но раз уж после работы макроса мы легко нашли правильный ответ, мучать далее макрос не считаю целесообразным - свою функцию он выполнил.

input box и message box в макросах Excel

2012-02-18T02:53:00.003+02:00

Привет всем!

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

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

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

Опция vbOKCancel (альтернативой ей является опция vbOKOnly) позволяет управлять выполнением макроса и принимать человеческие решения в процессе его выполнения. В результате x примет значение 1 или 2 соответственно, в зависимости от чего условным блоком мы корректируем выполнение макроса.
И вот так выглядит форма ввода input box так. введенное в форму значение будет присвоено переменной у, как задано макросом.

Вот основное, что я хотел вам рассказать о формах input box и message box, которые, если вы будете писать макросы, несомненно рано или поздно вам понадобятся.

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

Пример 2: максимальное количество повторений значений подряд

2012-02-11T15:12:00.003+02:00

Приветствую вас!

Совсем недавно я столкнулся с задачей следующего характера: нужно было с помощью формул определить какое максимальное количество раз ПОДРЯД встречается определенное значение в столбце.

Ну, к примеру, можно искать сколько раз значение 1 повторяется.
Макросом эта задача решается элементарно:

Кстати, что касается условных операторов, на этом примере видно 2 варианта синтаксиса условного блока: вариант без End If и в одну строку используется, когда в условном блоке находится только 1 команда.

И еще одно, что я настоятельно рекомендовал бы начинающим пользователям макросов, отмечать строки, находящиеся внутри какого-то блока (условного блока или цикла) предварительно отступив некоторое место (например, я использую tab для отступа, как видно на скриншоте. Точно также я делаю в HTML - очень удобно для того, чтобы не забывать ставить закрывающие тэги.

Как решить данную задачу с помощью формул я некоторое время размышлял, пока не написал макрос и не вникнул в то, что я написал:
- переменная k считает текущее количество повторений;
- переменная q выбирает максимальную k.
И так я понял, что решить эту задачу формулами тоже абсолютно просто:
- в столбце B будут располагаться текущие значения переменной k;
- после чего из них мы выберем максимальное значение формулой МАКС.

1. В столбце В в ячейке В1 формула =ЕСЛИ(A1=1;1;0)
2. ячейке В2 формула =ЕСЛИ(A2=1;C1+1;0) и растягивается с В2 до В1000
3. в ячейке С1 формула =МАКС(B:B)

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

Дальше будет...
Буду рад вас видеть вновь на страницах моего блога!

Поиск ошибок в макросах. Пошаговое выполнение макросов Excel

2012-02-04T17:17:00.001+02:00

Приветствую вас!

Сегодня я хочу поговорить об ошибках, а если быть точнее, то об ошибках в макросах. Ошибки - это неотъемлемая составляющая написания макросов (ну, по крайней мере для меня). И я еще помню то время, когда я не умел быстро находить ошибки, а просто всматривался в текст макроса в надежде понять, что же происходит не так.

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

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

Те ошибки, которые так просто не найдутся, можно найти другими путями. Один из них - вывести множество промежуточных результатов либо же запустить пошаговое выполнение макроса.
Зачастую пошаговое выполнение макроса поможет лучше и быстрее найти ошибку, но если вы имеете дело с длиннющим циклом и весьма заметной ошибкой, то имеет смысл воспользоваться именно промежуточным выводом результатов (таблицей, например, где каждая строка будет соответствовать 1 циклу макроса).

Ну а мы вернемся к неверному результату макроса, написанного к посту об условных операторах Excel.

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

Ну, поскольку промежуточные результаты уже были получены нами в формулах, и об ошибках мы кое-что знаем, весь макрос помечать не станем, только его часть.
Помечаем левой кнопкой мыши кликая на том месте, где хотим сделать пометку об остановке макроса.
Запускаем макрос стандартной кнопочной проигрывания (выделена красным).
Промежуточные результаты смотрим, наводя мышкой на переменные. Вот s2 имеет значение 14 и пробел. Почему? А потому, что p2=14, а мы должны были использовать не p2, а q2.
Пробел появляется после использования функции str. Поэтому проще всего убрать все пробелы из результата, добавив соответствующую строку.

В результате получим макрос без ошибок.

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

Условные операторы в макросаx Excel

2012-02-04T16:38:00.003+02:00

Привет всем!

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

Вернемся к уже рассмотренному примеру изменения формата отображения записи цвета RGB.

Поставим цель получить результат в ячейку А6.

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

После написания макроса я получил такой результат, как на скриншоте. И самое время проверить его.

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

После исправления ошибок получим вот такой рабочий макрос.

Также, как и в формуле, в макросе может использоваться оператор Else, которые будет выполняться, если условие НЕ соблюдается. При наличии более 1 строки после If, завершать выполнение условного оператора следует командой End If.

Вся разница между формулой ЕСЛИ в Excel и использованием условного оператора в макросах заключается в том, что формулы можно использовать только для расчетов, а условные операторы для любых доступных действий в Excel - например, открыть другой файл Excel, сохранить файл или запустить другой макрос.

Вот и все, что я хотел сказать об условных операторах.
Если вам было интересно - читайте следующие посты. Мне еще многое хочется вам рассказать.

Условное форматирование

2012-02-01T19:41:00.001+02:00

Привет всем!

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

Для того, чтобы попасть в меню условного форматирования, которое вы видите на скриншоте. Зайдите во вкладку Главная, панель Стили, кнопка Условное форматирование.

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

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

До новых встреч!

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

2012-01-31T22:33:00.003+02:00

Приветствую вас в моем блоге!

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

Итак, автофильтр - маленькая кнопочка, вынесенная на настраиваемую панель инструментов и имеющая большую важность при работе с таблицами в Excel.

Как установить автофильтр? Для установки автофильтра нужно ПРОСТО выделить шапку таблицы (достаточно часто она по совместительству является также первой строкой листа) и нажать на кнопочку. Результат на скриншоте (это Excel 2007)...
Сразу же отмечу, что снять той же кнопочкой его не получится. Для снятия автофильтра нужно зайти во вкладку Главная, панель Редактирование, меню Сортировка и фильтр и нажать на кнопку фильтр (опять же, для Excel 2007). Думаю, и для Excel 2003 вы найдете автофильтр без труда.

Кстати, если вы узнали таблицу - это пример изменения формата записи цвета RGB.

Итак, настраиваемый фильтр. Открыв фильтр, мы увидим следующие опции фильтрации и сортировки: сортировки по возрастанию и убыванию, по цвету (приятное дополнение от Excel 2007), фильтры по цвету, текстовые и по имеющимся в колонке значениям.

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

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

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

Надеюсь, вам было интересно и вы вернетесь, чтобы узнать больше об Excel.

Редактирование макросов Excel во встроенном редакторе

2012-01-29T23:47:00.004+02:00

Привет!

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

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

Начнем с того, что нажмем на запись нового макроса, после нажмем заранее вынесенную на настраиваемую панель кнопку вставить строку и завершим запись макроса.

Войдем в этот макрос и увидим приблизительно следующее содержимое.
Теперь самое время использовать цикл. Я применяю 2 вида цикла, которые могут решить любые мои задачи:
For x=...Next "x"
While x<...Wend
Первый используется когда известно сколько раз нужно выполнить цикл, второй используется, если цикл должен выполняться пока действует некоторое условие (например, пока x< какого-то числа и т.д.).

Нужное количество строк будет задаваться значением ячейки A1. Переменной k присвоим значение этой ячейки. Цикл выполним от 1 до k с шагом 1 (шаг по умолчанию, которого тоже, как правило, достаточно).

Выполнив такой нехитрый макрос, мы достигнем поставленной цели.

Просто, полезно, и когда нужно будет вставить 20 или 40 строк - этот макрос будет существенно экономить мое время.

В следующих постах я покажу как выполнять макрос пошагово для нахождения в нем ошибок, как пользоваться формами в экселе, как пользоваться условными операторами в макросах, а также как общаться с макросов во время его выполнения посредством форм с сообщениями и форм для ввода. Также где-нибудь обязательно напишу об операторе go to , который просто незаменим при выполнении макросов с разветвленной структурой. Так что заходите еще!

Создание макросов в Excel

2012-01-26T23:12:00.001+02:00

Доброго времени суток, уважаемые читатели!

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

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

Появившееся меню, которое, собственно, нас и интересует, выглядит следующим образом.
И если выбрать один из макросов и нажать кнопку Изменить, откроется окно Misrosoft Visual Basic, в котором мы сможем изменить, дописать, переименовать, удалить имеющиеся макросы.

Примерно так будет выглядеть редактор макросов в открывшемся окне Misrosoft Visual Basic. Здесь вы сможете и справку почитать :), и написать нужное содержимое для ваших макросов, и совместить тексты нескольких макросов в один, и поэтапно выполнять ваш макрос, сверяя результат выполнения каждого этапа с вашим файлом Excel, что очень сильно упрощает нахождение ошибок.

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

Заходите еще!

Excel Пример 1: изменение формата записи цвета RGB

2012-01-25T02:05:00.000+02:00

Приветствую вас!

Вот, думаю, и добрались до примеров применения Excel. Для начала рассмотрю очень легкий и простой пример, как и обещал, жизненный.
Суть примера заключается в том, что, как оказалось, пипетка фотошопа определяет цвет в формате:
R: 0...255
G: 0...255
B: 0...255

Для тех, кто не в курсе, RGB - red-green-blue - составляющие любого цвета, при этом в интернете вы можете увидеть цвета, в которых каждый из трех цветов разделен на 256 значений - от 0 до 255.
Для сайта же цвет задается в формате rrggbb, где каждый из цветов обозначается двумя цифрами шестнадцатиричной системы исчисления (0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f).

Собственно. Серым выделены начальные данные - цвет, полученный пипеткой. Зеленым - конечный результат, по каждой составляющей и весь цвет внизу. Белым - промежуточные расчеты.
Столбец D - интенсивность составляющей цвета делим на 16 и округляем в меньшую сторону (получая таким образом больший разряд числа в шестнадцатиричной системе).
Столбец F - остаток от деления на 16 (меньший разряд числа в шестнадцатиричной системе).
Столбцы E и G переводят полученные значения разрядов в шестнадцатиричную систему отсчета с помощью простой, но достаточно длинной формулы, которую вы видите на скриншоте, вверху.

Далее просто соединяем рязряды в числа, соединяем числа воедино, получая готовый цвет.

Макросы на заказ

2012-01-25T01:24:00.000+02:00

Привет, уважаемые читатели и посетители моего блога!

Я хочу предложить вам свои услуги по организации отчетов и написанию нужных макросов в Excel 2003 и 2007. Если вам надоели рутинные отчеты (особенно актуально для ежедневных отчетов, формируемых в одном и том же формате), или если вы хотите организовать новый сложный или объемный тип отчета, если вы хотите максимально упростить обработку данных для этого отчета и предоставить результаты в удобном формате - я готов заняться этим.

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

Все, что от вас требуется - изложить максимально полно задание:

- какие таблицы с начальными данными будут использованы;

- пример подсчета необходимого результата (не в Excel, просто в привычном для вас виде) в случае наличия специфических расчетов;

- какие результаты нужно получить.

Помимо отчетов я могу помочь вам выполнить

- расчетные задачи с помощью Excel (в том числе и расчеты с итерациями с помощью создания макросов Excel);

- построение текстовых блоков при наличии заданного формата (например, текстов писем для рассылки) и многое другое...

Стоимость моей работы договорная. Для связи со мной вы можете использовать e-mail Varvari4ek@yandex.ru

Оплата моих услуг* возможна как на карту банка (в т.ч. Приватбанка), так и в Webmoney wmz, wmu, wmr.

* альтернативными вариантами оплаты могут быть Liberty Reserve или Perfect Money.

Функции в Excel

2012-01-23T23:59:00.000+02:00

Привет вам снова, мои уважаемые читатели.

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

Итак, это функции ВПР, счётесли и суммесли. Разберем эти формулы Excel на простейшем примере: есть таблица из 2 столбцов А и В и 9 записей (строк).

Функция ВПР находит строку с первым подходящим "искомым значением" в первом столбце "таблицы", и возвращает ячейку из этой строки, находящуюся в "столбце" с соответствующим номером. При этом "интервальный просмотр" задает степень соответствия найденного значения искомому. 0 - точное соответствие.

Функция Счётесли из выбранного "диапазона" ячеек находит все ячейки, соответствующие "критерию" и возвращает их количество.

Функция Суммесли из выбранного "диапазона" ячеек находит все строки с ячейками, соответствующими "критерию" и возвращает сумму значений ячеек, находящихся в этих же строках, но из "диапазона суммирования".
В итоге получим такую табличку с результатами:

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

Полезные сочетания клавиш

2012-01-21T02:59:00.000+02:00

Привет!

В этом посте я хочу сказать несколько слов о клавиатуре. Часто мы настолько привыкаем к мышке - этому изобретению ленивых юзеров, что напрочь забываем о клавиатуре. И совершенно напрасно! Пусть мышка и выполняет основную массу операций, но некоторые просты и очень часто повторяющиеся действия в несколько раз быстрее выполнить используя клавиатуру. За счет каждой такой маленькой и незаметной операции ваша работа с Excel существенно ускорится.

И пусть многие из этих клавиш (или их сочетаний) всем известны, но в этом посте я их все же напишу, чтобы обратить ваше внимание, что их применение помогает упростить, ускорить вашу работу.
Итак:
F2 - редактировать содержимое выделенной ячейки
Enter - закончить редактирование содержимого выделенной ячейки и выделить следующую ячейку
F9 - пересчитать книгу (если выставлен пересчет вручную)
Ctrl + X - вырезать
Ctrl + C - копировать
Ctrl + V - вставить
Ctrl + F - поиск
Ctrl + H - поиск с заменой
Ctrl + S - сохранение файла
Ctrl + Z - отменить последнее действие
Ctrl + ("up" или "down" или "right" или "left") - перемещение выделения до ближайшей пустой/заполненной ячейки
Ctrl + Shift + ("up" или "down" или "right" или "left") - то же самое, но с выделением всех промежуточных ячеек
Ctrl + Page up - перейти на предыдущий лист
Ctrl + Page down - перейти на следующий лист
Ctrl + Home - перейти к ячейке А1
Ctrl + End - перейти к концу данных таблицы
F4 - выбрать закрепление строки/столбца при наборе номера ячейки в формуле (добавлено)

Возможно, список будет дополнен позже. Надеюсь, вы узнали что-то новое и полезное для себя!
Буду рад, если вы допишете полезные сочетания клавиш, которые я пропустил :)

Настраивая панель инструментов (часть 2)

2012-01-20T01:46:00.000+02:00

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

- автофильтр. К сожалению, для того, чтобы снять убрать автофильтр отдельной кнопки нет.
и - вставить значения и вставить форматы соответственно (кнопки из меню специальной вставки, к сожалению многих других кнопок из того же меню нет).
и - вставить строку и вставить столбец. Особенно заметно удобство этих кнопок, когда надо вставить 2-5 строк или столбцов.

- объединить ячейки и отменить объединение.

- вставить функцию.

- кнопочки для записи и просмотра макросов. Удобно вместо отображения всей панели макросов

- защищают ячейку, лист, книгу от изменений.

- очистить значения и очистить формат. Часто пользуюсь второй из них, т.к. функцию первой выполняет Del

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

- кнопочки пересчета. Бывает ситуация, когда автоматический пересчет формул не подходит и приходится использовать ручной. Правда, функцию первой кнопки выполняет клавиша F9.

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

- разделить и закрепить области таблицы.

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

Настраиваемая панель инструментов

2012-01-19T02:16:00.000+02:00

Привет всем!

Как я и обещал в предыдущем посте, речь пойдет о настраиваемой панели инструментов - безумно полезной возможности работы с Excel 2003. В Excel 2007 я работал совсем немного, и настраиваемая панель меня просто спасла - без нее я бы потратил нереальное количество времени на поиск любимых кнопочек.

Вот и она. Сразу хочу обратить ваше внимание, что, как и любая другая панель, она может располагаться в любой части экрана, быть закрепленной с краю, или плавающей посредине экрана.
Второй момент - для удобства можно создать несколько таких панелей. Как вы видите, ниже моей панели присутствует еще одна настраиваемая панель, пустая.
Для создания и настройки своей панели зайдите в меню Вид -> Панели инструментов -> Настроить. Выберите вкладку Панели инструментов, нажмите кнопку Создать, выберите Настраиваемая, назовите ее понежнее - и вот она уже плавает посредине экрана.
Теперь просто засуньте ее туда, где она не будет вам мешать и будет легкодоступна для курсора мыши - это тоже очень важно, ведь, предположительно, ее кнопками вы будете пользоваться достаточно часто.

Настройка настраиваемой панели Excel 2003

Настроить панель достаточно просто, но достаточно долго (по крайней мере у меня это занимает минут 15-20).

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

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

Свой подход

2012-01-18T14:11:00.000+02:00

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

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

Я всегда, открывая переустановленный Excel (2003 или 2007) сразу настраиваю свою панель с часто используемыми кнопочками - экономит просто неимоверное количество времени! В следующем посте обязательно покажу подробно настройку собственной панели.

Открытие

2012-01-18T00:16:00.000+02:00

Привет всем!

Сегодня я открываю свой личный блог, который, я надеюсь, поможет вам узнать несколько ближе Excel 2003. Вы его знаете? Тогда, может быть, вы поможете мне познакомить как можно ближе всех с ним? Ведь так часто думаешь, что знаешь, - а на самом деле внутри оказывается целый мир!

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

Могу помочь и вам!