Рубрики
Ноу-хау

Маленькие помощники в работе

Мы часто сталкиваемся с типовыми, казалось бы, задачами, о которых разработчики Microsoft Excel почему-то не подумали. Это приводит к тому, что приходится раз за разом делать какие-то типовые вещи, обрабатывать однотипные данные, где этого можно избежать, потратив определенное время на автоматизацию. Но где бы это время взять! Но мы — не единственные, кто сталкивается с такими проблемами.

Вы наверняка знаете, что Microsoft Excel позволяет устанавливать надстройки, которые могут существенно упростить нам жизнь. Одна из таких настроек была разработана Николаем Павловым — профессиональным тренером и разработчиком проектов автоматизации средствами Microsoft Office. Называется она Plex (вы можете подробнее ознакомиться с ней по ссылке).

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

Надстройка вас не заинтересовала? Посмотрите сайт Планета Excel — очень полезный проект!

Рубрики
Ноу-хау

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

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

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

Я нашел три небольших ошибки:

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

Со второй ошибочкой все было куда интереснее. У моей супруги закралось подозрение, что картинка с логотипом (убрана на этом фото) может быть непомерно большой и сохраненной в формате bmp. Но то что оказалось на самом деле превзошло все ожидания!

На проверку оказалось, что файл с картинкой — это не просто файл с картинкой. Это встроенный в Excel файл Microsoft Word с картинкой.

Ну и третье, что уменьшило файл еще в 10 раз — это простое сохранение в формате xlsx, вместо оригинального устаревшего xls. Теперь он весит 340КБ.

Дорогие друзья! Пожалуйста, вставляя картинки делайте это через функциональность Insert Picture или (если вы копируете картинку из другого документа) не поленитесь нажать Paste Special и выберите подходящий формат — в данном случае, как нельзя лучше подходят форматы Picture (PNG) или Picture (JPEG).

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

Рубрики
Ноу-хау

Горячие клавиши — полезное

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

Вставка удаление ячеек/столбцов/строк

Ctrl + плюс (на цифровой клавиатуре, или Ctrl + Shift + равно) — вставить ячейки.
Ctrl + минус — удалить ячейки. Помните, что вставка и удаление нескольких ячеек потребует от вас выбрать в появившемся окне вариант вставки или удаления. На самом деле мы чаще всего вставляем или удаляем столбец или колонку. Чтобы сделать это, их необходимо сначала выделить, а потом произвести действие.
Ctrl + пробел — выделить столбец (если выделено несколько ячеек единым диапазоном, то при нажатии на эту комбинацию клавиш выделятся все столбцы, в которые входит этот диапазон).
Shift + пробел — выделить строку (то же про диапазон)

Группировка

Shift + Alt + стрелка вправо — добавить уровень группировки для выделенных ячеек (аналогично, чтобы сэкономить время выделите сначала столбец или строку).
Shift + Alt + стрелка влево — разгруппировать выделенные ячейки.

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

Ctrl + 1 — формат выделенного объекта (если объект не выделен, а выделена ячейка, то формат ячейки).
Ctrl + B — сделать шрифт полужирным (b — bold).
Ctrl + I — сделать шрифт курсивным (i — italic).
Ctrl + U — сделать шрифт подчеркнутым (u — underlined).

Работа с книгой

Ctrl + S — сохраниться.
Ctrl + O — открыть книгу.
Ctrl + P — печать.

Ввод данных

Tab — нажимать, когда вы заполняете данные построчно — переходит на следующую незащищенную ячейку (обычно вправо) — если нажать на Enter после нажатия Tab, курсор возвращается на ячейку, с которой начали нажимать Tab только в следующую строчку.
Enter — применить изменение ячейки и перевести курсор на одну ячейку ниже (или как описано в пункте вверху).
Ctrl + Enter — применить изменение ячейки, оставив курсор на той же ячейке.
Shift + Enter — применить изменение ячейки и перевести курсор на одну ячейку вверх.
F2 — войти в форматирование ячейки.
F4 — повторить последнее действие (если вы не находитесь в форматировании ячейки), изменить тип ссылки (если вы находитесь в форматировании ячейки).

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

Рубрики
Ноу-хау

Горячие клавиши — зачем?

Сегодня вдвоем с коллегой делали серьезные расчеты в Excel. Расчеты предполагали большое количество входных данных, часто представленных в безобразном виде — выгрузка из SAP Blueprint в Excel. В очередной раз убедился, что использование мышки — это жуткий пожиратель времени. Грамотное использование горячих клавиш в разы ускоряет процесс обработки данных.

Хотите развить в себе навыки быстрой и эффективной работы в Excel — запретите себе пользоваться мышью там, где чисто теоретически можно без нее обойтись. Поначалу вам может показаться, что вы делаете обычную работу медленнее, но со временем (когда вы перестанете задумываться, какую комбинацию нажать, чтобы эксель сделал ту или иную вещь) вы начнете работать гораздо быстрее. Это как инвестиция. Потратьте сейчас время на свое развитие, чтобы пожинать плоды всю оставшуюся жизнь.

Другой аналогичный пример, который я рекомендую любому офисному работнику — научитесь печатать вслепую, хотя бы по-русски. Это займет у вас пару выходных полностью и одну неделю на работе после них будет вызывать неудобство (будете печатать медленнее, чем обычно). Мне «повезло», у меня было время научиться печатать вслепую, пока я служил в армии. Для обучения печати слепым методом я рекомендую программку Соло на клавиатуре — она действительно помогает и стоит всего 150-300 рублей (не пожалейте их, купите лицензионную версию — эта программа, если вы потратите не нее время, окупится тысячи раз!).

Но вернемся к горячим клавишам в Microsoft Excel. Для того чтобы научиться пользоваться ими я рекомендую несколько простых приемов:

(1) если вы делаете что-то с помощью мыши с ленты, при наведении Excel показывает комбинацию клавиш — обращайте на них внимание, вы скоро их запомните (в Excel 2010 эта функциональность включена по-умолчанию, в 2007 версии ее необходимо включить в настройках).

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

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

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

Рубрики
Ноу-хау

Выпадающий список без лишних кликов

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

Если вы хотите, чтобы Excel предложил вам все доступные варианты, поставьте курсор под последнюю ячейку из списка и нажмите Alt + вниз. Появится список.

О том, как сделать выпадающий список определенных значений с помощью функции Data Validation, смотрите в этой статье статье.

Если у вас остались какие-то вопросы, вы можете задать их в комментариях, написать в наших группах ВКонтакте или на Facebook, или написать мне электронное сообщение на edward@youcanexcel.ru.

Рубрики
Ноу-хау

Немного про избранные ссылки Windows

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

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

Эта функциональность появилась начиная с версии Windows Vista. Раньше приходилось изобретать обходные пути, как избежать долгих лазаний по папкам (я, например, создавал в Моих документах папку с ярлыками на часто используемые папки на сетевом диске — когда мне надо было что-то открыть из них или сохранить в них, я просто переходил в мои документы, заходил в папку с ярлыками и шел в нужное мне место).

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

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

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

Ради чего мы собственно создавали эти ярлыки. Это важно — они появляются не только в папках Windows, они также будут доступны в большинстве Мелкософтовых программ при нажатии на Сохранить, Открыть и прочие кнопки, которые предполагают выбрать файл или папку.

Один раз настроенные ярлыки (5-10 минут вашего времени) сэкономят вам часы!

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

Рубрики
Ноу-хау

Всплывающие подсказки и выпадающие списки

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

Я набросал вариант того, как может выглядеть эта форма (пока без каких бы то ни было подсказок).

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

Но давайте по-порядку.

Пользователю непонятно, как заполнять поле. Нужна подсказка.

Можно использовать примечания, которые будут отражаться, когда человек наводит на ячейку с примечанием мышку, но это не всегда удобно и выглядит довольно топорно. Я предлагаю использовать для этого функциональность Data Validation. На ленте Data нажмите на кнопку Data Validation из блока Data Tools. В появившемся окне перейдите на вкладку Input Message. Заполните поля Title и Input Message.

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

Есть ограничения по длине поля. Необходимо проверить, чтобы она не была превышена.

Опять идем в Data Validation из блока Data Tools на ленте Data. На вкладке Settings в поле Allow выбираем Text Length. В поле Data выбираем Less than or equal to. И в поле Maximum указываем максимальную длину поля (например, 25 символов).

Далее переходим на закладку Error Alert и настраиваем внешний вид и текст сообщения об ошибке, если она была вызвана пользователем (введен текст длиннее 25 символов).

Ошибка выглядит так.

Пользователю необходимо выбрать один из нескольких доступных вариантов. Прочие варианты запрещены.

И вновь нам в помощь всемогущий функционал Data Validation из блока Data Tools на ленте Data. На вкладке Settings в поле Allow выбираем List. Далее в поле Source делаем одну из двух вещей:

(1) Делаем исчерпывающий список вариантов через точку с запятой (возможно, через запятую, если в Windows запятая выбрана как разделитель списка),

(2) Делаем ссылку на диапазон данных, в котором будут храниться варианты. Тут есть маленький секрет. Если вы хотите сделать ссылку на диапазон на другом листе, то вам придется присвоить имя (например, options_list) и сделать ссылку =options_list. Подробнее об именах в этой статье.

Для пользователя выпадающий список будет выглядеть следующим образом.

Можно также настроить сообщение об ошибке на закладке Error Alert.

Пример, с которым мы работали, вы можете найти в приложенном файле.

Если у вас остались какие-то вопросы, вы можете задать их в комментариях внизу статьи (для этого необходимо войти в свой аккаунт на сайте ВКонтакте) или написав мне сообщение на адрес edward@youcanexcel.ru.

Вы также можете присоединиться к нашей группе ВКонтакте или на Facebook, чтобы получать советы и know how по Microsoft Excel регулярно.

Рубрики
Ноу-хау

Контроль версий файлов

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

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

А выход предельно прост. Забудьте слово final. Очень часто случается так, что из самого неожиданного места приходит запрос на изменение. И тогда final становится «почти final» и появляется «совсем final» и такое может повторяться много-много раз.

Вместо слова final запомните слова «v» (для version) или «rev» (для revision). И просто отмечайте каждую новую версию файла новым номером версии. И тогда любой человек сможет с уверенностью сказать, какой файл ему нужен.

Другой вариант, который может быть полезен — после версии файла добавьте дату создания этой версии в каком-нибудь универсальном формате, например, yyyy-mm-dd — такой формат интуитивно понятен, независимо от национальности.

Рубрики
Ноу-хау

Преобразование столбцов в колонки и обратно

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

Есть два способа сделать это — с помощью формул и с помощью функциональности Paste Special (специальная вставка).

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

Итак, Paste Special.

(1) Выделите диапазон данных, который вам необходимо развернуть. Нажмите копировать (Ctrl + C или Ctrl + Insert — старая школа).

(2) Поставьте курсор туда, куда необходимо вставить развернутую таблиц и нажмите на ленте Home в блоке Clipboard на значок треугольника на кнопке Paste, в выпавшем списке выберите Paste Special.

(3) В появившемся диалоговом окне зажгите галочку Transpose и смело жмите Ok.

Развернутая таблица готова.

Рубрики
Ноу-хау

Работа с очень большими файлами

Вы порой работаете с файлами, которые считаются по 40-60 минут? Некоторые из них открываются по 3-5 минут? Работа с ними превращается в пытку. Я хочу предложить вам несколько очевидных вещей, которые помогут вам изрядно улучшить жизнь себе и окружающим.

Пожалуй, самый очевидный совет, который я могу здесь дать — включите ручной режим пересчетов. Делается это следующим образом: на ленте Formulas в блоке Calculations нажмите Calculation Options и выберите в выпадающем меню Manual. При включении этого режима Excel перестает пересчитывать формулы при изменении тех или иных ячеек. Это безумно удобно, если вы работаете с большой таблицей с массой вычислений, особенно если вы работаете с фильтром. Помните, что каждый раз когда вам нужно увидеть результаты, необходимо пересчитать формулы. Есть несколько вариантов, как это сделать:

F9 — пересчитать все ячейки, зависящие от изменившихся со времени последнего пересчета (помните, что изменение настроек фильтра — это изменение всех ячеек, которые находятся в таблице, на которую установлен фильтр).
Shift + F9 — пересчитать все ячейки на выбраном листе (только зависящие от изменившихся со времени последнего пересчета.
Ctrl + Alt + F9 — пересчитать все ячейки всех открытых книг. В силу технических причин Excel иногда не пересчитывает все ячейки (при этом внизу слева останется надпись Calculate) — если вы хотите убедиться, что ВСЕ расчеты выполнены, — нажмите Ctrl + Alt + F9.

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

Третье, если вы используете классические таблицы (в которых расчеты во всех строках одинаковые), сохраните формулы только в верхней строке. Остальные — «захардкопить» (скопировать ячейки и не снимая выделения нажать вставить только значения). При этом файл становится значительно меньше в размере. В то же время, если вам необходимо пересчитать эти строки, вы всегда сможете скопировать их из верхней строки.

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

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

P.S. Еще один трюк с пересчетами. Иногда бывает нужно пересчитать одну-две ячейки, ну или одну колонку. Для того чтобы сделать это можно войти в редактирование ячейки (F2) и нажать на Enter. Чтобы пересчитать диапазон ячеек с формулами (например, колонку) выделите этот диапазон и нажмите Find and Replace (Ctrl + H), в обеих строка введите =. Excel заменит равно на равно в выделенном диапазоне. То есть сделает изменения во всех ячейках с формулами.

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