Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формула для всего столбца с пропуском некоторых ячеек
 
#1
05.11.2015 19:53:09
Здравствуйте. Подскажите, как протянуть формулу (ВПР) на весь столбец, пропуская некоторые ячейки, в которых есть формула Автосумма. Файлик прикрепляю.
Есть таблица. Есть строки 1 и 2 уровня. ВПРкой подтягиваются данные из др. табличке а ячейки 2го уровня. В ячейках 1го уровня стоит Автосумма по соответствующим ячейкам 2го уровня. В каждой группе (все 2го уровня) разное количество строк. В столбце D так же стоит Автосумма. На форуме смотрела похожие темы, но там можно протянуть с определенным шагом, здесь конкретных шагов нет. Какие то строки 2го уровня будут как добавляться,так и удаляться периодически. По файлу: необходимо протянуть ВПР в В3:В24 минуя ячейки В10, В15, В20, В22. Возможно ли такое? И Возможно ли, при подтягивании ВПР, что бы вставлялись ТОЛЬКО значения, миную форматы из других таблиц?  
 
 
 
#2
05.11.2015 20:02:53
Здравствуйте. Сначала скройте автоильтром желтые строки и протяните формулу по всему столбцу, или скопируйте одну ячейку (Ctrl+C) и выделив столбец вставьте (Ctrl+V)
Изменено: gling - 05.11.2015 20:04:54
 
 
 
#3
05.11.2015 20:03:49
Добрый вечер.
А где в примере хоть один ВПР?
А так-то - думаю, только макросом можно воплотить.
Кому решение нужно - тот пример и рисует.
 
 
 
#4
05.11.2015 20:10:18
gling, дело в том, что первоначально так и делала, но после отображения всей таблицы выяснилось, что даже при таком фильтре, протягивается ВПРка и на желтые строки((((
Цитата
Пытливый написал: где в примере хоть один ВПР?
Я не стала перегружать Пример файла ВПРками, думаю, вопрос не только в формуле ВПР, но там же можно и другую формулу какую нить вставлять.
Вопрос в том, как сделать пропуски на желтые строки при протягивании формул на столбец))))
 
 
 
#5
05.11.2015 20:22:32
При протягивании и полном копировании ячеек такого о чем вы пишите не замечал. Знаю если скопировать ячейку и выделив диапазон со скрытыми ячейками и вставить спец вставкой "формулу", то формула вставится и в скрытые. Есть еще функция в Вашей версии "Выделить видимые ячейки" (Alt+;) попробуйте.
 
 
 
#6
05.11.2015 20:27:16
Макросом примерно так (ВПР допишите сами в стиле R1C1)
Код
Sub tt()
    Dim Cell As Range
    For Each Cell In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        With Cell
            If Not .HasFormula Then .Offset(, 1).FormulaR1C1Local = "=ВПР....."
        End With
    Next
End Sub
Или так
Код
Sub tt()
    Dim Cell As Range
    For Each Cell In Range("A2:A" & CellsI(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeConstants)
        With Cell
            .Offset(, 1).FormulaR1C1Local = "=ВПР....."
        End With
    Next
End Sub
Изменено: МВТ - 05.11.2015 20:30:51
 
 
 
#7
05.11.2015 20:34:12
Здравствуйте уважаемая!
Я предлагаю такой вариант, выделяем диапазон В3:В24, а далее:
В ленте "Главная" идем таким путем "Найти и выделить" - > "Выделить группу ячеек" -> "Константы"
Останутся выделены ячейки без формул, в этот момент жмити "=" пишите свою формулу и при завершении нажимаете Ctrl+Enter
 
 
 
#8
05.11.2015 21:16:44
МВТ, Спасибо за макрос. ВПР Вставила вот в таком же виде нужно: .Offset(, 1).FormulaR1C1Local = "=ВПР(RC1;R28C1:R36C2;2;ЛОЖЬ)" ? (Сохранила)
Затем, при применении Макроса, пишет Can't execute code in break mode (((  Очень бы хотелось все же его сделать рабочим

justirus, Ваш вариант работает )))) Спасибо
 
 
 
#9
05.11.2015 21:37:27
Инна Волкова, Вы пытаетесь в столбце В искать столбец В. Напишите, что и где Вам надо найти?
 
 
 
#10
05.11.2015 22:00:34
МВТ, Вот пример. нужно, к примеру, подтянуть в столбец В, значения из нижней таблицы
 
 
 
#11
05.11.2015 22:07:28
Здравствуйте. Наверно формула должна быть такой.
Код
"=VLOOKUP(RC1,R28C1:R36C2,2,FALSE)"
или заменить строку
Код
.Offset(, 1).FormulaR1C1 = "=VLOOKUP(RC1,R28C1:R36C2,2,FALSE)"
Изменено: gling - 05.11.2015 22:12:03
 
 
 
#12
05.11.2015 22:11:21
Для Вашего примера (диапазон легко поменять  :))
Код
Sub tt()
    Dim Cell As Range
    Range("B2:B24").SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _
        = "=ВПР(RC1;R28C1:R36C2;2;ËÎÆÜ)"
End Sub
 
 
 
 
#13
05.11.2015 22:17:57
МВТ, О! Супер! Все работает. Спасибо
А скажите, вот если у меня 50 столбцов таких, мне копировать 50 таких макросов под каждый столбец? Т.Е в каждом столбце будет ВПРка из разных таблиц
Изменено: Инна Волкова - 05.11.2015 22:32:53
 
 
 
#14
05.11.2015 22:21:09
Инна Волкова, вот, немного допилил, попробуйте (исправил опечатку)
Код
Sub tt()
    Dim Cell As Range
    Dim Formula
    On Error Resume Next
    Set Cell = Application.InputBox("Введите диапазон, в который будет вставлена формула", Type:=8)
    If Err Then Exit Sub
    Formula = InputBox("Введите формулу в стиле R1C1 без кавычек и знака """"=""""")
    If Formula = "" Then Exit Sub
    Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _
        = "=" & Formula
End Su
UPD вот еще один вариант. В ячейку вписываете формулу в ЛЮБОМ формате (A1 или R1C1), потом распространяете ее на пустые ячейки диапазона, который выбираете в диалоговом окне
Код
Sub tt_1()
    Dim Cell As Range
    Dim Formula As String
    Formula = ActiveCell.FormulaR1C1Local
    If Formula = "" Then
        MsgBox "Ячейка не содержит формулы", vbInformation
        Exit Sub
    End If
    On Error Resume Next
    Set Cell = Application.InputBox("Введите диапазон, на который будет распространена формула", Type:=8)
    If Err Then Exit Sub
    If Formula = "" Then Exit Sub
    Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local _
        = Formula
End Sub
Изменено: МВТ - 05.11.2015 22:31:38
 
 
 
#15
05.11.2015 22:47:34
Цитата
МВТ написал: UPD вот еще один вариант.
Т.Е этот вариант можно применять к каждому из 50 столбцов, только где-то должны быть написаны эти 50 формул в отдельных ячейках, так?
Только объясните по поводу диапазона,который нужно прописать в макрос? Это должен быть диапазон именно столбца, куда вставить формулу необходимо или можно указать диапазон всех 50 столбов, но на каждый выбирать нужную формулу из 50 заполненных ячеек отдельных?

Просто я сейчас пробовала вписать диапазон столбца, но применять могу не только к этому столбцу
Изменено: Инна Волкова - 06.11.2015 10:17:20
 
 
 
#16
05.11.2015 22:54:29
Второй макрос берет формулу из ячейки, которая была активной на момент его запуска и распространяет на пустые ячейки диапазона, который Вы выберете, примерно, как когда Вы растягиваете формулу за уголок  :)
 
 
 
#17
05.11.2015 23:14:50
МВТ, Круто! А формат ячейки меняется (если брать пример ВПР, то меняет на тот формат ячейки из которой он значения вытягивает) или остается тот же, что и был, до вставки формулы?  
 
 
 
#18
05.11.2015 23:24:02
Формат не трогает, это отдельно надо делать, причем, довольно муторно. Каждый компонент формата (имя шрифта, толщина, размера, цвет и т.д., цвет фона, обрамление) необходимо задавать отдельно. Но теоретически сделать можно
UPD вот, придумал
Код
Sub tt_2()
    Dim Cell As Range
    Dim Formula As String
    Dim Rng As Range, Ar As Range
    Set Rng = ActiveCell
    Formula = Rng.FormulaR1C1Local
    If Formula = "" Then
        MsgBox "Ячейка не содержит формулы", vbInformation
        Exit Sub
    End If
    On Error Resume Next
    Set Cell = Application.InputBox("Введите диапазон, на который будет распространена формула", Type:=8)
    If Err Then Exit Sub
    Application.ScreenUpdating = False
    If Formula = "" Then Exit Sub
    Cell.SpecialCells(xlCellTypeBlanks).FormulaR1C1Local = Formula
    Rng.Copy
    For Each Ar In Cell.Areas
        Ar.PasteSpecial (xlPasteFormats)
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
P.S. немного переделал
Изменено: МВТ - 06.11.2015 01:27:48
 
 
 
#19
05.11.2015 23:52:38
Инна Волкова, Ваш #15 - ну вот зачем там такое цитирование? Исправлено.
 
 
 
#20
06.11.2015 18:08:05
Цитата
МВТ написал:
UPD вот, придумал
Так получается, что макрос убивает и форматы желтых ячеек тоже
 
 
 
#21
06.11.2015 22:53:28
Да, Вы правы, ошибся. Так надо
Код
Sub tt_2()
    Dim Cell As Range
    Dim Formula As String
    Dim Rng As Range, Ar As Range
    Set Rng = ActiveCell
    Formula = Rng.FormulaR1C1Local
    If Formula = "" Then
        MsgBox "Ячейка не содержит формулы", vbInformation
        Exit Sub
    End If
    On Error Resume Next
    Set Cell = Application.InputBox("Введите диапазон, на который будет распространена формула", Type:=8)
    If Err Then Exit Sub
    Application.ScreenUpdating = False
    If Formula = "" Then Exit Sub
    Rng.Copy
    With Cell.SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1Local = Formula
        For Each Ar In .Areas
            Ar.PasteSpecial (xlPasteFormats)
        Next
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 
 
 
#22
07.11.2015 19:25:02
Цитата
МВТ написал:
Да, Вы правы, ошибся. Так надо
а еще вопрос такой: а можно сделать так, что бы после подтягивания ВПР, там где ВПР в итоге остались только значения. Т.е. я обычно выделяла диапазоны с ВПР (что опять же не удобно, пропускать приходится желтые строки) копировала и вставляла Значения. Ну потому как файлы с таблицами откуда подтягиваются значения потом удаляются, сл-но, открыв, док-т на другом ПК в ячейках НД выскакивает.
 
 
 
#23
07.11.2015 20:29:54
На другом ПК надо разорвать связи, формулы сами на значения поменяются ☺
Всё сложное - не нужно. Всё нужное - просто /М. Т. Калашников/
 
 
 
#24
07.11.2015 20:56:26
Инна Волкова, макросом можно. Как-то так
Код
Sub mm()
    With Selection
        .Formula = .Value
    End With
End Sub
 
 
 
#25
09.11.2015 14:02:10
МВТ, это все формулы "рубит", а что бы только ВПР?
 
 
 
Страницы: 1
Читают тему
Наверх