Перейти к содержимому

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel. Круговая диаграмма с двумя наборами данных

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

В качестве данных для примера я взял население Земли по континентам в 1950 и 2000 гг. (см. лист «Население» Excel-файла; Австралию я убрал так как ее доля ничтожно мала, а диаграмма становится плохо читаемой :)). Сначала создайте базовую круговую диаграмму: выделите диапазон А1:С6, пройдите по меню Вставка → Круговая → Круговая.

Рис. 1. Создаем обычную круговую диаграмму

Подробнее »Excel. Круговая диаграмма с двумя наборами данных

Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Подробнее »Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Excel «тормозит». Что делать? Дубль 2

Около двух лет тому назад мой знакомый прислал мне Excel-файл содержащий порядка 200 строк без формул и связей, и весящий около 28МВ! Для перехода курсора из ячейки в ячейку требовалось несколько секунд. Оказалось, что файл содержал объекты, и мне удалось их удалить с помощью небольшого кода VBA.

У этой истории совсем недавно появилось продолжение. Посетитель блога оставил комментарий к той заметке, указав на иное оригинальное решение. Оно мне так понравилось, что я решил вернуться к этой теме.

Кто не знает, стандартный Excel-файл, начиная с версии 2007, является zip-архивом. Я про это узнал из комментария. Любопытно, что мои знакомые сисадмины этого тоже не знали 🙂 Вот что по этому поводу значится в Википедии:

Чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Подробнее »Excel «тормозит». Что делать? Дубль 2