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

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

Использование метода Монте-Карло для расчета риска

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

Не так давно я прочитал замечательную книгу Дугласа Хаббарда Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе. В кратком конспекте книги я обещал, что одному из разделов – Оценка риска: введение в моделирование методом Монте-Карло – я посвящу отдельную заметку. Да всё как-то не складывалось. И вот недавно я стал более внимательно изучать методы управления валютными рисками. В материалах, посвященных этой тематике, часто упоминается моделирование методом Монте-Карло. Так что обещанный материал перед вами.

* * *

Приведу простой пример моделирования методом Монте-Карло для тех, кто никогда не работал с ним ранее, но имеет определенное представление об использовании электронных таблиц Excel.

Предположим, что вы хотите арендовать новый станок. Стоимость годовой аренды станка 400 000 дол., и договор нужно подписать на несколько лет. Поэтому, даже не достигнув точки безубыточности, вы всё равно не сможете сразу вернуть станок. Вы собираетесь подписать договор, думая, что современное оборудование позволит сэкономить на трудозатратах и стоимости сырья и материалов, а также считаете, что материально-техническое обслуживание нового станка обойдется дешевле.

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

Excel. Сумма цифр целого числа

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

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую начать с Введение в формулы массива и Некоторые примеры использования формул массива. Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

В настоящей заметке использованы материалы книги Джона Уокенбаха MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

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

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

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

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

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

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

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

Excel. Некоторые примеры использования формул массива

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

Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

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

Основное достоинство формул массивов состоит в том, что они позволяют выполнять очень широкий круг вычислений, который другими способами выполнить нельзя. К сожалению, формулы массивов – это наиболее сложное и непонятное средство Excel.

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

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