» Excel FAQ (часть 2)

ghosty
Цитата:

т.е. диапазоны A:A и B:B. Но приводимые вами формулы наотрез отказываются понимать такое написание

Действительно, не работает ;-((((
ну, как вариант обхода - почти весь столбец ;-)) :

Код:
{=СУММ(ЕСЛИ((A1:A65535="A")*(B1:B65535="B");1;0))}

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

AuthorR

Ребяты а как данные (числа) из столбца поместить в одну ячейку так, чтобы они распологались в строчку с пробелами?

Вопрос
Имеется рабочая книга со множеством листов на всех листах есть данные и диаграмма.
Как сделать так чтобы заголовок диаграммы менялся автоматически. Например при смене названия из ячейки A1 и A2. Причем желательно сделать название по шаблону ПР("График "&"A1"&" по "&A2).
На прямой ввод формулы Excel ругается (Неверная формула) хотя в ячейке такой номер проходит да и ссылка на эту ячейку работает. НО хочется сделать красиво.

DonkeyHottt
Хороший вопрос.
Думаю с помощью VBA.
Ну а самое простое:
Если данные в столбце A A1:A10
То вот так

Код: =A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6" "&A7" "&A8" "&A9" "&A10

=ТЕКСТ(Базовая!RC[-1];"[Синий]ДД/ММ/ГГГГ - ") & Базовая!RC[2] & " " & ЕСЛИ(Базовая!RC[3]>0;" (";"") & Базовая!RC[3] & ЕСЛИ(Базовая!RC[3]>0;") ";"") & Базовая!RC[4] & ". " & "Менеджер Петров"

Подскажите как в этой формуле заставить работать "[Синий]" и сделать надпись "Менеджер Петров" подчеркнутой?

Ответ лучше отправить копией на feks(AT)ngs.ru

Заранее спасибо!

To "ZORRO2005": Спасибо, то, что надо!

ZORRO2005
Спасибо за ответ!
В VBA я не силен.
А второй способ конкретно к моей задаче не очень удобен, так как данные всегда в разных ячейках хоть и одного столбца. Можно конечно заранее написать огромную колбасу и выбирать потом нужную ее часть копированием с подстановкой впереди знака равенства, но по времени это почти тоже, что я использую сейчас (разворачиваю столбец в строку в блокноте и результат копирую в ячейку Странно почему не предусмотрели такой вариант транспонирования? Всю проблему можно было решить установкой одной галочки. Но всеравно Ваш способ возьму на вооружение Еще раз спасибо!

DonkeyHottt
ну с VBA и помочь можно ;-))) Только попросите...

А пока я рекомендую Вам полуручной способ:
выделяете ваши данные в столбце
копировать
перейти в любую ячейку, где справа свободное место
"Специальная вставка" - птичка "Транспонировать"
(оно вставить ваши данные в строку и они уже будут выделены)
Копировать
Перейти в Блокнот - вставить, выделить, копировать
вернуться в Excel - F2 - "вставить"
всё.

доброго времени суток. я хотел бы попросить совет в решении такого вопроса.
упрощенно это выглядит так:

слева: так как сейчас.
справа: так как хотелось бы.
в ячейке С2 находится формула: =ЕСЛИ(A1=1;"";1)
и хотя ячейка становится пустой (согласно условию ЕСЛИ), но так как там находится формула, то "Длинная строка" из ячейки B2 на нее не заходит. а хотелось бы.
ширину столбцов менять нельзя.
данных достаточно много и хотелось бы это дело немного упростить, поскольку сейчас это происходит так:
выделяется столбец С, затем автофильтр по пустым ячейкам, затем del. тогда все ок. но если потом инфа обновляется, а в ячейках уже нет формул, то получаются несросты.вот.
воможен вариант с использованием макроса для удаления формул из таких ячеек, но он даст тот же недостаток, плюс его потребуется устанавливать на всех компах.

может быть, есть более простое решение. буду очень благодарен, если кто-либо сможет помочь. спасибо.

Такой вопрос.
Можно ли сделать так, чтобы при выделении какой либо клетки подсвечивалась бы вся строка, в которой эта клетка стоит?

SERGE_BLIZNUK
Спасибо! Я тоже раньше использовал для этого блокнот, но Ваш способ всетаки быстрее моего C Вашего позволения обращусь к Вам, если возникнут вопросы по VBA и макросам.

Angelo
Можно, если записать этот VBA код в модуль листа.

Код:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Application
.EnableEvents = False

Target.Cells.EntireRow.Select

.EnableEvents = True
End With

End Sub

DonkeyHottt
Можно вот как еще:
Допустим данные в столбце A(A1:A30)
В любой ячейке,где нет данных

Код: =A1:A30

прошу прощения, возможно ли решение моего вопрорса?

VOVFVV
Без удаления формул этого никак не сделаешь. - Если занята соседняя ячейка, на нее переноса не будет.
Так что три варианта: Удалять формулы, когда они дают пустую строку и восстанавливать их с помощью макроса перед обновлением данных. В этом случае будет тот самый перенос, о котором ты говоришь.
Цитата:

ширину столбцов менять нельзя.

А если можно менять высоту строк, то просто ставишь горизонтальное выравнивание: "распределенный (отступ)". Тут перенос просто не понадобится.Использовать комментарии, где будет храниться полное содержание ячейки. В этом случае перенос опять же не понадобится - достаточно будет навести курсор на ячейку.
P.S.: Макрос можно засунуть в надстройку и выложить её в сеть. Тогда ничего устанавливать не надо - достаточно будет на каждом компе один раз подцепить эту надстройку.

Подскажите, как мне автоматизировать сравнение двух списков, т.е.
есть список оборудования с инвентарными номерами:

А В
инв.номер наименование

через месяц бухгалтерия выдала новый список и у меня есть подозрения, что несколько позиций были либо удалены (списано оборудование), либо добавлены. Уверен, что переименовывание не делали, т.е. ни для одного инв. номера не меняли наименование оборудования.
Как можно заставить Excel помочь мне определить какие позиции были удалены или добавлены, чтобы мне не искать это вручную, т.к. список очень большой.
Искать (сравнивать) можно только по инв. номеру.

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

=ЕСЛИ(ВПР('Инвентарная книга'!A3;'Инвентарная книга'!$A$3:$G$351;1)=ВПР('[Инвентаризация 12_2006.XLS]Бухгалтерия спис'!A3;'[Инвентаризация 12_2006.XLS]Бухгалтерия спис'!$A$3:$G$351;1);ИСТИНА;ЛОЖЬ)

Stasssm
1) сначала надо определить в каком файле больше инвентарных номеров.
2) я бы второй файл закинул на отдельный лист в первом файле.
3) инвентарные номера упорядочены по возрастанию?
4) я не понял зачем тебе две фунцкии ВПР. Ты же просто ищешь инв. номер из одного файла в столбце инв. номеров другого файла.

Mushroomer
Есть две таблицы вида:
инв номер/наименование.

Надо сравнить наименования. Первый ВПР - это наименование из первой таблицы, второй ВПР - наименование из второй таблицы. Одному инвентарному номеру должны соответствовать одинаковые наименования, значит первый ВПР = второй ВПР.

Mushroomer

Списки разные - в старом 347 позиций. В новом 350. Может и не сложно было бы найти всего-то 3 наименования, но кто знает может убрали 10 и добавили 13.
Список не отсортирован по инв. номерам (его в обоих случаях выдавала программа Парус). Сортировка идет по наименованию.
Я могу эти две таблицы объединить в одну рабочую книгу, даже больше если это нужно я бы мог поместить вторую рядом с первой таблицей на одном листе - здесь как скажете так и сделаю, лишь бы вы после этих манипуляций подсказали мне чем эти списки прошерстить и выяснить какой порядковый номер добавился а какой удалили.

The okk
Цитата:

Надо сравнить наименования.

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

Stasssm
Пример там где Н/Д - это и есть либо новые либо удаленные элементы. Причем в ВПР (т.к. инвентарные номера не упорядочены) последний параметр у меня 0.

Mushroomer

Файл я получил, посмотрел. В принципе все понятно. Завтра на работе с утра прогоню весь список.
Спасибо огромное.

The okk
спасиб. что ж придется на макросе остановиться. 2 и 3 к сожалению не подходят.
думал, может существует какой либо другой способ.

The okk

Цитата:

Angelo
Можно, если записать этот VBA код в модуль листа.

А можно пожалуйста пальцем тыкнуть, где этот модуль листа находится...?
Спасибо.

ZORRO2005
Очень интересный способ! Спасибо! Запомнил.
Как бы это макросом описать?
И еще господа такой вопрос: Записал я простой макрос. При его выполнении в ячейки записывается определенная информация. Как сделать так, чтобы она записывалась в эти ячейки всех открытых книг? Что нужно дописать в макрос?

Stasssm
Можно попробовать через условное форматирование

DonkeyHottt
Вообще-то про макросы в топике по VBA спрашивают. Ну. да ладно.
В макросе все гораздо проще. Например, так.

Код: Sub test2()
For Each c In Range("A1:A30")
ActiveCell.Value = ActiveCell.Value & " " & c.Value
Next
End Sub

Уважаемые господа Yuk, ZORRO2005, Mushroomer, The okk - в очередной раз обсуждение в данном топике плавно перетекает в обуждение VBA (Excel VBA) - может разрешим (в правилах) и здесь небольшие примеры использования макросов? а то мой пост уже вроде как и оффтопик...

---

Angelo
Цитата:

можно пожалуйста пальцем тыкнуть, где этот модуль листа находится...?

Alt-F11 (или Сервис / Макросы / Редактор VisualBasic )
слева будет Project Explorer (если нет - включить его через Ctrl-R) - двойной щёлчок на нужном листе и в открывшемся окне вставить код.

DonkeyHottt
Цитата:

Очень интересный способ! Спасибо! Запомнил.
Как бы это макросом описать?

зачем этот способ описывать макросом, когда Вы можете в макросе сделать то же самое без всех этих хлопот! Например, для выделенных ячеек (причём не обязательно в столбце - любых выделенных):

Код:
Sub SelectionCellsToCell()
ResultStr = ""
For Each c In Selection
Cur_Cell_Type = TypeName(c.Value)
If Cur_Cell_Type <> "Empty" Then
ResultStr = ResultStr & c.Value & " "
End If
Next
Cells(1, 2).Value = ResultStr ' запишем результат в ячейку B1
End Sub

Angelo
Сервис - Макрос - Редактор Visual Basic (или просто Alt + F11)
Откроется редактор. Слева будет окошко, где будет список всех листов книги. Кликаешь дважды по тому листу, для которого ты хочешь сделать такую фишку.
Открывается модуль этого листа. Вот в него и вставляешь.