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

Спарклайны или микрографики в ячейках Excel

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

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

Например, на рис. 1 показаны изменения среднемесячной температуры июля в Москве за период наблюдений с 1879 по 2013 г. Чтобы рисунок был более наглядным показаны не абсолютные значения, а колебания относительно среднего значения за весь период наблюдений (18,4⁰С). Красным выделен абсолютный максимум (2010 г., 26,1⁰С) и минимум (1904 г., 14,6⁰С).

Рис. 1. Динамика среднемесячной температуры июля в Москве

Рис. 1. Динамика среднемесячной температуры июля в Москве

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

Алгоритм ссылочного ранжирования PageRank и линейная алгебра

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

Недавно прочитал любопытную книгу Стивен Строгац. Удовольствие от х. Автор рассказывает около 30 занимательных (и поучительных) историй из области математики. В одной из историй говорится о принципах работы PageRank – алгоритма ссылочного ранжирования, впервые использованного в Google. Тема актуальна и довольна проста для понимания. Так что слово Стивену Строгацу…

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

PageRank

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

Пользовательский формат числа в Excel

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

Excel предоставляет достаточно много встроенных числовых форматов, однако возможны ситуации, в которых ни один вам не подойдет. В таком случае попробуйте создать собственный (пользовательский) числовой формат. [1] Для этого, находясь в ячейке, кликните правой кнопкой мыши, и в контекстном меню выберите Формат ячеек. Откроется диалоговое окно Формат ячеек (рис. 1). Перейдите на вкладку Число. Окно Формат ячеек также можно вызвать, нажав сочетание Ctrl+1 или с помощью ленты (меню Главная), щелкнув на стрелке в правом нижнем углу группы Число (рис. 2).

Рис. 1. Окно Формат ячеек

Рис. 1. Окно Формат ячеек

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

Использование надстройки Inquire (запрос) в Excel 2013

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

В Office 2013 Professional Plus впервые используется надстройка для анализа содержимого книг Excel – Inquire (в переводе с английского: осведомляться, спрашивать, искать). [1] Чтобы проверить версию программы, установленной на вашем ПК, пройдите по меню ФайлУчетная запись (рис. 1). Например, на домашнем ПК у меня версия «для дома и учебы» (рис. 1а), а вот на работе – «профессиональный плюс» (рис. 1б).

Рис. 1. Проверка версии MS Office 2013

Рис. 1. Проверка версии MS Office 2013

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

Простые (или обыкновенные) дроби в Excel

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

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

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

Рис. 1. Стандартные форматы обыкновенных дробей в Excel

Рис. 1. Стандартные форматы обыкновенных дробей в Excel

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

Перевод единиц измерения в Excel с помощью функции ПРЕОБР

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

В Excel имеются широкие возможности для перевода одних единиц измерения в другие. Большинство из них реализованы с помощью функции ПРЕОБР. Настоящая заметка основана на возможностях Excel2013. Функция ПРЕОБР довольно значительно модернизировалась MS от версии к версии (см. соответствующую справку по версиям 2010 и 2007; до 2007 для функции ПРЕОБР требовалось использование надстройки Пакет анализа).

Синтаксис функции:

ПРЕОБР(число;исх_ед_изм;кон_ед_изм)

где, число – значение в исходных единицах измерения, которое нужно преобразовать; исх_ед_изм – единицы измерения аргумента число; кон_ед_изм – единицы измерения результата; исходные и конечные единицы измерения должны вводиться в кавычках; набор этих аргументов весьма велик, но ограничен; полный их перечень см. в справке по Excel2013.

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

Глава 13. Динамические диапазоны на основе функций ИНДЕКС и СМЕЩ

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

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                            Оглавление                              Следующая глава

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

На рис. 13.1 и 13.2 приведен пример расширения исходного набора данных. Если вы используете динамический диапазон внутри функции СУММ, диапазон A2:A4 будет автоматически расширяться до А2:А5, если новое число (в ячейке А5) добавляется к исходным данным.

Рис. 13.1. Функция СУММ для диапазона А2,A4

Рис. 13.1. Функция СУММ для диапазона А2:А4

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

Глава 12. Выборка из диапазона дат с помощью критерия в ином формате

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

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                             Оглавление                                Следующая глава

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

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

Рис. 12.1. Подсчет количества дат

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

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

Excel. Создание указателя листов в книге

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

Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение – просто выбрать лист из списка (рис. 1). Для этого кликните правой кнопкой мыши в левом нижнем углу экрана на одной из кнопок прокрутки.

Рис. 1. Выбор листа из списка

Рис. 1. Выбор листа из списка

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

Глава 11. Булева логика: критерии И, ИЛИ

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

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                              Оглавление                                Следующая глава

В предыдущих главах вы уже использовали И критерий при написании формул. Например:

Рис. 10.24 – суммирование с использованием двух критериев
=СУММЕСЛИМН(C8:C19;A8:A19;">="&A2;A8:A19;"<="&B2;B8:B19;C2)

Рис. 10.25 – также суммирование с использованием двух критериев =БДСУММ(A7:C19;C7;A1:C2)

Рис. 4.39 – нахождение минимума на основе двух критериев
=АГРЕГАТ(15;6;$C$3:$C$13/(($A$3:$A$13<>$F$2)*($B$3:$B$13=E5));1)

Рис. 10.26 – суммирование с использованием трех критериев; формула массива, основанная на булевой логике с И критерием: =СУММПРОИЗВ(--(A8:A19>=A2);--(A8:A19<=B2);--(B8:B19=C2);C8:C19)

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

Булеву алгебру предложил в 1854 году Джордж Буль. Логическими (булевыми) называют тип данных, которые имеет только два варианта значений: ИСТИНА и ЛОЖЬ (или 1 и 0).

ИСТИНА = 1, ЛОЖЬ = 0

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