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

Как возвести число в степень и извлечь корень

Знаете ли вы, что для того, что для возведения в степень числа в Excel есть специальный символ ^ (на шестерке в английской раскладке). Примеры применения:
=A2^3 — Возвести A2 в 3 степень
=A2^A3 — Возвести A2 в степень, указанную в ячейке A3

Чтобы извлечь корень любой степени делаем следующее «^(1/n)», где n — нужная степень числа. Например,
=A2^(1/3) — взять корень третьей степени из A2
=A2^(1/A3) — взять корень степени A3 из A2

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

Выравнивание по центру выделения

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

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

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

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

На картинке ниже заголовок таблицы объединен в одну ячейку при написании формулы, я кликнул на колонку B:B, а программа автоматически написала A:D.

Поэтому я стараюсь избегать объединения ячеек.

Кроме того, часто возникают ошибки при копировании диапазонов ячеек с объединением. Наверняка вы не раз видели такую ошибку.

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

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

У этой функции есть недостаток — невозможно в этом диапазоне сделать перенос. Если вам требуется длинный текст с переносом и не хочется делать объединение, возможно, вам поможет вставка текстового поля. Чтобы оно было привязано к размерам ячейки, во время создания или изменения размеров поля удерживайте кнопку Alt. Теперь, если вы будете менять ширину колонок или высоту строк, текстовое поле также будет менять размер.

Но и у этого способа есть недостатки, на экране тестовое поле, в котором много текста может отображаться иначе, чем при печати.

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

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

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

Поиск по двум критериям без макросов

Все мы любим функцию VLOOKUP (ВПР) и дорожим ей. Но в то же время бесимся, когда она не работает или когда ее не достаточно, чтобы решить нашу проблему. Сталкивались с тем, что нужно было организовать поиск сразу по нескольким параметрам и не смогли справиться с этой проблемой? Читайте данную статью.

Если вам не очень хорошо знакома функция VLOOKUP (ВПР) или она часто выдает ошибки, почитайте эту статью.

Если же данная функция вам не в новинку, давайте разберемся с тем, как искать по двум параметрам. Однако, перед тем как перейти непосредственно к вопросу, я хочу порекомендовать вам одну потрясающую книгу по Microsoft Excel — Формулы в Microsoft Excel 2010 Джона Уокенбаха. Книга описывает, как эффективно использовать различные функции и как эффективно сочетать их в формулах. В ней также описаны специальные формулы, такие как финансовые и формулы массива. Книга учит эффективно использовать сводные таблицы и диаграммы. Она определенно стоит того, чтобы иметь ее в своей библиотеке.

Итак, зачем использовать функция VLOOKUP (ВПР)

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

Другой пример. У вас есть множество заказов на покупку (Purchase order), каждый из которых может содержать несколько услуг. Каждая из услуг нумеруется как отдельная линия основного заказа на покупку (Purchase order item). Необходимо «подтянуть» название линейки заказа на покупку из другой таблицы по этим двум номерам.

Как сделать

Основная идея использования функции VLOOKUP (ВПР) для поиска по двум критериям заключается в том, чтобы создать уникальный код, который будет

(1) включать в себя оба критерия,
(2) который будет уникальным для любых двух уникальных критериев (не будет повторяться для двух разных наборов критериев), и
(3) который будет находиться слева от базы, в которой мы будем искать.

Самый простой способ добиться этого — просто объединить два критерия (это можно сделать с помощью оператора & или функции CONCATENATE (СЦЕПИТЬ). Если оба критерия числовые и могут содержать разное количество символов длины, то лучше поставить между ними какой-нибудь символ, который точно не будет встречаться в коде, например, -, / или #.

Далее делайте поиск по этому совмещенному коду для поиска. Все просто — давайте разберем на примере.

Пример

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

Добавим в самом начале базы (обязательно перед искомым параметром — текущей ценой) параметр, по которому мы будем производить поиск. Для удобства просто объединим две колонки: номенклатурный номер и складом. В результате этот параметр будет иметь вид 24141982СК1. Выглядеть это будет следующим образом.

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

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

Обратите внимание, что если в базе данных, по которой вы производите поиск есть два одинаковых кода с разными значениями цены, то функция всегда будет выбирать первое попавшееся значение. Если совпадения нет вообще, функция вернет ошибку #N/A (#Н/Д).

Если вам необходимо просуммировать все совпадения, воспользуйтесь функцией SUMIFS (СУММЕСЛИМН). О том, как ей пользоваться, читайте здесь.

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

Автофильтр — за и против. Осторожно, возможны ошибки!

Каждый пользователь, который хотя бы время от времени открывает MS Excel, в определенной степени владеет фильтром, но многие ли знают его ограничения? В данной статье я хотел бы рассказать об основных за и против автофильтра.

Начнем с ликбеза, если вы никогда не пользовались автофильтром.

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

Выделите первую строку (если нет пропусков строк) или всю таблицу (если могу встречаться пустые строки) и нажмите на кнопку Фильтр из блока Сортировка и фильтр на ленте Данные. В первой строке в углу каждой ячейки появятся треугольники, при нажатии на которые вы можете фильтровать строки по значениям и с 2007 версии экселя по цветам, что бывает очень удобно, особенно когда работаете с чужими файлами, или если отмечали «сомнительные» ячейки цветом.

Безумно удобная функция! Особенно радует ее дружелюбность по отношению к пользователю.

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

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

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

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

В целом автофильтр — это очень полезный (хоть и не без ограничений механизм). Но обычно им не следует пользоваться более 3-5 раз подряд. Если вы меняете один и тот же критерий несколько раз подряд, возможно вы сравниваете показатели при разных значениях автофильтра. Попробуйте присмотреться к сводным таблицам и функциям SUMIFS (СУММЕСЛИМН) и COUNTIFS (СЧЕТЕСЛИМН).

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

Выделить все ячейки с формулами в диапазоне

Представьте себе, вы работаете с огромной таблицей, половина данных в которой рассчитывается по сложной формуле. Представьте себе, что вы работаете с ней не один (одна). Некто Доктор Зло, второй пользователь таблицы, чтобы «не заморачиваться» заменил несколько формул в этой таблице на число вбитое от руки. Как его найти? Пролистывать всю таблицу — обычно не вариант: высока вероятность, что вы пропустите ячейку, или строк может быть и 200 тысяч.

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

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

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

Второй (самый быстрый). Выделите диапазон, в котором надо искать. На ленте Home нажмите на Find and select и в выпадающем списке выберите Go to special… (или нажмите Ctrl+G и в появившемся окне выберите Special). Появится окно.

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

В моем примере следующие ячейки оказались без формул.

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

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

Интересный сайт

Я хотел бы поделиться с вами молодым сайтом, который я недавно нашел в интернете. На его страницах вы сможете найти интересные идеи о том, как пользоваться Microsoft Office. Большинство статей посвящено тому, как сделать некоторые вещи проще и эффективнее, не тратя лишних усилий и нервов на переделывание.

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

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

Мы часто сталкиваемся с типовыми, казалось бы, задачами, о которых разработчики 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) ну и конечно, преобязательно прочитайте статьи на этом сайте про горячие клавиши: статья про навигацию с их помощью и десяток полезных комбинаций клавиш, а также о том, как сэкономить гору времени, если вы работаете с большим количеством разных папок, например, в сетевых папках.

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