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

Excel. Добавление примечания в формулу с использованием функции Ч

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

Наверное, все знают, как создать примечание к ячейке (рис. 1): вкладка «Рецензирование» → область «Примечания» → «Создать примечание» (или Shift + F2)

Рис. 1. Примечание к ячейке

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

С другой стороны, при создании сложной или редкой формулы через некоторое время вы уже не вспомните нюансы ее написания. Поэтому совсем отказаться от примечаний нельзя. И вот недавно я встретил описание оригинальной функции Excel – Ч.

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

Excel. Добавление фона на часть гистограммы

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

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

Рис 1. Распределение накладных по сроку оплаты; отсрочка составляет 30 дней; вертикальная линия отделяет своевременно оплаченные накладные от оплаченных с задержкой.

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

Рис. 2. Фон создан с помощью цветных столбиков

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

Excel. VBA-код для создания подписей к данным диаграммы

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

Ранее я уже подробно рассказывал о пузырьковых диаграммах. Кратко напомню.

Пузырьковая диаграмма – это разновидность точечной диаграммы, в которой точки данных заменены пузырьками, причем их размер служит дополнительным (третьим) измерением данных. На пузырьковой диаграмме, как и на точечной, нет оси категорий — и горизонтальная, и вертикальная оси являются осями значений. В дополнение к значениям X и значениям Y, наносимым на точечную диаграмму, на пузырьковой диаграмме показаны также значения Z (размер).

Пузырьковые диаграммы позволяют наглядно представить и анализировать взаимозависимости трех переменных.

В качестве примера построения пузырьковой диаграммы я привел распределение населения России по федеральным округам (рис. 1; см. также Excel-файл). Карта России была найдена в Интернете, сохранена как рисунок и импортирована в качестве фона области построения диаграммы. Координаты (X и Y) каждого «пузыря» были подобраны вручную.

Рис. 1. Распределение населения России по федеральным округам

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

Относительные, абсолютные и смешанные ссылки на ячейки в Excel

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

Этот материал предназначен для начинающих и подготовлен с участием Анны Ивановой

Ссылка в Excel – это адрес ячейки или диапазона ячеек.

В Excel есть два вида стиля ссылок:

  • Классический (или А1)
  • Стиль ссылок R1C1; здесь R — row (строка), C — column (столбец).

Включить стиль ссылок R1C1 можно в настройках Сервис —> Параметры Excel —> закладка Формулы —> галочка Стиль ссылок R1C1:

Рис. 1. Настройка стиля ссылок

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

Как с помощью диаграммы приукрасить действительность? или о факторе лжи Эдварда Тафти

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

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

Существуют три вида лжи: ложь, наглая ложь и статистика
Высказывание приписывается премьер-министру
Великобритании Бенджамину Дизраэли

Я помню, как однажды, работая в издательстве, хотел продемонстрировать сокращение числа ошибок в рекламных объявлениях. Исходные данные выглядели неплохо (рис. 1), но самое первое значение (год 2002-й, I кв.) явно не вписывалось в обнаруженную тенденцию… и я его просто… отбросил (рис. 2).

Рис. 1. Доля объявлений, вышедших с ошибками

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

Принцип Эдварда Тафти минимизации количества элементов диаграммы

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

Edward R. Tufte – автор нескольких книг [1], посвященных дизайну, инфографике, визуальному представлению количественных данных. Согласно принципу Тафти, диаграмма должна содержать только самое необходимое. Информацию передают ряды данных, а не линии сетки, подписи данных и т.п. Таким образом, элементы диаграммы, которые не передают мысль автора, нужно безжалостно удалить.

Рассмотрим несколько примеров. Для удобства откройте файл Принцип минимизации количества элементов диаграммы Эдварда Тафти. Пример в формате Excel2007.

На рис. 1 представлена стандартная гистограмма, построенная Excel без посторонней помощи.

Рис. 1. Стандартная гистограмма

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

Excel. Суммирование значений в четных (или кратных) строках (столбцах)

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

Недавно у меня возникла задача просуммировать значения в каждом третьем столбце. Пример, о котором идет речь, мы рассмотрим позже в силу его сложности, а начнем с более простого случая – найдем сумму значений для всех четных / нечетных строк. Исходные данные представлены на рис. 1 (см. также лист «Пример1» Excel-файла).

Рис. 1. Данные о приходе / уходе [1]

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

Excel. Использование раскрывающегося списка для ограничения допустимых записей

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

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

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 1а); см. также Excel-файл, лист «Список».

Рис. 1. Список фамилия: (а) в произвольном порядке; (б) в алфавитном порядке.

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

Проверка формул в Excel, или что означает зеленый треугольник

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

Широко известна функция проверки правописания в Word или Outlook. Гораздо реже пользователи применяют проверку формул в Excel. На эту мысль меня натолкнул комментарий к моей заметке «Excel. Преобразование как бы чисел». В заметке я предлагал несколько относительно несложных методов преобразования «как бы чисел» в настоящие числа. Но автор комментария – Саша – заметил, что существуют стандартные методы проверки в Excel, которые еще проще…

Итак, если у вас есть данные, которые похожи на числа, но числами не являются (воспринимаются Excel’ем как текст), и при этом в левом верхнем углу ячейки появляется зеленый треугольник (рис. 1), выделите область, содержащую такие данные. В нашем примере это А2:А24. Появится желтый ромбик с восклицательным знаком. Щелкните на стрелке вниз, а затем на «Преобразовать в число».

Рис. 1. Преобразовать в число с помощью проверки

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

Личное хранилище информации на базе Excel

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

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

Я использую базы данных на основе Excel вот уже более 10 лет. Приходя на новое место работы, я на основе пустографки создаю новую базу и постепенно заполняю ее файлами, созданными мною в процессе работы. Например, на последнем месте я работаю с августа 2005 года. За это время моя база разрослась до ≈ 8000 документов (рис. 1).

Рис. 1. Справочник в «свернутом» виде

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