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

Вход

Регистрация

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

 

= Мир MS Excel/Мультивыбор в выпадающем списке + работа с данными в ячейке - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Мультивыбор в выпадающем списке + работа с данными в ячейке (Формулы/Formulas)
Мультивыбор в выпадающем списке + работа с данными в ячейке
_Under Дата: Воскресенье, 07.02.2016, 12:01 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Приветствую. Нужна помощь знатоков.
Есть таблица, к примеру, с товарами и дополнительными услугами. Услуг много - порядка десяти, и они еще будут расширяться. Нужно сделать мультивыбор с выпадающего списка, чтобы всё оставалось в ячейке.
В Сети нашел следующий макрос, отредактировал под свой случай, получилось сделать мультивыбор в ячейке.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range
    On Error Resume Next
    If Not Intersect(Target, Range("B4:B10")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
        If Len(oldval) <> 0 And oldval <> newVal Then
            Target = Target & "," & newVal
        Else
            Target = newVal
        End If
        If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
[/vba]

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

Я что-то явно делаю не так. Подскажите, пожалуйста, как быть? Отказаться от этого макроса и искать альтернативный вариант?
// Прошу прощения, файл приложил, но из-за неподтвержденного имейла он сбросился, а текст остался. Прикрепил.
[moder]Ну да, не так - файл не прикладываете.
К сообщению приложен файл: _Microsoft_Offi.xlsm(18Kb)


Сообщение отредактировал _Under - Воскресенье, 07.02.2016, 12:16
 
Ответить
СообщениеПриветствую. Нужна помощь знатоков.
Есть таблица, к примеру, с товарами и дополнительными услугами. Услуг много - порядка десяти, и они еще будут расширяться. Нужно сделать мультивыбор с выпадающего списка, чтобы всё оставалось в ячейке.
В Сети нашел следующий макрос, отредактировал под свой случай, получилось сделать мультивыбор в ячейке.
[vba]
Код
Private Sub Worksheet_Change(ByVal Target As Range
    On Error Resume Next
    If Not Intersect(Target, Range("B4:B10")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        newVal = Target
        Application.Undo
        oldval = Target
        If Len(oldval) <> 0 And oldval <> newVal Then
            Target = Target & "," & newVal
        Else
            Target = newVal
        End If
        If Len(newVal) = 0 Then Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
[/vba]

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

Я что-то явно делаю не так. Подскажите, пожалуйста, как быть? Отказаться от этого макроса и искать альтернативный вариант?
// Прошу прощения, файл приложил, но из-за неподтвержденного имейла он сбросился, а текст остался. Прикрепил.
[moder]Ну да, не так - файл не прикладываете.

Автор - _Under
Дата добавления - 07.02.2016 в 12:01
Udik Дата: Воскресенье, 07.02.2016, 12:30 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1208
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
==
понял зачем Application.Undo так хитро впихнули.

Искать позицию и индексировать не получается

а где Вы пытаетесь это сделать? Нашёл только функцию добавления значения в конец ячейки с мультивыбором.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 07.02.2016, 13:05
 
Ответить
Сообщение==
понял зачем Application.Undo так хитро впихнули.

Искать позицию и индексировать не получается

а где Вы пытаетесь это сделать? Нашёл только функцию добавления значения в конец ячейки с мультивыбором.

Автор - Udik
Дата добавления - 07.02.2016 в 12:30
_Under Дата: Воскресенье, 07.02.2016, 13:10 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007

а где Вы пытаетесь это сделать? Нашёл только функцию добавления значения в конец ячейки с мультивыбором.

Я прикрепил только пример документа с нуля, чтобы не повергать в шок людей другими моими "гениальными" решениями.
Пробовал дублировать ячейку с мультивыбором, а потом в таблице услуг искать позицию и индексировать или использовать ВПР. Естественно, что формула возвращает только одно значение, в то время, как мне надо получать всевозможные, учитывая выбор.
К примеру, в той же В4 выбраны Услуга 3(100), Услуга 2(70), Услуга 3(100) и Услуга4(125).
В столбце Сумма я хочу получить сумму выбранных услуг - 395. При этом, хотелось бы получать динамическую сумму, т.е. выбрал что-то - сумма прибавилась, убрал всё - столбец Сумма пустой.
 
Ответить
Сообщение

а где Вы пытаетесь это сделать? Нашёл только функцию добавления значения в конец ячейки с мультивыбором.

Я прикрепил только пример документа с нуля, чтобы не повергать в шок людей другими моими "гениальными" решениями.
Пробовал дублировать ячейку с мультивыбором, а потом в таблице услуг искать позицию и индексировать или использовать ВПР. Естественно, что формула возвращает только одно значение, в то время, как мне надо получать всевозможные, учитывая выбор.
К примеру, в той же В4 выбраны Услуга 3(100), Услуга 2(70), Услуга 3(100) и Услуга4(125).
В столбце Сумма я хочу получить сумму выбранных услуг - 395. При этом, хотелось бы получать динамическую сумму, т.е. выбрал что-то - сумма прибавилась, убрал всё - столбец Сумма пустой.

Автор - _Under
Дата добавления - 07.02.2016 в 13:10
Udik Дата: Воскресенье, 07.02.2016, 13:25 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1208
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
ну так можно
Код
=СУММПРОИЗВ(($N$3:$N$6)*НЕ(ЕОШ(ПОИСК(массив;B5))))


правда если одну услугу несколько раз ввести то будет считать 1 раз
К сообщению приложен файл: 0t.xlsm(20Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 07.02.2016, 13:28
 
Ответить
Сообщениену так можно
Код
=СУММПРОИЗВ(($N$3:$N$6)*НЕ(ЕОШ(ПОИСК(массив;B5))))


правда если одну услугу несколько раз ввести то будет считать 1 раз

Автор - Udik
Дата добавления - 07.02.2016 в 13:25
Udik Дата: Воскресенье, 07.02.2016, 13:42 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1208
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
если надо чтобы считало повторные значения
Код
=СУММПРОИЗВ(($N$3:$N$6)*(ДЛСТР(B5)-ДЛСТР(ПОДСТАВИТЬ(B5;массив;"")))/ДЛСТР(массив))

==
укоротил формулу
К сообщению приложен файл: 6143062.xlsm(20Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 07.02.2016, 13:54
 
Ответить
Сообщениеесли надо чтобы считало повторные значения
Код
=СУММПРОИЗВ(($N$3:$N$6)*(ДЛСТР(B5)-ДЛСТР(ПОДСТАВИТЬ(B5;массив;"")))/ДЛСТР(массив))

==
укоротил формулу

Автор - Udik
Дата добавления - 07.02.2016 в 13:42
_Under Дата: Воскресенье, 07.02.2016, 13:45 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Udik, спасибо огромное!
В моей таблице дублироваться может только несколько услуг, но это всё решится путём добавления в список "Дополнительная Услуга1".
 
Ответить
СообщениеUdik, спасибо огромное!
В моей таблице дублироваться может только несколько услуг, но это всё решится путём добавления в список "Дополнительная Услуга1".

Автор - _Under
Дата добавления - 07.02.2016 в 13:45
МВТ Дата: Воскресенье, 07.02.2016, 13:45 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 475
Репутация: 135 ±
Замечаний: 0% ±

Excel 2007
Udik, я тоже попытался сделать подобным образом, но если в ячейке одна и таже услуга введены более одного раза, то посчитается только одна. Поэтому, мне кажется, удобнее сделать UDF[vba]
Код
Function tt(Text As String, Rng As Range)
    'Text содержимое ячейки (или ссылка на нее) в которой содержится перечень услуг
    'Rng двухмерный диапазон, содержащий соответствия между наименованием услуги и ее стоимостью
    Dim arr, aText, i As Long, j As Long
    arr = Rng.Value
    aText = Split(Text, ",")
    For i = 0 To UBound(aText)
        For j = 1 To UBound(arr)
            If aText(i) = arr(j, 1) Then tt = tt + Val(arr(j, 2))
        Next
    Next
End Function
[/vba]
 
Ответить
СообщениеUdik, я тоже попытался сделать подобным образом, но если в ячейке одна и таже услуга введены более одного раза, то посчитается только одна. Поэтому, мне кажется, удобнее сделать UDF[vba]
Код
Function tt(Text As String, Rng As Range)
    'Text содержимое ячейки (или ссылка на нее) в которой содержится перечень услуг
    'Rng двухмерный диапазон, содержащий соответствия между наименованием услуги и ее стоимостью
    Dim arr, aText, i As Long, j As Long
    arr = Rng.Value
    aText = Split(Text, ",")
    For i = 0 To UBound(aText)
        For j = 1 To UBound(arr)
            If aText(i) = arr(j, 1) Then tt = tt + Val(arr(j, 2))
        Next
    Next
End Function
[/vba]

Автор - МВТ
Дата добавления - 07.02.2016 в 13:45
Udik Дата: Воскресенье, 07.02.2016, 13:48 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1208
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
то посчитается только одна

Это про какую формулу? Вторая ж повторы считает %)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
то посчитается только одна

Это про какую формулу? Вторая ж повторы считает %)

Автор - Udik
Дата добавления - 07.02.2016 в 13:48
МВТ Дата: Воскресенье, 07.02.2016, 14:13 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 475
Репутация: 135 ±
Замечаний: 0% ±

Excel 2007
Udik, сорри, пока макрос писал, не заметил, что Вы вторую написали :)
 
Ответить
СообщениеUdik, сорри, пока макрос писал, не заметил, что Вы вторую написали :)

Автор - МВТ
Дата добавления - 07.02.2016 в 14:13
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Мультивыбор в выпадающем списке + работа с данными в ячейке (Формулы/Formulas)
Страница 1 из 11
Поиск:

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