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

Вход

Регистрация

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

 

= Мир MS Excel/Вывод значений отсутствующих в заданном диапазоне. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Вывод значений отсутствующих в заданном диапазоне. (Макросы/Sub)
Вывод значений отсутствующих в заданном диапазоне.
djon2012 Дата: Среда, 21.01.2015, 13:59 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Всем форумчанам доброго дня!!!
Если не сложно помогите пожалуйста в решении небольшой проблемы. Я смог ее решить сам, но с помощью большого количетсва ячеек с формулами (прикреплю файл со своим решением ). Для меня важна скорость выполнения задачи. Возможно макросом будет быстрее или с помощью других формул.
Задача такова
1. На лист1 диапазон A1:J150 заполнен числами от 1 до 90.
2. Нужно (например) с диапазона C12:I23 вывести отсутствующие значения (значения от 1 до 90) в О2 (строку); с диапазона A12:J100 вывести отсутствующие значения в О3: с диапазона B38:F140 вывести отсутствующие значения в О4 и т.д.
Большое Спасибо!!!
К сообщению приложен файл: 2015.xlsb (35.7 Kb)
 
Ответить
СообщениеВсем форумчанам доброго дня!!!
Если не сложно помогите пожалуйста в решении небольшой проблемы. Я смог ее решить сам, но с помощью большого количетсва ячеек с формулами (прикреплю файл со своим решением ). Для меня важна скорость выполнения задачи. Возможно макросом будет быстрее или с помощью других формул.
Задача такова
1. На лист1 диапазон A1:J150 заполнен числами от 1 до 90.
2. Нужно (например) с диапазона C12:I23 вывести отсутствующие значения (значения от 1 до 90) в О2 (строку); с диапазона A12:J100 вывести отсутствующие значения в О3: с диапазона B38:F140 вывести отсутствующие значения в О4 и т.д.
Большое Спасибо!!!

Автор - djon2012
Дата добавления - 21.01.2015 в 13:59
Karataev Дата: Среда, 21.01.2015, 22:01 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1334
Репутация: 533 ±
Замечаний: 0% ±

Excel
посмотрите приатаченный файл.кнопку для макроса я не делал,поэтому стартуйте из vbe
К сообщению приложен файл: Macro2015.xlsb (22.6 Kb)
 
Ответить
Сообщениепосмотрите приатаченный файл.кнопку для макроса я не делал,поэтому стартуйте из vbe

Автор - Karataev
Дата добавления - 21.01.2015 в 22:01
wild_pig Дата: Среда, 21.01.2015, 22:06 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 517
Репутация: 97 ±
Замечаний: 0% ±

2003, 2013
В примере все строки "ложь".
Нужно (например)

Почему именно эти диапазоны, кто их выбирает и как?
вывести отсутствующие значения

если в выбранном диапазоне нет какой либо цифры 1-90 её внести в строку? Т.е., если в диапазоне нет ни одной цифры 2 то строка будет пустая, кроме 2?
 
Ответить
СообщениеВ примере все строки "ложь".
Нужно (например)

Почему именно эти диапазоны, кто их выбирает и как?
вывести отсутствующие значения

если в выбранном диапазоне нет какой либо цифры 1-90 её внести в строку? Т.е., если в диапазоне нет ни одной цифры 2 то строка будет пустая, кроме 2?

Автор - wild_pig
Дата добавления - 21.01.2015 в 22:06
djon2012 Дата: Четверг, 22.01.2015, 00:44 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Вам большое Karataev!!! hands :hands: hands yes :yes: yes
Уважаемый Wild_Pig отвечаю на Ваши вопросы:
1. Да в примере все ячейки "ложь" потому что взяты большие диапазоны в которых представлены все числа и выводить нечего. В постановке задачи я предоставил другие диапазоны пункт 2. В примере я предоставил свое решение формулами.
2. По поводу диапазонов. Их выбираю я сам по нужным мне критериям.
3. Если в выбранном диапазоне присутствуют все числа от 1 до 90 то выводить пустую строку, если отсутствуют какие либо числа (5 13 45) то их надо вывести.
Спасибо!!!


Сообщение отредактировал djon2012 - Четверг, 22.01.2015, 09:25
 
Ответить
СообщениеСпасибо Вам большое Karataev!!! hands :hands: hands yes :yes: yes
Уважаемый Wild_Pig отвечаю на Ваши вопросы:
1. Да в примере все ячейки "ложь" потому что взяты большие диапазоны в которых представлены все числа и выводить нечего. В постановке задачи я предоставил другие диапазоны пункт 2. В примере я предоставил свое решение формулами.
2. По поводу диапазонов. Их выбираю я сам по нужным мне критериям.
3. Если в выбранном диапазоне присутствуют все числа от 1 до 90 то выводить пустую строку, если отсутствуют какие либо числа (5 13 45) то их надо вывести.
Спасибо!!!

Автор - djon2012
Дата добавления - 22.01.2015 в 00:44
djon2012 Дата: Четверг, 22.01.2015, 09:31 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
А МОЖНО ЛИ ВЫШЕОПИСАННУЮ ЗАДАЧУ РЕШИТЬ С ПОМОЩЬЮ ФОРМУЛ (КРОМЕ ТЕХ ФОРМУЛ ЧТО В МОЕМ ВАРИАНТЕ) ???
СПАСИБО!!!
 
Ответить
СообщениеА МОЖНО ЛИ ВЫШЕОПИСАННУЮ ЗАДАЧУ РЕШИТЬ С ПОМОЩЬЮ ФОРМУЛ (КРОМЕ ТЕХ ФОРМУЛ ЧТО В МОЕМ ВАРИАНТЕ) ???
СПАСИБО!!!

Автор - djon2012
Дата добавления - 22.01.2015 в 09:31
_Boroda_ Дата: Четверг, 22.01.2015, 09:49 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Посмотрите такой вариант
Диапазон вручную записываете в столбец N
Нолики и единички можно скрыть. Например, с помощью формата ячеек ;;;

Файл поменял
К сообщению приложен файл: 2015_3.xlsx (23.8 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Четверг, 22.01.2015, 09:53
 
Ответить
СообщениеПосмотрите такой вариант
Диапазон вручную записываете в столбец N
Нолики и единички можно скрыть. Например, с помощью формата ячеек ;;;

Файл поменял

Автор - _Boroda_
Дата добавления - 22.01.2015 в 09:49
djon2012 Дата: Четверг, 22.01.2015, 11:39 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Здравствуйте Boroda !
Ваш вариант увы не подходит так как расчет по вашей формуле идет в несколько раз дольше чем по моей
и на выходе имеем 0 и 1 а мне нжно конкретное число.
Ваша формула
Код
=--(СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(";"&Q$1&";";";"&ДВССЫЛ($N2)&";")))=0)

Моя формула
Код
=ЕСЛИ(СЧЁТЕСЛИ($C$12:$I$23;2)=0;2)

Возможно я плохо сформулировал вопрос, попробую обьяснить еще раз на примере своей формулы. Если в конкретном диапазоне (например C12:I23) есть число (например 2) то выводим ЛОЖЬ или 0, а если числа 2 нет то выводим его и так со всеми числами от 1 до 90. Моя формула работает, но возможно есть другие более быстрые и короткие формулы которые бы обрабатывали данные по вышеизложенному алгоритму еще быстрее.
Спасибо!!!


Сообщение отредактировал djon2012 - Четверг, 22.01.2015, 11:58
 
Ответить
СообщениеЗдравствуйте Boroda !
Ваш вариант увы не подходит так как расчет по вашей формуле идет в несколько раз дольше чем по моей
и на выходе имеем 0 и 1 а мне нжно конкретное число.
Ваша формула
Код
=--(СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(";"&Q$1&";";";"&ДВССЫЛ($N2)&";")))=0)

Моя формула
Код
=ЕСЛИ(СЧЁТЕСЛИ($C$12:$I$23;2)=0;2)

Возможно я плохо сформулировал вопрос, попробую обьяснить еще раз на примере своей формулы. Если в конкретном диапазоне (например C12:I23) есть число (например 2) то выводим ЛОЖЬ или 0, а если числа 2 нет то выводим его и так со всеми числами от 1 до 90. Моя формула работает, но возможно есть другие более быстрые и короткие формулы которые бы обрабатывали данные по вышеизложенному алгоритму еще быстрее.
Спасибо!!!

Автор - djon2012
Дата добавления - 22.01.2015 в 11:39
_Boroda_ Дата: Четверг, 22.01.2015, 11:50 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
расчет по вашей формуле идет в несколько раз дольше чем по моей

Конечно. Зато в вашей формуле нужно диапазоны напрямую в формулу вводить, а в моей достаточно просто написать в столбце N. Для этого формула и писалась.
Кстати, действительно, это я что-то загнул. Вполне достаточно
Код
=(СЧЁТЕСЛИ(ДВССЫЛ($N2);O$1)=0)*O$1

А ваша формула нормальная. Только диапазоны вручную набивать нужно. И число сравнения (в вашем примере - 2)
К сообщению приложен файл: 2015_5.xlsx (24.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
расчет по вашей формуле идет в несколько раз дольше чем по моей

Конечно. Зато в вашей формуле нужно диапазоны напрямую в формулу вводить, а в моей достаточно просто написать в столбце N. Для этого формула и писалась.
Кстати, действительно, это я что-то загнул. Вполне достаточно
Код
=(СЧЁТЕСЛИ(ДВССЫЛ($N2);O$1)=0)*O$1

А ваша формула нормальная. Только диапазоны вручную набивать нужно. И число сравнения (в вашем примере - 2)

Автор - _Boroda_
Дата добавления - 22.01.2015 в 11:50
djon2012 Дата: Четверг, 22.01.2015, 12:08 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Модератору: прошу прощения за свою оплошность по недооформлению формул тегами, исправлюсь. Спасибо за напоминание.
Boroda: Спасибо за другой вариант формулы, буду разбираться попробую на скорость выполнения.
 
Ответить
СообщениеМодератору: прошу прощения за свою оплошность по недооформлению формул тегами, исправлюсь. Спасибо за напоминание.
Boroda: Спасибо за другой вариант формулы, буду разбираться попробую на скорость выполнения.

Автор - djon2012
Дата добавления - 22.01.2015 в 12:08
djon2012 Дата: Четверг, 22.01.2015, 12:34 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Boroda, обработка данных по по вашей формуле идет дольше приблизительно в 1,5 раза. Я думаю это связано с ссылкой в формуле на просматриваемый диапазон и ссылкой на выводимое значение.
 
Ответить
СообщениеBoroda, обработка данных по по вашей формуле идет дольше приблизительно в 1,5 раза. Я думаю это связано с ссылкой в формуле на просматриваемый диапазон и ссылкой на выводимое значение.

Автор - djon2012
Дата добавления - 22.01.2015 в 12:34
_Boroda_ Дата: Четверг, 22.01.2015, 12:39 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Я ж писал уже
Конечно. Зато в вашей формуле нужно диапазоны напрямую в формулу вводить, а в моей достаточно просто написать в столбце N. Для этого формула и писалась.

Тормозит именно ДВССЫЛ. И это нормально. Это плата за удобство пользования. Впрочем, решать вам: хотите заводить руками диапазоны и числа 1-90, но чтобы было побыстрее - да за ради Бога. Или хотите удобства и наглядности в работе? Решать вам.


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЯ ж писал уже
Конечно. Зато в вашей формуле нужно диапазоны напрямую в формулу вводить, а в моей достаточно просто написать в столбце N. Для этого формула и писалась.

Тормозит именно ДВССЫЛ. И это нормально. Это плата за удобство пользования. Впрочем, решать вам: хотите заводить руками диапазоны и числа 1-90, но чтобы было побыстрее - да за ради Бога. Или хотите удобства и наглядности в работе? Решать вам.

Автор - _Boroda_
Дата добавления - 22.01.2015 в 12:39
djon2012 Дата: Четверг, 22.01.2015, 12:59 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 106
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Boroda, без сомнения Ваш вариант более удобен, остановлюсь таки на нем. Спасибо Вам. Удачи!!!! yes yes yes
 
Ответить
СообщениеBoroda, без сомнения Ваш вариант более удобен, остановлюсь таки на нем. Спасибо Вам. Удачи!!!! yes yes yes

Автор - djon2012
Дата добавления - 22.01.2015 в 12:59
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Вывод значений отсутствующих в заданном диапазоне. (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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