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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Число вхождений подстроки (символа) в текст

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

Недавно коллега спросил, как подсчитать число вхождений подстроки (определенного символа) в текст, размещенный в одной ячейке? Я давно знал, что в Excel нет такой функции. И вот вчера написал небольшую пользовательскую функцию КолСимв. [1] Вот ее код:

Function КолСимв(строка As String, символ As String) As Integer

Application.Volatile True

Dim TestPos As Integer

КолСимв = 0

TestPos = 1

Do While InStr(TestPos, строка, символ) > 0

КолСимв = КолСимв + 1

TestPos = InStr(TestPos, строка, символ) + Len(символ)

Loop

End Function

Мастер функции КолСимв предлагает выбрать, «строку» – в чем ищем, и «символ» – что ищем (рис. 1). При этом «символ» может содержать и несколько символов (подстроку).

Рис. 1. Мастер функции КолСимв, подсчитывающей число вхождений символа (подстроки) в текст

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

Excel для бухгалтера: исправление ошибки округления

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

Бухгалтеры (и не только) знают одну «нехорошую» особенность Excel`я – «неумение» правильно суммировать. 🙂 Иногда это приводит к казусам в бухгалтерских документах, сформированных в Excel (рис. 1)

Рис. 1. Фрагмент счет-фактуры с «неверным» суммированием

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

Excel. Подсчет числа ячеек, содержащих символ (букву)

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

Недавно, в комментариях к заметке Excel. Суммирование по ячейкам, выделенным цветом меня спросили, как подсчитать число ячеек, содержащих определенный символ / букву. У меня получилось решить задачу без использования кода VBA на основе формулы массива. Если вы не использовали ранее такие формулы, рекомендую начать с заметки Excel. Введение в формулы массива.

Шаг 1. Определим, содержится ли искомый символ в ячейке (рис. 1).

Рис. 1. Содержится ли искомый символ в выбранной ячейке

НАЙТИ($C$1;A1) – ищет символ, хранящийся в $C$1, в строке А1; если находит, возвращает позицию этого символа в строке; если не находит, то возвращает ошибку #ЗНАЧ!

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

Анализ инвестиционного проекта (приобретение отеля) с помощью Crystal Ball в Excel

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

Рассмотрим инвестиционный проект, связанный с приобретением апартаментов (небольшого отеля), и проанализируем экономические параметры проекта с помощью программы Crystal Ball [1].

Вы – потенциальный покупатель отеля. Прежде чем принять решение о приобретении отеля, вы выяснили следующее. В отеле 40 апартаментов, каждый из которых сдают по цене $ 500 в месяц. Операционные расходы по всему отелю колеблются вокруг суммы  $ 15 000 в месяц. Каждый месяц с равной вероятностью сдаются от 30 до 40 апартаментов. Расчет прибыли для средних доходов и расходов можно выполнить в Excel с помощью простейшей формулы (рис. 1):

(1) Прибыль = Число сданных в аренду апартаментов * Арендная плата – Операционные расходы

Рис. 1. Расчет прибыли для средних доходов и расходов

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

Моделирование методом Монте-Карло в Crystal Ball для Excel

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

Ранее я рассмотрел пример использования метода Монте-Карло для расчета риска с применением стандартных средств Excel. К сожалению, Excel не очень подходит для решения такого рода задач, так как является детерминистской программой. В каждой отдельной ячейке может располагаться лишь конкретное значение, и для моделирования методом Монте-Карло требуется создать множество строк (сценариев), используя генератор случайных чисел (например, функцию СЛЧИС).

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