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

Как быстро удалить тысячи строк из громадной таблицы по фильтру

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

Файл с исходной таблицей вы можете скачать отсюда, чтобы попробовать сделать тоже самое или провести свой эксперимент.

В качестве эксперимента, статья написана в виде нескольких скриншотов. Надеюсь, вам понравилось. Присылайте обратную связь на электронную почту edward@youcanexcel.ru

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

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

Я уже писал про то, что функция VLOOKUP (ВПР), наверное, самая полезная функция после простых арифметических операций в Excel (про VLOOKUP тут, тут и тут). Но недавно я перестал ей пользоваться и вот, почему.

  1. Это просто неудобно. VLOOKUP (ВПР), наверное, единственная формула, которая ссылается не на конкретную ячейку, в которой заинтересован пользователь, а на массив и выбирает нужную колонку с помощью номера столбца, который передается как отдельный параметр.
  2. Столбец, в котором мы ищем должен быть левее, чем столбец с искомым значением
  3. Копирование формулы в соседний столбец не меняет столбец, по которому происходит поиск. Приходится делать одну из двух вещей: либо менять столбец вручную, либо встраивать в формулу проверку на столбец или ссылку на номер этого столбца.
  4. Вставленный в массиве столбец может «сломать» формулу. Когда вы вставляете внутри массива столбец, формула автоматически не изменяет номер столбца, из которого вы берете значение. А представьте, что вы работаете с чужим файлом и просто не знаете о том, что где-то есть ссылка на этот массив с помощью функции VLOOKUP (ВПР)
  5. Это формула, которая может значительно увеличивать объем расчетов там, где это совершенно не нужно. Представьте, что вы работаете с массивом с 50 колонками. Значение, по которому вы ищете находится в крайней левой колонке, а значение, которое вам нужно – в правой. Изменение любой ячейки из 48 никак не влияющих на значение столбцов заставит вашу формулу пересчитаться. Зачем?

И что теперь?
Действительно. Я не призываю вас вместо VLOOKUP (ВПР) пользоваться Ctrl + C, Alt + Tab, Ctrl + F, Ctrl + V, Enter, Esc, Ctrl + C, Alt + Tab, Tab, Ctrl + V (и это если вы знаете цену клавиатуре и не пользуетесь мышью). Ни в коем случае!

Я предлагаю вам пользоваться сочетанием функций INDEX (ИНДЕКС) + MATCH (ПОИСКПОЗ). Работает это так.

INDEX (массивоткудавзять, [номерстроки], [номерстолбца])

Формула берет значение, которое находится в строке номерстроки и в столбце номерстолбца в массиве массивоткудавзять.

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

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

MATCH (чтоискать, гдеискать, как_искать)

что_искать – ссылка на ячейку или значение, которое будем искать.

где_искать – массив данных, в котором ищем (самая левая колонка в случае с VLOOKUP (ВПР))

как_искать — -1, 0 или 1. Обычно используем 0 – точное совпадение (отлично работает в несортированных массивах). -1 – поиск первого значения, которое меньше искомого (массив данных должен быть отсортирован по ключу по убыванию). 1 – поиск первого значения, которое больше искомого (массив должен быть отсортирован по возрастанию).

Конкретный пример.

Читаем формулу изнутри наружу, как обычно. В этом примере формула определяет номер строки, соответствующей значению из колонки A:A в диапазоне D:D, а потом с помощью функции INDEX выбирает значение с этим номером строки из колонки O:O.

См. также: Почему я пользуюсь ссылками на колонки, а не на конкретные диапазоны

И еще один пример

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

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

Как Excel пересчитывает книгу и почему надо избегать волатильных функций

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

Как Excel пересчитывает книгу

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

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

Для чего нужны эти связи?

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

Значение этой формулы зависит от значений ячеек B2 и C2. Поэтому пересчитывать ее имеет смысл только тогда, когда эти значения меняются. До тех пор, пока из значения неизменны и формула в ячейке D3 не меняется, нет необходимости ее пересчитывать.

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

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

Microsoft приводит следующий список функций (он менялся до 1997 года, но мы проигнорируем старые версии Excel):

NOW (ТДАТА)
TODAY (СЕГОДНЯ)
RAND (СЛЧИС)

RANDBETWEEN (СЛУЧМЕЖДУ) (Miscrosoft ее не упоминает, но она волатильная)
OFFSET (СМЕЩ)
INDIRECT (ДВССЫЛ)
CELL (ЯЧЕЙКА)
INFO (ИНФОРМ)

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

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

Если Вы часто работаете с большими файлами, вам могут быть интересна статья о работе с ними.

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

Окно контрольного значения для отслеживания результатов

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

Для этого в Microsoft Excel есть готовая функциональность, которая называется Окно контрольного значения (Watch window). Чтобы ей воспользоваться необходимо выполнить несколько простых шагов:

  1. На ленте выбрать Формулы => Окно контрольного значения
  1. Выберите ячейки, в которых находятся результаты расчета
  2. Нажмите кнопку Добавить контрольное значение в появившемся окне и подтвердите выбранные ячейки, нажав Ok.

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

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

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

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

Знаете ли вы, что для того, что для возведения в степень числа в 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. Большинство статей посвящено тому, как сделать некоторые вещи проще и эффективнее, не тратя лишних усилий и нервов на переделывание.