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

Как обойти ограничение 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

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

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

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

Excel. Перемещение формул без изменения относительных ссылок

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

На днях дочь обратилась с проблемой. Она построила сложную таблицу в Excel с большим числом формул, основанных на относительных ссылках, и возникла потребность скопировать эти формулы в новую область листа с сохранением ссылок на те же ячейки, что и исходные формулы (подробнее о типе ссылок см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel). «Зайти» во все ячейки с формулами и изменить ссылки на абсолютные было затруднительно, так как таких ячеек было больше ста…

К сожалению, стандартные средства Excel не позволяют выполнить подобное копирование. Что вообще-то говоря, удивительно! Попробуйте, например, перенести формулу =В1+С1, хранящуюся в ячейке D1, в ячейку D4 (рис. 1). Если выполнить копирование с помощью специальной вставки и опции вставить формулы, в ячейке D4 обнаружите формулу =В4+С4.

Рис. 1. Специальная вставка; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

Excel. Диаграмма, изменяющаяся при добавлении данных

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

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

В качестве пример возьмем курс доллара (рис. 1). Для начала создадим обычную диаграмму (тип «График с маркерами»).

Рис. 1. График с маркерами

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