» Excel FAQ (часть 5)

kimtan
+1
Я уже высказывал мысль, что формулы хороши на стадии моделирования сложной задачи. А серьезные расчеты, с плавающей точкой, а тем более с обилием ф-ций по подмножеству... - ну нафиг, в VBA.

grbdv
Так вот и у меня формулы примерно для этого служат - подготовка "сырого" материала для передачи в Statistica. Но вот и формулы тормозить начали.

Что бы такого почитать по формулам массива, чтобы уже всерьез с ними познакомиться - и чтобы за полчаса все уже было ясно?

ghosty
Цитата:

... подготовка "сырого" материала для передачи в Statistica. Но вот и формулы тормозить начали.

Повторюсь, но на определенном этапе надо отказываться от формул. Большинство приходит к этой мысли именно в твоем случае - когда они начинают тормозить. Я же думаю, что отказываться надо когда накладные расходы на их (формул) обслуживание (контроль, выявление ошибок, резервные копии etc.) резко увеличиваются.

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

А касательно формул массивов... Мне, при нужде (при работе с чужим материалом), хэлпа хватает за глаза. А я стараюсь до этого не доводить :) Все - в VBA.

grbdv

Цитата:

Все - в VBA.

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

ghosty
Цитата:

Я дружу с VBA. Но применяю его уже совсем в замороченных случаях. ...

Так и карты в руки! Чего ж тянуть-то до самой заморочки :)

Цитата:

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

У меня примерно такая же картина. Мне легче и лучше, когда логика алгоритма явно видна в коде. А на этом невнятном суржике (формулы), когда половину подспудно надо еще в бошке иметь.... Нафиг нужно?

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

Имо, как только заходит речь о "быстродействии" формул, то самое время портировать это дело под VBA.

Цитата:

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

Это другая крайность :) Опять растут затраты на написание нового кода, поиск существующего (и его упорядочивание) и его адаптацию. Где-то же должна быть золотая середина :)

Прошу прощения, если это уже гдето было. Я не нашёл.
Помогите с созданием протокола соревнований. Каким образом можно складывать и вычитать ячейки с форматом (Минуты:секунды:сотые)?

Rjevsky
Цитата:

Каким образом можно складывать и вычитать ячейки с форматом (Минуты:секунды:сотые)?

Стандартным. Проблемы возникнут при переходе за сутки и в отрицательных величинах. Если нужны сотые (тысячные) то форматировать, как "hh:mm:ss.000". Эксель это позволяет, и считает корректно.

Для общего понимания нюансов и подводных камней необходимо похэлпить и/или погуглить "Представление дат и времени в Excel". Складывать и вычитать - как и обычные величины.

Да, разобрался. За сутки время не вылезет. А вот как выбрать среди, скажем, 20 таких ячеек 5 с максимальным результатом?

Rjevsky
функция НАИБОЛЬШИЙ

Цитата:

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

С полной ответственностью заявляю, что еще не встречал рукописную VBA функцию (замену простейших функций ВПР(), ГПР(), ПОИСКПОЗ(), ПРОСМОТР() и т.п.), которая бы работала быстрее встроенных.
С формулами массивов не все так просто: бывают очень быстрые, а бывают невыносимо тормозные - все зависит от конкретной задачи, условий и количества обрабатываемых данных.
Для своих сложных задач использую как формулы так и рукописные функции, выклянченые у макрописцев, главный критерий - быстродействие.
А вот поизвращаться люблю с формулами - хобби такое

kimtan
В обратном порядке отвечу, сорри...

Цитата:

А вот поизвращаться люблю с формулами - хобби такое

С уважением отношусь к такому хобби. Не раз был впечатлен. Без иронии. Но...

Цитата:

... главный критерий - быстродействие.

Думаю, добавлено исключительно для красного словца :) Если под "быстродействие" имеется ввиду "отсутствие явных для тормозов", то, может, так и следовало бы написать?

И еще. "главный критерий" - тоже для красного словца? Кому нужна летающая задача, но не масштабируемая и требующая к себе отношения более нежного чем то, с которое демонстрировал друг моего детства Костя Остенбакен к подруге моего же детства, польской красавице Инге Зайонц.

Я неоднократно говорил - главный минус сложных (изящных, авторских) формул - их хрупкость и немасштабируемость. Их нельзя отдать в руки простого юзера... Да и сам, подчас, можешь нагородить такого ... (см. сороконожку :)

Последнее.
Цитата:

еще не встречал рукописную VBA функцию (замену простейших функций ВПР(), ГПР(), ПОИСКПОЗ(), ПРОСМОТР() и т.п.), которая бы работала быстрее встроенных.

Тут, вообще, попахивает провокацией :) Ну как же можно всерьез искать и всерьез обсуждать более совершенную замену "простейшему"? :))

Другое дело, что - зачем эти "простейшие"? Не поверите, джентльмены, но ни разу не приходилось мне их использовать :)

М.б. в задачах, сходных с примерами из хэлпа, они и эффективны... Фантастически, несравненно "быстродействены". Но... Чуть задача выходит за рамки хэлпа и - см. выше.

Имо, быстродействие тут вообще ни при чем, а в качестве аргументации - ваащениачом.

Тут важно не пропустить для себя(!) момент, когда проект пора переводить под VBA, оставаясь в рамках Экселя, а когда (как базу данных) под Access.

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

kuterma2
Индизайн... такие выражения... в приличном обществе... Нет на вас модераторов :)

Я не знаю, в каком виде он импортирует Эксель, но начал бы с того, что разместил бы всю инфу - "ячейки - в каждом xls-файле они находятся в одном и том-же месте" - упорядоченно в одной книге и на одном листе в некоем логическом порядке. Желательно с явными разделителями. Существенная часть работы уже будет сделана и придется дальше ворочать единственный объект, а не сколько-то там.

grbdv слово "индизайн" тут не важно в общем, прошу пардона). Отдаю себе отчёт в том, что надо готовить файл в экселе. Ваш метод возьму на заметку. Ещё на ум пришло вот что - можно-ли в экселе в одном файле выделить только нужные ячейки,скопировать их куда мне надо, затем открыть след. Файл и сделать чтото, чтобы автоматом выделились аналогичные (те-же самые) ячейки?

kuterma2
Цитата:

можно-ли в экселе в одном файле выделить только нужные ячейки,скопировать их куда мне надо, затем открыть след. Файл и сделать чтото, чтобы автоматом выделились аналогичные (те-же самые) ячейки?

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

Как я понял, обмен с Индизайном (прости Господи :) ведется через буфер обмена. Если это единственный путь взаимодествия с Адобом, то это дело можно автоматизировать через VBA - в цикле перебор книг по списку, открытие каждой, выделение, копирование в буфер, ожидание реакции пользователя, закрытие книги. Но это вопрос для смежной темы.

grbdv понял. В общем лёгких путей нет - буду делать по твоему рецепту - если разберусь с "сослаться формулами на исходные диапазоны"

kuterma2
Было бы еще неплохо заранее упорядочить имена исходных файлов (книг) по маске [общее_имя]_[индекс_с_лидирующими_нулями]. Упростится создание формул или обращение из VBA.

Ну и, обязательно, посмотреть в сторону автоматизации, предоставляемой самим Адобом - вдруг там есть подключаемые COM-библиотеки... Тогда задача, с одной стороны - упоростится, с другой - усложнится :), а с третьей - решение будет изящным, масштабируемым и переносимым.

Добавлено:
А если это разовый случай - то идти по второму, ручному варианту - не на одном листе, а на нескольких в одной книге.

Добавлено:

Цитата:

если разберусь с "сослаться формулами на исходные диапазоны"

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

grbdv

Вот теперь всё становится на свои места. Спасибо, друг. Думаю справлюсь.

Да - работа разовая - так что думаю пойду по второму варианту

Добрый день. В екселе при прокрутке колесика мыши увеличивается/уменьшается экран, а при нажатом Сtrl идет прокрутка, не подскажите в чем проблема может быть и как это поправить?

denisAzef
Снять галку в пункте "Панорамирование с помощью IntelliMouse"
Для Excel 2003: Меню Сервис >Параметры>Общие
Для Excel 2007/2010: Параметры>Дополнительно

Подскажите, пожалуйста, есть ли какая родная команда для перестановки в столбиков в строки и наоборот (вообщем перевернуть таблицу) или может макрос?

anynamer
Почитайте в Cправке Excel или поищите в интернете по теме "excel транспонирование" и/или функции ТРАНСП

anynamer
Как написано выше, а если формулы не самоцель, то ПКМ - Специальная Вставка. Галка транспонировать.
В VBA есть соответствующий аргумент Transpose метода PasteSpecial.

VictorKos, grbdv Спасибо, поэкспериментирую

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

Изначально формат ячеек текстовый.
Делаю формат ячеек "Время", но ничего не меняется. Функция СУММ не подсчитывает общее время.

Но стоит выделить ячейку, перейти в "Строку формул" и ничего не меняя нажать Enter, то формат к ячейкам применяется и Excel сам дописывает недостающие нули.

Вот скрин, где красным выделены исходные данные, а зелёным - данные где вручную выделялась "Строка формул" отдельно для каждой ячейки:


Можно ли сделать так, чтобы Excel автоматически правильно применял формат ячейки?

Вот на всякий случай пример файла, представленного на скрине.

Nimbussr
Чтобы преобразовать текстовые данные в формат "время" попробуйте ввести в какую нибудь пустую ячейку число 1, скопируйте эту ячейку в буфер обмена, затем выделите диапазон с текстовыми данными, нажмите правую кнопку мыши и выберите в контекстном меню "Специальная вставка". В появившемся окне отметьте пункт Операция>умножить (или разделить, неважно) и нажмите ОК. Если данные преобразовались в десятичные числа, примените к диапазону формат "время".
Можно вместо 1 использовать 0, тогда в окне спецвставки отметить пункт "сложить" или "вычесть".

Пожалуй, быстрее будет в пустом соседнем столбце ввести формулу =D2*1, и растянуть её вниз до конца диапазона, при необходимости изменить формат на "время".

Nimbussr
Есть еще формула =ВРЕМЗНАЧ(D2)
Растянуть на весь диапазон. Убедиться, что в исходном текстовом представлении представлены все три составляющие - час, мин, сек. Иначе вариант мин:сек будет интерпретирован, как час:мин.

VictorKos, grbdv
Спасибо вам большое за помощь!
Попробовал оба варианта всё работает.

VictorKos спасибо большое

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

http://imageshack.us/photo/my-images/204/55325906.png/