» Excel FAQ (часть 3)

Вот действительно задача, во первых "3" может появится в каждой ячейке i-раз (через символ например) - соответственно каждый такой раз надо проверить стоят ли рядом другие цифры (по одной позиции с лева и с права). К тому-же у нас N таких колонн. Как бы не проблема если просто проверить наличие тройки в ячейке (или во всей строке), однако в этом случае придётся тупо отсеивать все комбинации тройки если она стоит рядом с другими цифрами. Да ещё сколько раз.. Если-бы она была в ячейке только один или пару раз - то ерунда а так... никаких идей к сожалению (хотя может функций мало знаю).

lomaxx
Ваша проблема в том, что не правильно выбран инструмент для решения этой задачи.
Эта задача легко решается с помощью регулярных выражений, напр. в PERL.

lomaxx
Я перенес формат из своего 1-го файла и все получилось.
http://slil.ru/25471891
Формула получилась очень заковыристая
Но если сделать пользовательскую функцию то все будет проще
Vitus_Bering прав насчёт инструмента для решения этой задачи,
но мы же любим решать сложные задачи в EXCEL

ZORRO2005
Но ведь задача не решена, т. к. lomaxx пишет:

Цитата:

нужно чтобы в диапазоне B5:B9 стояли "значения" наличия числа 3

Цитата:

нужно чтобы в диапазоне B5:B9 стояли "значения" наличия числа 3

А поясните, что в итоге должно стоять в ячейках этого диапазона?
Не совсем понятно.

SergD1973
Я думаю изготавливается
каждая деталь в 3 цехе или нет.

Кстати SergD1973
Ваша формула:

Код: ЕСЛИ(НЕ(ИЛИ(ЕОШ(НАЙТИ("+";$V10));ЕОШ(НАЙТИ(H$9;$V10))));ЗНАЧЕН(ПРАВСИМВ($V10;1));0)

lomaxx
В ячейки B5:B9 пишем формулу:
=ИЛИ(СЧЁТЕСЛИ(C5:K5;"3?"); СЧЁТЕСЛИ(C5:K5;"3")) и т.д., с условиями поработайте сами, тогда если ИСТИНА, то в данной строке есть детали, изготовленные в цехе №3.

lomaxx

Цитата:

Судя по всему я не совсем ясно выразил свою мысль. Мне нужно несколько иное...

Могу предложить такой способ:
создается один служебный столбик, где вы объединяете через пробел все данные по цехам. Для этого выгодно использовать функцию, которую мне присылали на мой вопрос http://forum.ru-board.com/topic.cgi?forum=5&topic=23470&start=1100#18
Только функцию немного доработать, чтобы пробел между данными вставлялся.

Затем в полученном столбце нужно будет искать цифру 3 и проверять соседние символы, чтобы не были цифрой (чуть выше такой пример уже был).

Vitus_Bering
СЧЁТЕСЛИ(C5:K5;"3?") -не работает
а
СЧЁТЕСЛИ(C5:K5;"3")-работает

Вопрос по-прежнему открыт.
Возможно ли сделать так, чтобы при вставке строки формат и содержимое (или только формат) верхней строки копировался во вставляемую строку?
Или же это решается только в VBA?

ZORRO2005

Цитата:

СЧЁТЕСЛИ(C5:K5;"3?")-не работает

СЧЁТЕСЛИ(C5:K5;"3?*")

Bredun
Без VBA вроде-как нельзя, за исключением нескольких простейших примеров когда вставка ведётся в ручную и построчно (поколонно) - т.е. срабатывает автоформатирование, но работает только для одной соседней строки (столбца). Так можно по одной заполнять.
Другое дело принудительный формат колонны через Format->Cells->Number->Custom или Format->Conditional Formatting

Поставил Excel 2007 все супер (SP1 стоит), но когда делаю автофильтр, а потом удаляю выделенные им строки, то удаляются не только отфильтрованные строки но и те что скрылись при фильтровании. В Excel 2003 такого не замечал. Кто знает где тут Гейтс порылся?

Доброго времени суток
Следующий ворос:
Есть два столбца
Текст1 СуммаОбщ1
подтекст1 подсумма1
подтекст2 подсумма2
Текст2 СуммаОбщ2
подтекст3 подсумма3
подтекст4 подсумма4
Текст1 СуммаОбщ3
подтекст5 подсумма5
подтекст6 подсумма6


МОжно ли сделать суммуесли которая бы додавала по признаку Текст значения Второй подсуммы. Например, чтобы суммировало следующие значения По критерию "Текст1" -
подсумма2 и подсумма6?

Спасибо

gvserg1
http://slil.ru/25473958

ZORRO2005,
вы немного видоизменили начальное условие. Нужно было сделать формулой, так как массивы более чем по 50,000 строк сводились по суммам (и таких значений как Текст1...2 ооочень много.
Но все равно спасибо за помощь ))

А вот как все просто решилось... (лист sheet1)


http://slil.ru/25474053

Спасибо всем за советы! Завтра на работе опробую...

SergD1973

Цитата:

А поясните, что в итоге должно стоять в ячейках этого диапазона? Не совсем понятно

Как правильно написал ZORRO2005 мне нужно указать там
Цитата:

изготавливается каждая деталь в 3 цехе или нет.

lomaxx
http://slil.ru/25475518
Не получилось у меня без дополнительных столбцов,
если недостаточно условного форматирования.

Vitus_Bering
Воспользовался вашей формулой, только слегка подправил её и привел к виду:
=ИЛИ(СЧЁТЕСЛИ(C2:Y2;"3"); СЧЁТЕСЛИ(C2:Y2;"3 *"); СЧЁТЕСЛИ(C2:Y2;"3(*"))
только есть одна загвоздочка – а если будет слитное написание числа с буквой? Например: 3л или 3kasto
Не могу сообразить, что надо еще добавить (ПСТР – здесь не поможет?) в формулу (и как?).

SergD1973
Думаю, что объединение через пробелы возможно мне поможет, но пока хочу попробовать решить проблему с помощью вышеуказанной формулы…

ZORRO2005
Я как бы только начинаю осваивать Excel, поэтому мне несколько сложно понять некоторые моменты, в частности я практически ничего не понял в вашем последнем файле. Но ваше условное форматирование, с выделением ячеек мне еще в тот раз понравилось, хотя для меня оно в данный момент и не подходит. Но чувствую где-нибудь оно мне скоро пригодиться…

Цитата:

=ИЛИ(СЧЁТЕСЛИ(C2:Y2;"3"); СЧЁТЕСЛИ(C2:Y2;"3 *"); СЧЁТЕСЛИ(C2:Y2;"3(*"))

А ненужные значения можно исключить тупым перебором ненужных комбинаций например AND NOT ("*30*"v"*31*"v"*32*"v....v"*39*"v....v"*03*"v"*13*"v"*23*"v...v"*93*")

Вот для случая с одной ячейкой:

Код: =AND(COUNTIF(B3;"*3*");NOT(COUNTIF(B3;"*30*"));NOT(COUNTIF(B3;"*31*"));NOT(COUNTIF(B3;"*32*"));NOT(COUNTIF(B3;"*33*"));NOT(COUNTIF(B3;"*34*"));NOT(COUNTIF(B3;"*35*"));NOT(COUNTIF(B3;"*36*"));NOT(COUNTIF(B3;"*37*"));NOT(COUNTIF(B3;"*38*"));NOT(COUNTIF(B3;"*39*"));NOT(COUNTIF(B3;"*03*"));NOT(COUNTIF(B3;"*13*"));NOT(COUNTIF(B3;"*23*"));NOT(COUNTIF(B3;"*43*"));NOT(COUNTIF(B3;"*53*"));NOT(COUNTIF(B3;"*63*"));NOT(COUNTIF(B3;"*73*"));NOT(COUNTIF(B3;"*83*"));NOT(COUNTIF(B3;"*93*")))

lomaxx А Вам не кажется, что нужно изменить правила заполнения этой таблицы? В административном порядке обязать при вводе в таблицу номер цеха заполнять по определённым правилам!!! (например, номер цеха должен быть ВСЕГДА в начале и чем нибудь отделён (например дефисом))... Иначе получается, что из плохо организованного ввода приходится городить целый огород сложных, громоздких, малоэффективных и, главное, не 100% работающих формул/функций/макросов... Ведь, по сути, решается задача ИИ - на распознование текста! Да и, честно говоря, меня поражает сам подход к заполнению таблицы - что так за "чудесная" информация?? И какие варианты могут быть? вот, например (текст придуман!), "3.0KASTO" - это в 3м цехе сделано?
нет, конечно, я понимаю, народу, который здесь собрался, интересно решить интересную головоломку ;-) Но моё личное мнение - менять нужно подход к заполнению таблицы!!!
N.B.
Хотя, если таблица уже есть, заполнена и дальнейшее ёё изменение не предполагается, а какую обработку можно произвести. Но и тогда я бы рекомендовал добавить столбец(столбцы) в которых был бы отдельно номер Вашего цеха №3
Удачи.

vikkiv а для дробных чисел что будет?..

vikkiv

Цитата:

Код:=AND(COUNTIF(B3;"*3*");NOT(COUNTIF(B3;"*30*"));NOT(COUNTIF(B3;"*31*"));NOT(COUNTIF(B3;"*32*"));NOT(COUNTIF(B3;"*33*"));NOT(COUNTIF(B3;"*34*"));NOT(COUNTIF(B3;"*35*"));NOT(COUNTIF(B3;"*36*"));NOT(COUNTIF(B3;"*37*"));NOT(COUNTIF(B3;"*38*"));NOT(COUNTIF(B3;"*39*"));NOT(COUNTIF(B3;"*03*"));NOT(COUNTIF(B3;"*13*"));NOT(COUNTIF(B3;"*23*"));NOT(COUNTIF(B3;"*43*"));NOT(COUNTIF(B3;"*53*"));NOT(COUNTIF(B3;"*63*"));NOT(COUNTIF(B3;"*73*"));NOT(COUNTIF(B3;"*83*"));NOT(COUNTIF(B3;"*93*")))

Да уж... обалдеть. Не думал я, что так всё будет ужасно. Представьте что у меня на работе более 30 цехов. И вот так вот перебирать все возможные варианты... В таком случае уж сделать кажеться так как предлагал мне SergD1973:

Цитата:

создается один служебный столбик, где вы объединяете через пробел все данные по цехам.

, а далее с помощью автоформата с выборкой по условию производить отсев...


Добавлено:
SERGE_BLIZNUK

Цитата:

А Вам не кажется, что нужно изменить правила заполнения этой таблицы? В административном порядке обязать при вводе в таблицу номер цеха заполнять по определённым правилам!!! (например, номер цеха должен быть ВСЕГДА в начале и чем нибудь отделён (например дефисом))...

Да, что-то навроде этого есть. Это второй и последний вид записи. Не так как у меня в примере - каждый цех стоит в отдельной ячейке, а записью:
9, 25(магн.конт.), 9, 3 ток, 15, 25, 19(обр.шл.), 25, 27, 25 --24
77, 9(обд;узк), 15(*), 25, 19(**), 15(тр.,аз.), 25, 27, 25 --24
17(р.), 15, 19, 15ион.аз., 19, 20(*), 19, 20(**), 19, 27, 19 --24
Чем это поможет?


Цитата:

В административном порядке обязать при вводе в таблицу номер цеха заполнять по определённым правилам!!!

Тоже никак не выйдет. Т.к. есть определенная программа, купленная у белоруссов для ведения производства и т.п. Работают с ней уже лет 5. Соответственно
1. Никто ничего переделывать не будет, да и нет прав на изменение программного кода
2. ВСЕ уже привыкли работать в данном формате

Также, что хотел указать, вдруг кому понадобиться...:
3лит - 3 цех литьё
3 шл. - 3 цех шлифовка
3 kasto - обработка на станке "kasto"
и так далее...
Указанный порядок цехов обозначает маршрут обработки детали. Каждый цех выполняет свою работу и в зависимости от этого он и стоит на указанной позиции (от технологического процесса на обработку).

P.S. дробных чисел не будет.

lomaxx

Цитата:

а если будет слитное написание числа с буквой?

Тоже бился с этим вариантом, но, думаю, с данной формулой это не решаемо.
Если бы в Excel были активно задействованы регулярные выражения, то эта задача решалась бы очень легко.
Совершенно прав уважаемый SERGE_BLIZNUK, надо правильно заполнять таблицу, а иначе сами создаёте себе трудности и героически их преодолеваете...

Друзья! Напишите,пожалуйста,для lomaxx несложную функцию на VBA, которая сначала объединит все ячейки в одну (эта функция уже есть и успешно работает http://slil.ru/25459141, только нужно добавить пробел между объединяемыми ячейками) а потом полученное значение пробежит от первого символа до последнего и если встречается цифра "3" проверит, если перед тройкой и после неё не стоит другая цифра, значит данная функция возвращает ИСТИНА. Если такой отдельно стоящей тройки нет, значит ЛОЖЬ.
А хотя зачем городить огород и объединять На VBA можно и каждую ячейку таким образом проверить

Как видно из его объяснения, он не может повлиять на формат ввода данных.
Я бы сам написал, но на VBA не силен. Могу только имеющийся пример немного исправлять.

SergD1973
Цитата:

Друзья! Напишите,пожалуйста,для lomaxx несложную функцию на VBA

VBA пишется в своем топике

Цитата:

P.S. дробных чисел не будет.

Цитата:

...в которой построчно(в заданном несортированном диапазоне) нужно каким-то образом найти и показать в ней (строке) наличие конкретного числа...

В таком случае, а разве задача уже не решена ? Этим:


Код: =AND(COUNTIF(B3;"*3*");NOT(COUNTIF(B3;"*30*"));NOT(COUNTIF(B3;"*31*"));NOT(COUNTIF(B3;"*32*"));NOT(COUNTIF(B3;"*33*"));NOT(COUNTIF(B3;"*34*"));NOT(COUNTIF(B3;"*35*"));NOT(COUNTIF(B3;"*36*"));NOT(COUNTIF(B3;"*37*"));NOT(COUNTIF(B3;"*38*"));NOT(COUNTIF(B3;"*39*"));NOT(COUNTIF(B3;"*03*"));NOT(COUNTIF(B3;"*13*"));NOT(COUNTIF(B3;"*23*"));NOT(COUNTIF(B3;"*43*"));NOT(COUNTIF(B3;"*53*"));NOT(COUNTIF(B3;"*63*"));NOT(COUNTIF(B3;"*73*"));NOT(COUNTIF(B3;"*83*"));NOT(COUNTIF(B3;"*93*")))

ВСЕМ кто обсуждает задачу lomaxx
Выложил ход своих мыслей в файле:
http://slil.ru/25481276

SergD1973

Цитата:

Затем в полученном столбце нужно будет искать цифру 3 и проверять соседние символы, чтобы не были цифрой (чуть выше такой пример уже был).

Я также мыслил


Цитата:

напишите,пожалуйста,для lomaxx несложную функцию на VBA, которая сначала объединит все ячейки в одну (эта функция уже есть и успешно работает http://slil.ru/25459141, только нужно добавить пробел между объединяемыми ячейками)

Возьмите здесь:
http://slil.ru/25481245


Цитата:

а потом полученное значение пробежит от первого символа до последнего и если встречается цифра "3" проверит, если перед тройкой и после неё не стоит другая цифра, значит данная функция возвращает ИСТИНА

А вот здесь проблема!
если цех 73 будет стоять раньше 3 получим ЛОЖЬ.

Цитата:

А вот здесь проблема!
если цех 73 будет стоять раньше 3 получим ЛОЖЬ.

Ну не надо так буквально. В таких случаях обегать до конца "суммированной строки" и ЛОЖЬ возвращать только при достижении конца строки.

lomaxx
Доделал:
http://slil.ru/25483619
Можете даже выбирать номер нужного цеха.

Подскажите, пожалуйста как вставить в последний столбец формулы по аналогии с ячейкой Е1?
Заранее благодарен.

Пример