7. Полезняшки Excel

Excel. Примеры использования функции ДВССЫЛ (INDIRECT)

Рубрика: 7. Полезняшки Excel

Функция ДВССЫЛ (INDIRECT) — одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми. По сути, если в формуле есть раздел ДВССЫЛ со ссылкой на ячейку, эта ссылка обрабатывается как содержимое соответствующей ячейки. [1] Например (рис. 1), в ячейке С4 я ввел формулу =ДВССЫЛ(А4), и Excel возвратил значение, равное 6. Excel возвращает именно это значение, поскольку ссылка на А4 немедленно заменяется текстовой строкой В4. Следовательно, формула обрабатывается как =В4, что дает нам 6. По аналогии, если ввести в ячейке С5 формулу =ДВССЫЛ(А5), Excel вернет значение ячейки В5, то есть 9.

Рис. 1. Простой пример функции ДВССЫЛ

Читать полностью

Excel. Примеры использования функции СМЕЩ (OFFSET)

Рубрика: 7. Полезняшки Excel

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

Формально функция СМЕЩ (OFFSET) применяется для создания ссылки на прямоугольный диапазон, которая представляет собой заданное количество строк и столбцов, отстоящих от ячейки или диапазона ячеек (см. Help MS Excel). Неплохую статью для первого знакомства с функцией СМЕЩ написал Ренат Лотфуллин (рекомендую!)

1. Чтобы создать ссылку на диапазон ячеек, сначала необходимо указать начальную ячейку. Затем надо указать, на расстоянии скольких строк и столбцов от нее начинается прямоугольный диапазон. Например, с помощью функции СМЕЩ можно создать ссылку на диапазон ячеек, включающий две строки и три столбца и начинающийся на два столбцов правее и на одну строку выше текущей ячейки (рис. 1). Хитрость функции СМЕЩ в том, что она возвращает диапазон, поэтому, если просто ввести ее в ячейку, ничего не выйдет – функция СМЕЩ вернет ошибку #ЗНАЧ! Поэтому в примере использована функция СУММ, которая суммирует значения в диапазоне, возвращаемом функцией СМЕЩ. Заметим, что, если функция СМЕЩ возвращает одну ячейку, например, =СМЕЩ(A4;-1;2;1;1), то ее можно использовать напрямую, она вернет значение ячейки С3.

Рис. 1. Пример использования функции СМЕЩ; диапазон суммирования, возвращаемый функцией СМЕЩ подсвечен

Читать полностью

Как обойти ограничение Excel и сделать выпадающий список зависимым

Рубрика: 7. Полезняшки Excel

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Читать полностью

Excel. Проверка данных

Рубрика: 7. Полезняшки Excel

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

Средство проверки данных

Excel позволяет задать определенные правила, по которым будет определяться, какие данные могут содержаться в ячейке. [1] Например, необходимо, чтобы число, содержащееся в ячейке, принадлежало диапазону от 1 до 12. В случае если пользователь введет неправильное значение, программа выведет соответствующее сообщение (рис. 1).

Рис. 1. Вывод сообщения о неправильном вводе данных

Читать полностью

Диаграммы в Excel. Использование полос погрешности

Рубрика: 7. Полезняшки Excel

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

Например (рис. 1) полосы погрешностей могут изображать диапазоны ошибок измерения каждой точки данных. В этом примере полосы погрешностей выражены в процентах: значение плюс-минус 10% от значения. [3]

Рис. 1. График с полосами погрешностей, выраженных в процентах

Читать полностью

Закон Бенфорда или закон первой цифры

Рубрика: 7. Полезняшки Excel

Недавно я прочитал замечательную книгу Леонарда Млодинова (Не)совершенная случайность. Как случай управляет нашей жизнью.
О-о-чень рекомендую! Некоторые фрагменты мне особо понравились, и вот сегодня об одном из них – законе Бенфорда. [1]

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

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

Читать полностью

Excel. Биржевая диаграмма, она же блочная, она же ящичная

Рубрика: 7. Полезняшки Excel

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

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

Рис. 1. Меню выбора биржевой диаграммы

Читать полностью

Сравнение аннуитетных и дифференцированных платежей в погашение ипотечного кредита

Рубрика: 4. Финансы, 7. Полезняшки Excel

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

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

Упомянутое свойство денег имеет два важных следствия:

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

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

Читать полностью

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

Рубрика: 7. Полезняшки Excel

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

  • для точки: Формат точки данных → Заливка → Сплошная заливка
  • для подписи: Шрифт → Цвет текста

Рис. 1. Выделение цветом точки на графике и подписи точки

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

И всё же выделить цветом одну или несколько подписей оси возможно. Не скажу, что это очень просто, но изучение примера позволит вам освоить не только этот, но и некоторые другие весьма полезные приемы работы в Excel [1].

Читать полностью

Excel. Изменение области диаграммы с помощью строки формул

Рубрика: 7. Полезняшки Excel

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

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

Читать полностью