» Excel FAQ (часть 5)

kimtan, да, для разовой задачи через Word будет, наверное, эффективнее всего и наглядней, если VBA неподъемен.

А касательно формулы... Нарисуй хоть для одной строки. Пусть задумчивую :) Я вот что-то не могу сориентироваться, каким это боком можно заформулить...

Ну например так:
=СУММ(ПСТР(0&A1;НАИБОЛЬШИЙ(ЕЧИСЛО(-ПСТР(A1;СТРОКА($1:$99);1))*СТРОКА($1:$99);СТРОКА($1:$99))+1;1)*10^СТРОКА($1:$99)/10)
Формула массива

только вот нашелся готовый скрипт два дня ответ не могли найти вот кому надо если http://www.excel-vba.ru/chto-umeet-excel/kak-ostavit-v-yachejke-tolko-cifry-ili-tolko-tekst/

kimtan, вах, кшмр... Я поднимаю руки. Сдаюсь.
Мне надо время, чтобы ее переварить :)
Вообще-то, я боюсь таких... неустойчивых, что-ли :) ... конструкций.

asbo, что имеется ввиду под "неустойчивостью"?

qazaqazaqaz

Цитата:

вот нашелся готовый скрипт

Навскидку - рабочий вариант, но:
1. Вопрос с разделителями остается открытым.
2. Мне она не понравилась тем, что для определения принадлежности буква-цифра используются два разных алгоритма.
3. Это функция, она оставит исходные данные неизменными. Т.е. немного другой механизм.


Добавлено:
kimtan, отнюдь, не применительно к ее работоспособности. Это мое, сугубу личное восприятие. Громоздкость, что ли... Я всегда настораживаюсь, когда возникает много скобочных пар :) Тем более, я формулы массивов ни разу руками не трогал - не было нужды. Наверное, это мое предубеждение - решать подобные задачи средствами VBA, т.е. компактности и концентрации кода в одном месте.

там в самом файлике который пример еще програмка есть я ее в свой лист закопировал выделил колонки которые надо очистить от букв выбрал эти же колонки куда поместить результат и минуты не прошло как 800 тысяч строк очистило и все на своих местах осталось!

Добрый день!

Делаю первые шаги в Excel, подскажите, пожалуйста...
версия 2007

Столкнулся с такой проблемой при использовании более 7 "ЕСЛИ":
"Не удается ввести указанную формулу, поскольку она использует больше уровней вложенности, чем допускается текущим форматом файла."

Задача следующая:

Есть два столбца (по 20 позиций (цифры)

мне надо их сравнить с 2-мя горизонтальными строчками (только 12 значений), и при совпадении по горизонтали под этими строчками, например поставить "ок", в противном случае оставить пустоту " ".

пример составленной формулы:
=ЕСЛИ(И(sem=Месяц;sema=год);"ок";ЕСЛИ(И(shet=Месяц;sheta=год);"ок";ЕСЛИ(И(pat=Месяц;pata=год);"ок";ЕСЛИ(И(chet=Месяц;cheta=год);"ок";ЕСЛИ(И(tri=Месяц;tria=год);"ок";ЕСЛИ(И(dva=Месяц;dvaa=год);"ок";ЕСЛИ(И(odin=Месяц;odina=год);"ок";" ")))))))

формула работает как мне надо, но в ней только 7 значений сравнивает, а мне надо 20 !

Подскажите, научите ))

AlexanderVV
1. Использовать промежуточные столбцы
2. Уточнить алгоритм - весьма вероятно, что после первых проверок необходимость в последующих отпадет
3. Оба пункта - независимо друг от друга


Добавлено:
И вообще, такие вещи лучше на пальцах не объяснять, а выкладывать файл. Вероятность получения ответа, адекватного хотелкам существенно повысится.

asbo

Пример:
A(месяц) В(год)
2 2011
3 2011
5 2011
8 2011
9 2011
5 2012
8 2012
10 2012
3 2013
2 2014
1 2015

* А зависимо с В

далее строчка (тоже месяцы и года)
2011 2012 2013
1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 и т.д.
Х Х Х Х Х Х Х Х Х


задача: поставить "Х", где совпадают значения в столбцах со строчками, при этом значения столбца (А и В) переменные, то есть неизвестно сколько будет месяцев определенного года

AlexanderVV
=ЕСЛИ(СУММПРОИЗВ((месяц=A$16)*(год=A$15))=1;"ок";"")
Скачать Книга1.xls с WebFile.RU

VicKos

СПАСИБО!!!!

У меня ещё куча вопросов! Можно узнать как с Вами связаться?

AlexanderVV
Пожалуйста.
Относительно "кучи вопросов" отвечу, что я сам далеко не "гуру" в Excel, немножко нахватался на форумах, но, чем смогу, помогу.
А насчёт связаться - аськой не пользуюсь, может быть через почту? <delete>

AlexanderVV, я бы матрицу все-таки сделал, а не строку.
http://rghost.net/6878491/private/5390bd5cb8870b04cd647e8b68ddb97d

VicKos
Попутно, на этом примере, - при подобном сравнении значения с возможным из массива функция =ТИП(месяц=$E4) возвращает 16 (ошибка), как в ячейке выше, но на предпросмотре показывает как положено - 64 (массив). Не в курсе, часом? Я давно столкнулся, а сейчас вот вспомнил. Как заставить ее вернуть 64?


P.S. Что-то сегодня с движком руборда... Еле дышит...

asbo
Никогда не приходилось пользоваться этой функцией, почитал справку, но там как то очень кратко описано и пример с массивом (=ТИП({1;2;3;4})) не показывает, как это можно применить на практике.
Попробовал создать именованный массив констант "месяц" и использовать его в Вашей формуле - возвращает значение 4 (логическое), что, в общем, правильно, - проверяется равенство и результат либо ЛОЖЬ либо ИСТИНА.

VicKos
Цитата:

Попробовал создать именованный массив констант "месяц"

, я говорю применительно как раз к Вашему примеру для AlexanderVV.

У меня была аналогичная ситуация, только сама формула формировалась в коде VBA.
В данном случае выражение (месяц=$E4) возвращает массив логических соответствий каждого элемента диапазона "месяц" ячейке $E4. А Тип в конечном итоге определяет его как ошибку (16)... Сейчас уже не актуально, просто интересно :)

asbo
Начинает смутно доходить .
Насчёт VBA не знаю, я с ним очень мало знаком, а то, что СУММПРОИЗВ в процессе вычислений создаёт виртуальный массив - так ведь в конечном итоге эта функция возвращает единственное значение, которое не является массивом.
Это нужно у продвинутых пользователей спрашивать, у меня самого по Excel гораздо больше вопросов, чем ответов

Есть куча чисел в формате дд.мм.гг, нужно посчитать кол-во дат меньше заданной, например
01.01.2000
01.02.2001
04.02.2001
05.04.2002
02.06.2005
13.07.2006

ищем меньше 01.01.2003, должны получить 4

Подскажите, формулу...

Гуру Excel'я, помогите:

Стоит Microsoft Office 2003, проблема с файлами экселя.
Проблема следующая.

Открываю таблицу, что-то меняю в ней, нажимаю "крестик" (закрыть). Файл закрывается, ничего не спрашивая, изменения не сохраняет.

Создаю новую таблицу, что-то меняю, жму закрыть, появляется вопрос - СОХРАНИТЬ?, т.е. все как надо.

Т.е. где-то в уже созданных файлах кроется опция - не спрашивать о сохранение?

AlexanderVV, Вам же выше ответили с примером... Делайте по аналогии.
=СУММПРОИЗВ((E3:E8<C3)*1)
где E3:E8 - диапазон дат, а C3 - контрольная дата.

Смысл всей этой байды:
каждый элемент массива, полученного из диапазона E3:E8, сравнивается с контрольным значением. На выходе получается массив логических значений, размерностью такой же, как и E3:E8. Логическое ЛОЖЬ - это 0. ИСТИНА - это 1 (в формулах). Умножая на 1 мы производим неявное преобразование логических значений в числовые. Можно (а иногда и нужно) сделать это явно:
=СУММПРОИЗВ(Ч(E3:E8<C3))

Ф-ция СУММПРОИЗВ работает с массивами, но передаем мы в нее единственный массив. Поскольку умножать не на что, она суммирует значения внутри массива. Читайте хэлп по ней. Да и не по ней только.

Еще варианты для ячейки A17, в файле приложенном VicKos:
=ПОВТОР("ок";ИЛИ((месяц=A$16)*(год=A$15)))
Формула массива
=ПОВТОР("ок";СЧЁТЕСЛИМН(месяц;A$16;год;A$15)>0)
Обычная, но для молодых версий Excel



Добавлено:
asbo
По поводу функции ТИП():
Для того чтобы заставить ее увидеть массив, нужно и формулу вводить как формулу массива

kimtan, хе-хе :)
Тут целых два вопроса сразу:
1. Почему же на предпросмотре она его правильно видит и показывает?
2. Это фигурными скобками {} обрамить?

Да, так работает. Но, млин!, см .п.1 - кто же догадается, что надо еще что-то, если на полуходе она ужЕ добро дала? Рисовала бы сразу, что "Сам дурак!"...

Люди добры поможите...
Ситуация: Excel 2007, файл в сетевой папке. На одном компе при открытии файла не видна рабочая область. Как будто пустая оболочка EXCEL. Но при предварительном просмотре данные видно. При открытии с другого компа файл открывается нормально.
На ночь комп выключали, утром после загрузки файл опять нормально открылся.

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

surgutfred
Цитата:

На одном компе при открытии файла не видна рабочая область. Как будто пустая оболочка EXCEL. Есть мысли что это может быть?

Меню Окно -> Отобразить.

Что-то не могу сообразить с форматом чисел :(
Как сделать, чтобы положительные были с лидирующим плюсом, нули - без ничего, а отрицательные с минусом?
+5; 0; -5

asbo
[>0]+0;[<0]-0;0
В принципе условие можно опустить: +0;-0;0

kimtan, спасибо огромное.
Я по простоте души +0;0;-0 попробовал - не получилось. Расстроился :)

Добрый день, коллеги.
Вот у меня какой простой вопрос.

Есть ли возможность вставить в таблицу Excel такой объект: таблицу Excel ?

Отдельную, а не ту, что получается, вставка->таблица.
Она не подходит так так строки и столбцы совпадают со страницей.

Очень нужно.
Одно решение есть, конечно. Вставить документ Word, в который в свою очередь вставлена таблица Excel.
Может быть появится возможность вставить таблицу из OpenOffice, если его установить...
Но это, согласитесь, как-то не по джедайски...

mrtramvaev, Эксель в Эксель?
Ну, пусть так... А разве "Вставить - Объект - Из файла" не канает?

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