» Excel FAQ (часть 5)

sobaka70

=ПРОСМОТР(9E+307;ссылка_на_весь_столбец)

Господа, подскажите, пожалуйста, как можно преобразовать Сгруппированные данные, выгруженные из 1С. Данные вида:



Необходимо, чтобы информация была преобразована построчно с раскрытием уровней, вида:

ozioso
Формулами тут мало что сделать, ла и хлопотно будет и кривовато. Про 1С не знаю ничего :( Как она формирует отступы?... Пробелами? Сколькими и как? Это если разбирать строку и пытаться определить принадлежность поля.

Но, судя по картинке, рабочий вариант - это плясать от Rows(n).OutlineLevel. При условии, что 1С коректно назначает группировку строк, конечно.

asbo
В данном случае уровни отображаются через Indent. Верхний уровень - Indent 0, второй уровень - Indent 1 и т.д.

ozioso
Вопрос к VBA, согласен с asbo

aidomars
Надеюсь, кто-то сможет помочь, потому что я тут бессилен.

ozioso, ответил в смежной теме.

Подскажите хоткеи в MS Excel 2003 для перехода:
1) К последней заполненной ячейке в строке
2) К последней заполненной строке в колонке.

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

oshizelly
Если нет пустых ячеек, то

Цитата:

1) К последней заполненной ячейке в строке

CTRL + стрелка вправо

Цитата:

2) К последней заполненной строке в колонке.

CTRL + стрелка вниз

Заметил такой баг, вот иногда надо вводить числа в формате 5.1 5.2 5.3 - причем изначально выставляешь тип число - все равно преобразуется в формат даты и выводит число, как можно обойти?

PS: Чтобы не хранить как "текст".

InSe0F
Вариант: в XL'e установить разделитель дробной части ".".

Ещё один детский вопрос (RTFM опять не помог). В столбце "Порядковый номер записи" дана нумерация по хронологии с использованием простой формулы =A2+1. При сортировке таблицы по значениям в других столбцах нумерация, естественно, меняется, то есть, запись nr 1 (хронологически самая первая) может стать записью nr 56. Но ведь, как правило, требуется, чтобы при любой сортировке порядковый номер записи не менялся.
Какие есть решения проблемы, кроме наиболее очевидного, то есть, замены формулы на абсолютные значения?
Спасибо!

Да, а как правильно заменять формулы на абсолютные значения? До сих пор я пользовался для этого командой "Специальная Вставка" (Paste Special): копировал колонку в буфер обмена и затем вставлял оттуда обратно уже как значения (values). Но что-то подсказывает мне, что должно быть простая и удобная команда для такой замены.

oshizelly

Цитата:

Какие есть решения проблемы, кроме наиболее очевидного, то есть, замены формулы на абсолютные значения?

Заменить на абсолютные значения :))))
Я давно плюнул, все как-то не интуитивно получается, заморока сплошная.


Цитата:

должно быть простая и удобная команда для такой замены.

Во многих случаях проканывает перетаскивать правой кнопкой - копировать только значения.
Я себе еще и кнопку на панели сделал:

Код:
Sub sb_PasteValues()
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub

asbo

Цитата:

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

Я часто перетаскиваю куски текста правой кнопкой в MS Word. А в Excel так не получается: немедленно после нажатия на правую кнопку открывается контекстное меню, так что уже ничего никуда не перетащить. Возможно, эта фича появилась в новых версиях экселя? У меня старый добрый MS Excel 2003. Или от настроек зависит?

:Добавлено:
Немного на другую тему, но тоже насчет кнопок и хоткеев. Нередко нужно ненадолго включить/выключить отображение формул. Если делать через главное меню (Tools -> Options -> View -> Formulaes), то долго и неудобно.
В мануале сказано, что для этой операции есть стандартный хоткей: Switch between displaying formulas and their values on a worksheet: CTRL + ` (grave accent). Ещё бы знать, где на моей нестандартной клавиатуре этот самый ` (grave accent), пробовал несколько разных вариантов, но не угадал. А нельзя ли поменять хоткей, как в MS Word?
Или только через макрос? Но тогда придется аж целых два макроса писать, один с командой ActiveWindow.DisplayFormulas = False, а второй со значением =True, и к ним два разных хоткея. А хочется, чтобы переключаться туда/сюда нажатием одной и той же комбинации.

oshizelly
Цитата:

ничего никуда не перетащить

Странно... Это еще со времен чуть ли не '97. Хватать правой кнопкой за рамочку выделения надо.


Цитата:

CTRL + ` (grave accent).

Это тильда (~), но без шифта. Ё - если по-русски :) Под искейпом. У меня работает (2003 SP3 Ru). Как поменять - не знаю :(

А если макрос хочется, то два не надо:

With ActiveWindow
.DisplayFormulas = Not .DisplayFormulas
End With

asbo

Цитата:

Это еще со времен чуть ли не '97. Хватать правой кнопкой за рамочку выделения надо.

А-а-а-а! Кто же мог знать! Скрытая недокументированная фича О, сколько нам открытий чудных...

Ну, тогда уж наберусь нахальства вернуться к одной старой теме. Вот, почти год назад задал вопрос, как спрятать в списке макросов дурацкий префикс Personal.XLS!, который затрудняет чтение и навигацию по списку.
Проблема наверняка решается: у нескольких знакомых на компах видел список макросов без этого дурацкого префикса. Значит, я сам что-то у себя накрутил. Но что именно?
Пытался сам искать решение, но не сумел. Так и мучаюсь А решение, подозреваю, совсем простое.

oshizelly
Цитата:

дурацкий префикс Personal.XLS

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

asbo

Цитата:

Дык, а как же различать тогда одинаково названные макросы в разных книгах?

У меня немного другая ситуация: практически все макросы универсальные и потому живут в файле Personal.xls.
Помню, что раньше, до того, как я чего-то напортил, этого префикса не было - и можно было быстро переместиться в списке макросов к нужному пункту, введя первые буквы имени макроса. А теперь этого, естественно, сделать не получается, так как имена всех макросов начинаются одинаково. Вот понять бы, что именно я там накрутил и как это открутить обратно.

Добавлено:

asbo

Цитата:

Я себе еще и кнопку на панели сделал:

Код: Sub sb_PasteValues()
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub

oshizelly

Цитата:

Вот понять бы, что именно я там накрутил и как это открутить обратно.

Думаю, что можно попытаться проверить (как я писал выше - "а как же различать тогда одинаково названные макросы в разных книгах?") на предмет совпадения имен функций и макросов. Месторасположение его проверить - стандартное или нет.


Макрос "написан" совершенно "в лоб". Без проверок и защиты от дурака. Рекордером записал на раз попользоваться, а он и прижился. Выпадает иногда, например, при отсутствии в буфере обмена данных или они там какие-нибудь экзотические.

Добавлено:
Или не совпадают области копирования-вставки. Ну, в общем, все стандартные спотыкачи при копи-пасте.
Да, код полный.

Добавлено:
Ты зачем код под коврик загнал? :))

asbo

Цитата:

Думаю, что можно попытаться проверить (как я писал выше) на предмет совпадения имен функций и макросов. Месторасположение его проверить - стандартное или нет.

Извиняюсь, не понял, эта фраза к какому из моих вопросов относится? И где это "выше"?


Цитата:

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

Так это сначала надо ячейку в буфер вручную скопировать? Сейчас проверил, в этом случае нормально работает.
Вот только я не понял, почему первой строкой нельзя поставить операцию копирования, а надо это непременно делать вручную перед запуском макроса?


Цитата:

Ты зачем код под коврик загнал? )

Случайно не те тэги поставил.

Ненавижу программирование... Обвязка занимает в тысячи раз больше времени и места в коде.

oshizelly, вот окультуренная версия:

Код:
Sub sb_PasteValues()
On Error GoTo Er
Selection.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Exit Sub
Er:
Dim lErrNo&, sErrDsc$
Dim sMsgTxt, lMsgRsp&, lMsgBut&
lErrNo = Err.Number
sErrDsc = Err.Description
sMsgTxt = lErrNo & " - " & sErrDsc & vbLf & vbLf
Select Case lErrNo
Case 1004
lMsgBut = vbInformation
sMsgTxt = sMsgTxt & _
"Please check the following:" & vbLf & _
"- clipboard is not empty or " & vbLf & _
"- copy and paste areas are identical or" & vbLf & _
"- any some else" & _
vbLf
Case Else
lMsgBut = vbExclamation
sMsgTxt = sMsgTxt & "Unknown error. Send a letter to BG." & vbLf
End Select
lMsgBut = lMsgBut + vbOKOnly
lMsgRsp = MsgBox(sMsgTxt, lMsgBut)
End Sub

asbo

Цитата:

Цитата: Вот только я не понял, почему первой строкой нельзя поставить операцию копирования, а надо это непременно делать вручную перед запуском макроса?

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

oshizelly

Цитата:

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

Ну, хоть и не для частного, более общего, но примерно так и есть :) Ведь руками мы что делаем? Выделяем, потом копируем. Если нада - перемещаемся, если нет, - то выделение таким и остается. Затем ищем спецвставку, настраиваем ее и вставляем. Ну, а в моем случае надо нажать только кнопочку.

Впрочем ее легко заточить и сугубо под твою задачу - она будет копировать и сразу же вставлять. Надо Selection.PasteSpecial _ заменить на:

With Selection
.Copy
.PasteSpecial _
(по тексту)
End With

Цитата:

обсуждение ... началось с : как сделать, чтобы при сортировке по другим стобцам не сбивались порядковые номера записей. ... Спасибо!

Я и не заметил, как брюки превратились в элегантные шорты :) Не за что :)

asbo

Цитата:

Ненавижу программирование... Обвязка занимает в тысячи раз больше времени и места в коде... вот окультуренная версия:

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

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


Цитата:

... даже для просто решаемой задачи можно при достаточном упорстве и изобретательстве отыскать более сложное решение?

Снкс. Я оценил иронию и сарказм.

В данном случае этот обработчик реализует лишь так называемую защиту от дурака (foolproof), который пытается что-то куда-то вставить, не озаботившись предварительным копированием этого "что-то" в буфер обмена. Этот механизм фулпруф мягко намекает юзеру, что программа из одной строки работает как надо, разработчик знает об этой ошибке, и эта ошибка - не в коде программы, и а в генетическом коде юзера :)

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

Ну а документирование всего этого добра заняло уйму времени и развернулось в обсуждение на двух страницах :) Вот поэтому я и ненавижу программирование.

Но, вся эта лирика для смежной темы. Сорри за оффтоп.

Имеется таблица из ячеек, допустим, A1, B1, C1, A2, B2, C2, причем B1, C1 и B2 обведены границами (рамкой).
Понятно, что правая граница ячейки B1 - это одновременно и левая граница ячейки C1, а нижняя граница ячейки B1 - это и верхняя граница ячейки B2. Поэтому если мы переместим или уберем рамку ячейки B1, то это уничтожит ее общие границы со смежными ячейками C1 и B2.
Своя логика тут есть, но очень уж неудобно каждый восстанавливать вручную границы ячеек B2 и C1 (на самом деле этих ячеек в моей таблице гораздо больше, чем в этом примере).
Эта проблема никак не решается?

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

Можно ли как-то отсортировать таблицу с учетом диапазонов значений? Например, есть список гаек, столбцы диаметр, цена и пр. данные. Надо отсортировать список по столбцу "Цена", но с разбивкой по нескольким секциям, поскольку гайки в рамках диапазона 10-15 mm и 16-20 mm взаимозаменяемы.

При простой сортировке всей таблицы по столбцу "Цена" результат будет выглядеть так:

Название гайки Диаметр Цена
Латунная 20 mm 100 руб
Золотая 10 mm 150 руб
Чугунная 16 mm 200 руб
Ртутная 12 mm 300 руб
Деревянная 9 mm 350 руб
Картонная 14 mm 700 руб

А надо, чтобы было так:

Название гайки Диаметр Цена
Деревянная 9 mm 350 руб
Золотая 10 mm 150 руб
Ртутная 12 mm 300 руб
Картонная 14 mm 700 руб
Латунная 20 mm 100 руб
Чугунная 16 mm 200 руб

Спасибо!
(Понимаю, что снова примитивный вопрос, даже уже неудобно, но опять-таки не вычитал в справке ничего умного.)

oshizelly, создать третий столбец - группа.

asbo

Цитата:

oshizelly, создать третий столбец - группа.

Спасибо, но вообще-то до чего-то в таком роде я и сам уже почти додумался. Однако такое решение требует дополнительных усилий от пользователя.
Скажем, надо будет отсортировать всю таблицу по 2-му столбцу (диаметр) и затем во всех строках со значением диаметра гайки от 10 до 15 mm прописать туда номер группы. Потом повторить то же самое для остальных диапазонов диаметра.
Либо можно вообще ничего не прописывать, а просто произвести для каждой группы отдельно сортировку выделенных строк по 3-му столбцу (цене).

Я-то наивно считал, что MS Excel сам может все это сделать автоматически.