Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Заполнение диапазона ячеек в зависимости от значения - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Заполнение диапазона ячеек в зависимости от значения (Формулы/Formulas)
Заполнение диапазона ячеек в зависимости от значения
Webbear Дата: Пятница, 17.11.2017, 10:51 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 2 ±
Замечаний: 0% ±

Excel 2010
Добрый день, уважаемые форумчане!
Необходима помощь в решении следующей задачи:
Требуется автоматически заполнить несколько ячеек текстовыми данными из исходной таблицы в зависимости от значения определенных ячеек в определенном столбце.
Подробности в примере.
Заранее спасибо всем отозвавшимся.
К сообщению приложен файл: 0751702.xlsx (40.0 Kb)
 
Ответить
СообщениеДобрый день, уважаемые форумчане!
Необходима помощь в решении следующей задачи:
Требуется автоматически заполнить несколько ячеек текстовыми данными из исходной таблицы в зависимости от значения определенных ячеек в определенном столбце.
Подробности в примере.
Заранее спасибо всем отозвавшимся.
Автор - Webbear
Дата добавления - 17.11.2017 в 10:51
_Boroda_ Дата: Пятница, 17.11.2017, 11:21 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если на втором листе у Вас все варианты по 11 строк (или по другому количеству, но чтобы одинаково), то вот так можно
Код
=ИНДЕКС(Лист1!C:C;ПОИСКПОЗ(ПРОСМОТР(;-1/(B$15:B17<>"");B$15:B17);Лист1!B$1:B$999;)+СТРОКА()-ПРОСМОТР(;-1/(B$15:B17<>"");СТРОКА(B$15:B17)))&""

Вообще работать будет даже если и различное кол-во строк, но просто при различных количествах может внизу оранжевых попасть значение из следующего блока
К сообщению приложен файл: 0751702_1.xlsx (38.3 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли на втором листе у Вас все варианты по 11 строк (или по другому количеству, но чтобы одинаково), то вот так можно
Код
=ИНДЕКС(Лист1!C:C;ПОИСКПОЗ(ПРОСМОТР(;-1/(B$15:B17<>"");B$15:B17);Лист1!B$1:B$999;)+СТРОКА()-ПРОСМОТР(;-1/(B$15:B17<>"");СТРОКА(B$15:B17)))&""

Вообще работать будет даже если и различное кол-во строк, но просто при различных количествах может внизу оранжевых попасть значение из следующего блока
Автор - _Boroda_
Дата добавления - 17.11.2017 в 11:21
Webbear Дата: Пятница, 17.11.2017, 11:23 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 2 ±
Замечаний: 0% ±

Excel 2010
Спасибо, попробую.
 
Ответить
СообщениеСпасибо, попробую.
Автор - Webbear
Дата добавления - 17.11.2017 в 11:23
sboy Дата: Пятница, 17.11.2017, 11:50 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Вариант макросом в модуле листа
работает при выборе наименования работ
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
     With Sheets(2)
        Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
        Set cl = r.Find(what:=Target.Value).Offset(1, 1)
            If Not cl Is Nothing Then
                vr = .Range(cl, cl.End(xlDown)).Value
                Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
            End If
    End With
End Sub
[/vba]
К сообщению приложен файл: 0751702.xlsm (44.7 Kb)

Яндекс: 410016850021169

Сообщение отредактировал sboy - Пятница, 17.11.2017, 11:50
 
Ответить
СообщениеВариант макросом в модуле листа
работает при выборе наименования работ
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
     With Sheets(2)
        Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
        Set cl = r.Find(what:=Target.Value).Offset(1, 1)
            If Not cl Is Nothing Then
                vr = .Range(cl, cl.End(xlDown)).Value
                Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
            End If
    End With
End Sub
[/vba]
Автор - sboy
Дата добавления - 17.11.2017 в 11:50
Webbear Дата: Пятница, 17.11.2017, 13:00 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 2 ±
Замечаний: 0% ±

Excel 2010
Спасибо, оба варианта работают, но есть одно НО!
В обоих вариантах при удалении наименования работ не очищается заполненный диапазон.
В варианте с макросом из-за описанного выше возможно наложение вновь выбранного диапазона на ранее заполненный при изменении наименования работ.
 
Ответить
СообщениеСпасибо, оба варианта работают, но есть одно НО!
В обоих вариантах при удалении наименования работ не очищается заполненный диапазон.
В варианте с макросом из-за описанного выше возможно наложение вновь выбранного диапазона на ранее заполненный при изменении наименования работ.
Автор - Webbear
Дата добавления - 17.11.2017 в 13:00
sboy Дата: Пятница, 17.11.2017, 13:26 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Цитата Webbear, 17.11.2017 в 13:00, в сообщении № 5 ()
не очищается заполненный диапазон
в вашей просьбе было только про заполнение...
Цитата Webbear, 17.11.2017 в 10:51, в сообщении № 1 ()
Требуется автоматически заполнить

кроме бабы Ванги и Вас никто не знал, что чистить тоже надо..
Добавил
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
        If Target.Value <> "" Then
            With Sheets(2)
                Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
                Set cl = r.Find(what:=Target.Value).Offset(1, 1)
                    If Not cl Is Nothing Then
                        vr = .Range(cl, cl.End(xlDown)).Value
                        Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                        Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
                    End If
            End With
        Else
            If Cells(Rows.Count, 2).End(xlUp).Row <= Target.Row Then
                Set ec = Cells(Rows.Count, 9).End(xlUp)
                Else: Set ec = Target.End(xlDown).Offset(0, 7)
            End If
            Target.Offset(0, 4) = ""
            Range(Target.Offset(0, 7), ec).ClearContents
        End If
End Sub
[/vba]
К сообщению приложен файл: 9494298.xlsm (45.3 Kb)

Яндекс: 410016850021169
 
Ответить
Сообщение
Цитата Webbear, 17.11.2017 в 13:00, в сообщении № 5 ()
не очищается заполненный диапазон
в вашей просьбе было только про заполнение...
Цитата Webbear, 17.11.2017 в 10:51, в сообщении № 1 ()
Требуется автоматически заполнить

кроме бабы Ванги и Вас никто не знал, что чистить тоже надо..
Добавил
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Offset(0, 7) <> "" Then
        MsgBox "В этой строке есть наименование"
        Exit Sub
    End If
        If Target.Value <> "" Then
            With Sheets(2)
                Set r = .Range(.Cells(2, 2), .Cells(Rows.Count, 2).End(xlUp))
                Set cl = r.Find(what:=Target.Value).Offset(1, 1)
                    If Not cl Is Nothing Then
                        vr = .Range(cl, cl.End(xlDown)).Value
                        Target.Offset(1, 7).Resize(UBound(vr), 1) = vr
                        Target.Offset(0, 4).FormulaR1C1 = "=SUM(R[1]C[6]:R[" & UBound(vr) & "]C[6])"
                    End If
            End With
        Else
            If Cells(Rows.Count, 2).End(xlUp).Row <= Target.Row Then
                Set ec = Cells(Rows.Count, 9).End(xlUp)
                Else: Set ec = Target.End(xlDown).Offset(0, 7)
            End If
            Target.Offset(0, 4) = ""
            Range(Target.Offset(0, 7), ec).ClearContents
        End If
End Sub
[/vba]
Автор - sboy
Дата добавления - 17.11.2017 в 13:26
Webbear Дата: Пятница, 17.11.2017, 13:34 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 2 ±
Замечаний: 0% ±

Excel 2010
Спасибо еще раз, буду разбираться!
P.S:Я тоже не знал, пока не посмотрел, поэтому остаётся только Баба Ванга :D
 
Ответить
СообщениеСпасибо еще раз, буду разбираться!
P.S:Я тоже не знал, пока не посмотрел, поэтому остаётся только Баба Ванга :D
Автор - Webbear
Дата добавления - 17.11.2017 в 13:34
Webbear Дата: Пятница, 17.11.2017, 18:01 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 2 ±
Замечаний: 0% ±

Excel 2010
Подскажите пожалуйста еще, если не трудно, как дополнить код, чтобы заполнить ячейки К17:K25 в виде Лист1!E3:E10*D$16, К27:K34 в виде Лист1!E41:E48*D$26 и.т.д. аналогично выше описанному в зависимости от значения в столбце "В"
К сообщению приложен файл: 0601414.xlsm (92.1 Kb)
 
Ответить
СообщениеПодскажите пожалуйста еще, если не трудно, как дополнить код, чтобы заполнить ячейки К17:K25 в виде Лист1!E3:E10*D$16, К27:K34 в виде Лист1!E41:E48*D$26 и.т.д. аналогично выше описанному в зависимости от значения в столбце "В"
Автор - Webbear
Дата добавления - 17.11.2017 в 18:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Заполнение диапазона ячеек в зависимости от значения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!