Помогите организовать эффективный поиск в Excel
Добрый, день, не нашел поиском ничего стоящего и поэтому решил все таки спросить. Как можно организовать эффективно поиск в ячейках в Excel?
Задача следующая: в ячейку внизу таблицы вводится скажем имя клиента, необходимо узнать встречался ли он в таблице где-то сверху, и если встречался то сколько раз? Он соотвественно может встречаться не только в столбце в котором его добавили, но и в нескольких соседних, скажем имя руководителя, имя бухгалтера, имя первого поручителя, имя второго поручителя. Казалось бы, чего проще написал =счетесли() + счетесли() +...+ сколько надо раз. Но это достаточно сильно начинает тормозить работу Excel. Еще было бы неплохо выводить номера строк в которых это имя встретилось. НУ что-то вроде (иванов, строки: 10,58,1989,5901).
Что скажете как это сделать? :-) заранее благодарен.
Задача следующая: в ячейку внизу таблицы вводится скажем имя клиента, необходимо узнать встречался ли он в таблице где-то сверху, и если встречался то сколько раз? Он соотвественно может встречаться не только в столбце в котором его добавили, но и в нескольких соседних, скажем имя руководителя, имя бухгалтера, имя первого поручителя, имя второго поручителя. Казалось бы, чего проще написал =счетесли() + счетесли() +...+ сколько надо раз. Но это достаточно сильно начинает тормозить работу Excel. Еще было бы неплохо выводить номера строк в которых это имя встретилось. НУ что-то вроде (иванов, строки: 10,58,1989,5901).
Что скажете как это сделать? :-) заранее благодарен.
Что касается СЧЕТЕСЛИ, то можно делать один большой СЧЕТЕСЛИ(A1:F5000;"Иванов") вместо суммы по всем столбцам. В то что один СЧЕТЕСЛИ тормозит работу Экселя я не верю. Если же у тебя СЧЕТЕСЛей много, то распиши поподробнее.
Что касется варианта "иванов, строки: 10,58,1989,5901", то тут макрос нужен. Макрос, кстати, очень простой.
Что касется варианта "иванов, строки: 10,58,1989,5901", то тут макрос нужен. Макрос, кстати, очень простой.
Да наверное я чуть не правильно выразился. Столбцы к сожалению не соседние. А очень разные, могу сказать точно 26ой,36ой,233ий и 234ый. Таблица очень быстро растет каждый день добавляется около 100 строк. Уже 9000. Между этими столбцами соответсвенно тоже данные. И как вы понимаете это не единственная формула которая там есть. Так что пересчет ячеек и подсчет конкретно этой формулы действительно занимает время достаточно для того чтобы раздражало. Соответственно таким образом мы посчитаем количество повторяющихя клиентов с именем Иванов. Но так же надо еще посчитать количество повторяющихся руководителей, и количество поторяющихся поручителей. 1 строка как бы одна сделка и надо выявить любые повторы по этой сделке, как были раньше. Вдруг поручители козлы и постоянно нас кидают? А клиент и руководитель новые -подставные? Или руководитель который уже нас кинул - вдруг стал в новой сделке у кого-то поручителем - факт что сделка ни к черту не годится. Ну и макрос честно говоря для меня тоже не очень такой уж и простой :-(
Так в чем проблема-то..
Если хочешь получить данные в виде "(иванов, строки: 10,58,1989,5901)", то функцией СЧЕТЕСЛИ() ты не обойдешься:)..
При любом раскладе необходимо писать макрос(функцию)..
Насчет макроса - код может быть немного нагроможден, да и только - сама реализация, как сказал Cutty Surk, несложная.. Пробуй написать сам, если чо не получится - показывай!!
З.Ы.: может тебе в тот же самый MS Access "перелезть" - там реализация твоей задачи будет намного проще, например с помощью сиквэл-запросов!! Хотя понимаю, что возможно задача требует решения именно в MS Excel:)
Если хочешь получить данные в виде "(иванов, строки: 10,58,1989,5901)", то функцией СЧЕТЕСЛИ() ты не обойдешься:)..
При любом раскладе необходимо писать макрос(функцию)..
Насчет макроса - код может быть немного нагроможден, да и только - сама реализация, как сказал Cutty Surk, несложная.. Пробуй написать сам, если чо не получится - показывай!!
З.Ы.: может тебе в тот же самый MS Access "перелезть" - там реализация твоей задачи будет намного проще, например с помощью сиквэл-запросов!! Хотя понимаю, что возможно задача требует решения именно в MS Excel:)
Значит, смотри.
Очень частая ситуация - есть большая таблица, в которой содержимое некоторых столбцов есть формулы, которые вычисляются единожды при заполнении таблицы и потом не меняются. Но количество строк растёт, с ним растёт и количество формул - а Эксель вынужден пересчитывать одни и те же 9000 формул каждый раз.
Что делать?
Абсолютно универсальный совет: По возможности заменяйте формулы в таблицах значениями!
Заменить формулы значениями - это значит выделить диапазон, нажать Ctrl+C, а затем выполнить специальную вставку (правая кнопка мыши или главное меню) значений на самого себя. Для простоты использования рекомендую вынести кнопку "вставить значения" на панель инструментов
В твоём случае это выглядит так:
Пусть для простоты первый и второй столбцы заполняются руками, а третий - формула, например, =A2+B2. Мы заменяем значениями все формулы, кроме ПЕРВОЙ строчки таблицы и ПОСЛЕДНЕЙ. В итоге вместо 9000 формул в столбце осталось только 2. Первая строчка (строчка с заголовками столбцов не в счёт, конечно) нужна для того, чтобы можно было легко посмотреть, что у нас за столбец такой, как хранилище этой самой формулы и для внесения изменений (об этом ниже). Последняя строчка нужна для удобства добавления данных вниз таблицы - если данные не добавляются, то можно только первую строчку с формулами оставлять. Итак, пришли новые 100 строк. Ты впечатываешь ручную информацию, а затем продляешь формулу из последней, 9000-й строчки на новые данные (надеюсь, ты умеешь это делать одним двойным щелчком мыши). В итоге у тебя стали формулы с 9000-й по 9100-ю строчки. Теперь с 9000-й по 9099-ю заменяешь значениями, а в 9100-й - она теперь последняя - оставляешь формулу.
Если потребовалось, например, в столбце заменить формулу на A2-B2 - меняешь её в верхней строчке, продляешь вниз, заменяешь значениями - и всё ок.
При правильной, выверенной, последовательности нажатий клавиш на клавиатуре такие операции по замене формул значениями делаются очень просто.
Что ты за это получишь:
1. Уменьшение размера файла (формулы занимают больше места)
2. Убыстрение работы.
Предлагаю осмыслить вышеописанное, а затем продолжим. :)
Очень частая ситуация - есть большая таблица, в которой содержимое некоторых столбцов есть формулы, которые вычисляются единожды при заполнении таблицы и потом не меняются. Но количество строк растёт, с ним растёт и количество формул - а Эксель вынужден пересчитывать одни и те же 9000 формул каждый раз.
Что делать?
Абсолютно универсальный совет: По возможности заменяйте формулы в таблицах значениями!
Заменить формулы значениями - это значит выделить диапазон, нажать Ctrl+C, а затем выполнить специальную вставку (правая кнопка мыши или главное меню) значений на самого себя. Для простоты использования рекомендую вынести кнопку "вставить значения" на панель инструментов
В твоём случае это выглядит так:
Пусть для простоты первый и второй столбцы заполняются руками, а третий - формула, например, =A2+B2. Мы заменяем значениями все формулы, кроме ПЕРВОЙ строчки таблицы и ПОСЛЕДНЕЙ. В итоге вместо 9000 формул в столбце осталось только 2. Первая строчка (строчка с заголовками столбцов не в счёт, конечно) нужна для того, чтобы можно было легко посмотреть, что у нас за столбец такой, как хранилище этой самой формулы и для внесения изменений (об этом ниже). Последняя строчка нужна для удобства добавления данных вниз таблицы - если данные не добавляются, то можно только первую строчку с формулами оставлять. Итак, пришли новые 100 строк. Ты впечатываешь ручную информацию, а затем продляешь формулу из последней, 9000-й строчки на новые данные (надеюсь, ты умеешь это делать одним двойным щелчком мыши). В итоге у тебя стали формулы с 9000-й по 9100-ю строчки. Теперь с 9000-й по 9099-ю заменяешь значениями, а в 9100-й - она теперь последняя - оставляешь формулу.
Если потребовалось, например, в столбце заменить формулу на A2-B2 - меняешь её в верхней строчке, продляешь вниз, заменяешь значениями - и всё ок.
При правильной, выверенной, последовательности нажатий клавиш на клавиатуре такие операции по замене формул значениями делаются очень просто.
Что ты за это получишь:
1. Уменьшение размера файла (формулы занимают больше места)
2. Убыстрение работы.
Предлагаю осмыслить вышеописанное, а затем продолжим. :)