| Правила | Регистрация | Пользователи | Поиск | Сообщения за день | Все разделы прочитаны |  Справка по форуму | Файлообменник |

Вернуться   Форум DWG.RU > Программное обеспечение > Прочее. Программное обеспечение > Как в Excel создать дополнительные фильтры или выпадающие списки в столбце и чтобы данные определенной ячейки были связаны с соседними ячейками по горизонтали?

Как в Excel создать дополнительные фильтры или выпадающие списки в столбце и чтобы данные определенной ячейки были связаны с соседними ячейками по горизонтали?

Ответ
Поиск в этой теме
Непрочитано 12.09.2016, 12:28
Как в Excel создать дополнительные фильтры или выпадающие списки в столбце и чтобы данные определенной ячейки были связаны с соседними ячейками по горизонтали?
ВиталийР
 
Регистрация: 09.09.2016
Сообщений: 31

Уважаемые коллеги!
Помогите пожалуйста в решении следующего вопроса. При создании базы данных по производителям металлопроката, я столкнулся с проблемой создания дополнительного фильтра или выпадающего списка при этом ячейки списка не связаны с соседними ячейками по горизонтали.
Как связать ячейки по горизонтали, так сказать сгруппировать? Это мне нужно для того, чтобы при выборе в выпадающем списке (дополнительном фильтре) «города», в столбце «регион» мне список выдавал конкретный город, а также дилера, телефон и почту из соответственно столбцов «дилер», «телефон», «почта». Цель создания выпадающего списка (дополнительного фильтра) минимизировать расширения высоты строки, данное расширение обусловлено, тем, что каждый производитель имеет более 2000 наименований и в каждом наименовании приходиться прописывать всех дилеров производителя, из-за чего каждая строка одного наименования расширяется значительно, что делает таблицу не компактной, визуально не удобной и трудно читаемой. При создании дополнительного фильтра или выпадающего списка я могу скрыть весь список дилеров, оставив только одну строку в которой я буду выбирать города из этого списка, таким образом я решу вопрос с расширением высоты строк.

Во вложении таблица, в которой я уже создал выпадающий список, но он работает не как фильтр, а также города (столбец «регион») не связаны с соседними ячейками по горизонтали (столбцы: «дилер», «телефон», «почта»).

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

Вложения
Тип файла: xlsx 12345.xlsx (14.7 Кб, 79 просмотров)

Просмотров: 22196
 
Непрочитано 16.09.2016, 06:54
#81
trir


 
Регистрация: 18.12.2010
Сообщений: 3,227


за это время можно выучить SQL
trir вне форума  
 
Непрочитано 16.09.2016, 08:58
#82
ShaggyDoc

Thượng Tá Quân Đội Nhân Dân Việt Nam
 
Регистрация: 14.03.2005
44d32'44"С, 33d26'51"В
Сообщений: 10,840


Цитата:
Сообщение от trir Посмотреть сообщение
за это время можно выучить SQL
За время этого обсуждения уже можно было бы научиться работать с БД в Access. Но еще лучше - не в самой среде MS-Access, а в среде программирования. Там все эти вопросы с "дполнительными фильтрами", "выпадающими списками", "диапазонами" решаются элементарно. Ну и "деревья", поиск, фильтрация, сортировка, надежный безошибочный ввод данных.

А если еще использовать SQL, то можно решить вообще всё. А еще ведь возникнут вопросы с вычислениями и выводом конечной продукции в виде отчета...

Да, Excel очень хорошая программа, позволяющая решать множество вычислительных задач без привлечения программистов. Хотя уже чуть более сложное приходится делать с использованием VBA. Но рассматривать её таблицы как базы данных - несерьезно.

Ну да, можно только с помощью топора, клиньев и мха и без единого гвоздя построить здание. Но лучше все-таки для каждой работы использовать соответствующие инструменты.
ShaggyDoc вне форума  
 
Непрочитано 16.09.2016, 09:41
#83
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Плюс экселя - что он всегда под рукой, везде, в любой комплектации офиса. Задачу подобной сложности можно решить и в нем - но если решать, а не ждать решения. Даже такие примитивные вещи, как динамические таблицы, ТС не хочет использовать - вместо этого руками задает фильтры (при создании динтаблиц они создаются автоматом и можно время потратить на что-то другое). Он даже не понял сарказма по поводу именованных диапазонов для связанных списков для десятков тысяч строк данных - а вы предлагаете ему без разбега перейти на БД)
Сергей812 вне форума  
 
Непрочитано 18.09.2016, 00:35
#84
Enik

ГИП
 
Регистрация: 07.06.2015
Сообщений: 1,120


Вот, нашёл интересный материал в сети.
http://www.planetaexcel.ru/techniques/2/100/

Полезный макрос для получения выборки из БД по заданным условиям.
Enik вне форума  
 
Непрочитано 18.09.2016, 01:42
#85
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


можно и так, но быстрее будет скопировать весь диапазон Range в Variant, и далее уже с массивом в Variant работать)

----- добавлено через ~4 мин. -----
имею в виду в указанной выше по ссылке функции VLOOKUP2
Сергей812 вне форума  
 
Непрочитано 18.09.2016, 02:25
#86
Enik

ГИП
 
Регистрация: 07.06.2015
Сообщений: 1,120


Цитата:
Сообщение от Сергей812 Посмотреть сообщение

можно и так, но быстрее будет скопировать весь диапазон Range в Variant, и далее уже с массивом в Variant работать)
Суть уловил. Работа не с диапазоном в пространстве листа, а с переменной в виде двухмерного массива. Но, поскольку данным ЯП не владеют, то прошу вас реализовать это решение в коде. Будьте так любезны. Я и форумчане - все будем благодарны.

Это очень полезный макрос будет.
Enik вне форума  
 
Непрочитано 18.09.2016, 03:10
#87
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Типа такого
Код:
[Выделить все]
Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
                  N As Long, ResultColumnNum As Long) As Variant
    Dim I1 As Long
    Dim lArray As Variant: lArray = Table
    For I1 = LBound(lArray, 1) To UBound(lArray, 1)
      If (lArray(I1, SearchColumnNum) = SearchValue) Then N = N - 1
      If (N = 0) Then
        VLOOKUP2 = lArray(I1, ResultColumnNum)
        Erase lArray
        Exit Function
      End If
    Next I1
End Function
Сергей812 вне форума  
 
Непрочитано 18.09.2016, 05:50
#88
trir


 
Регистрация: 18.12.2010
Сообщений: 3,227


Select ResultColumnNum from Table where SearchColumnNum = SearchValue
trir вне форума  
 
Непрочитано 18.09.2016, 14:16
#89
Enik

ГИП
 
Регистрация: 07.06.2015
Сообщений: 1,120


Цитата:
Сообщение от Сергей812 Посмотреть сообщение
Типа такого
Всё работает. Спасибо!

----- добавлено через ~4 мин. -----
А ещё у меня возникла мысль, что можно не формировать базу из значений в заранее заданных списках. Лучше наоборот. Можно из вручную заполненной базы из каждого столбца формировать списки уникальных значений. А потом уже эти списки в окно поиска вставлять. Чтобы самому составителю базы сразу были видны его же ошибки.

http://www.excel-vba.ru/chto-umeet-e...sya-znachenij/
Enik вне форума  
 
Непрочитано 19.09.2016, 02:17
#90
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Цитата:
Сообщение от Enik Посмотреть сообщение
А ещё у меня возникла мысль, что можно не формировать базу из значений в заранее заданных списках. Лучше наоборот. Можно из вручную заполненной базы из каждого столбца формировать списки уникальных значений. А потом уже эти списки в окно поиска вставлять. Чтобы самому составителю базы сразу были видны его же ошибки.
На мой взгляд, надо разбивать на отдельные подзадачи-листы:
  1. Список регионов (городов);
  2. Список производителей и общей информации о них (без сортамента);
  3. Список дилеров для всех производителей (там же в списке ссылка на производителя) - скрытый лист;
  4. Лист для редактирования списка дилеров - в верхней части выпадающий список с уникальными именами производителей (ссылка на поз. №2), под ним таблица со списком дилеров для этого производителя (выборка макросом из списка поз.№3). После завершения редактирования в списке поз. 3 происходит обновление части таблицы, относящейся к данному производителю;
  5. Список сортамента для всех производителей. В нем же в дополнительных столбцах условия для выборки, над таблицей поля для ввода значений условий. Лист скрытый;
  6. Лист для редактирования списка сортамента - аналогично списку дилеров поз.№4;
  7. Ну и лист подбора, собственно. Там поля ввода/выбора параметров сортамента, региона и т.д.. Далее из таблицы поз.5 делается выборка производителей, потом по заданному региону - список дилеров. И это информация выводить в таблицу под полями ввода.

Естественно, это все не в "чистом" excel, а комбинируя взаимные связи между листами через формулы с небольшой помощью vba. Ну или смотрите в сторону БД)
Сергей812 вне форума  
 
Непрочитано 19.09.2016, 16:26
#91
Enik

ГИП
 
Регистрация: 07.06.2015
Сообщений: 1,120


Цитата:
Сообщение от Сергей812 Посмотреть сообщение
На мой взгляд, надо разбивать на отдельные подзадачи-листы: ...
Ну да, как-то так оно и должно быть. Если по-людски.

Но я придумал ещё одно решение. Если стоите - сядьте, а то упадёте.

Оно эффективное и простое до безобразия. Не важно, как ТС вздумает составлять и дополнять БД - любую неопределённость и полёт мысли это решение преодолеет.

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

И всё! Как поиск в гугле.
Enik вне форума  
 
Непрочитано 19.09.2016, 16:48
#92
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Цитата:
Сообщение от Enik Посмотреть сообщение
А нужно-то всего-навсего на отдельном листе объединить строки базы в куски текста, после чего осуществлять поиск по ключевым словам методом подстроки в строке. Ввёл несколько слов и на выходе получил строки базы, содержащие нужную информацию
Зачем? Вводишь в таблицах данных скрытые столбцы с формулой сравнения, которые все ссылаются на одну ячейку с искомым значением. Забил данные в ячейку и применил фильтры по столбцам сравнения.
И давно гуляет по сети вариант нечеткого поиска - но он тормозной из-за нескольких вложенных циклов.
Сергей812 вне форума  
 
Непрочитано 19.09.2016, 17:28
#93
Enik

ГИП
 
Регистрация: 07.06.2015
Сообщений: 1,120


Цитата:
Сообщение от Сергей812 Посмотреть сообщение
Зачем?
А так проще. К структуре БД - привязки никакой. Создавай столбцов сколько хочешь и пиши всё, что хочешь. Вплоть до базы из 1 столбца с содержанием типа "Руслан трубы дёшево", "Гаго лист шпунт экскаватор Липецк". Иными словами, сажаешь за базу любую блондинку и не боишься за сохранность своего детища. Я конкретно в этом решении вижу самый короткий путь к реализации задумки ТС.
Enik вне форума  
 
Непрочитано 19.09.2016, 18:45
#94
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


это плохо на самом деле - и так эксель не является БД сам по себе, хотя можно простые вещи там реализовать. Если еще и структуру как попало создавать без учета того, что эксель является электронной таблицей в первую очередь - то это без блондинки обойдется) Да и подобные задачи решаются бэкапами, транзакциями (чего у экселя никогда и не было) и т.д. А самый короткий путь ТС уже выбрал похоже, судя по молчанию - делает именованные области)
Сергей812 вне форума  
 
Автор темы   Непрочитано 08.10.2016, 18:36
#95
ВиталийР


 
Регистрация: 09.09.2016
Сообщений: 31


Всём спасибо огромное, меня из форума убедил один специалист, что ексел не потянет миллион строк, что после 50 000 строки база в екселе накроется. Я заказал ему сделать базу за денежку
ВиталийР вне форума  
 
Непрочитано 08.10.2016, 18:49
#96
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Цитата:
Сообщение от ВиталийР Посмотреть сообщение
Всём спасибо огромное, меня из форума убедил один специалист, что ексел не потянет миллион строк, что после 50 000 строки база в екселе накроется. Я заказал ему сделать базу за денежку
специалист слукавил - просто на листе больше миллиона строк не поддерживается. И спокойно догонял на тестах динтаблицы на 500'000 строк - ничего не падало)
Сергей812 вне форума  
 
Непрочитано 08.10.2016, 20:56
#97
trir


 
Регистрация: 18.12.2010
Сообщений: 3,227


Цитата:
И спокойно догонял на тестах динтаблицы на 500'000 строк - ничего не падало)
сколько такой эксель кушал памяти?
Он может и может столько, если осторожно, но лучше не надо
trir вне форума  
 
Непрочитано 08.10.2016, 22:39
#98
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Цитата:
Сообщение от trir Посмотреть сообщение
сколько такой эксель кушал памяти?
много, пару гб) Но это был тест - упадет Excel или нет.

В данном случае самая большая таблица была бы сортамента, таблицы производителей и их дилеров была бы меньше гораздо. ТС пошел по неправильному пути занесения все в одну таблицу - получил многократную избыточность информации (на порядки).
Сергей812 вне форума  
 
Непрочитано 09.10.2016, 01:14
#99
trir


 
Регистрация: 18.12.2010
Сообщений: 3,227


Цитата:
много, пару гб)
значит Excel был х64. А люди обычно ставят х32 и не заморачиваются...
trir вне форума  
 
Непрочитано 09.10.2016, 01:37
#100
Сергей812


 
Регистрация: 10.08.2013
Сообщений: 6,376


Цитата:
Сообщение от trir Посмотреть сообщение
значит Excel был х64. А люди обычно ставят х32 и не заморачиваются...
оптимизируйте хранение данных - и x32 хватит. Это был лишь тест) Ну раскрутил кто-то ТС на БД, теперь бизнес у владельца фирмы, где работает ТС - будет зависеть от разработчика БД в том числе. Excel прозрачнее)
Сергей812 вне форума  
Ответ
Вернуться   Форум DWG.RU > Программное обеспечение > Прочее. Программное обеспечение > Как в Excel создать дополнительные фильтры или выпадающие списки в столбце и чтобы данные определенной ячейки были связаны с соседними ячейками по горизонтали?

Размещение рекламы
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Как извлечь данные из файла Excel LexaI Программирование 14 12.04.2014 20:14
Подскажите как в Excel совместить две ячейки в одну Львиное сердце Прочее. Программное обеспечение 5 31.08.2009 13:58
Ламерский вопрос по Excel: как сделать, чтобы не слетало форматирование исходных ячеек при вырезании и перетаскивании? kp+ Прочее. Программное обеспечение 7 23.02.2009 15:27
Сохранения адреса ячейки в Excel Малюк Прочее. Программное обеспечение 2 30.03.2008 09:33