» Excel FAQ (часть 4)

Попробуйте подсчитать сами. Так как первый символ не учавствует в перестановке, то:
=ПЕРЕСТ(255;15)
Объем одного символа = 1 байт.

Abysssss
надо более четко ставить задачу. Как я понял.
Есть несколько файлов. В каждом файле есть номера договоров. Они могут повторятся (даже в пределах одного файла).
Задача: найти одинаковые договора в разных файлах.
Вопрос (на который ответ не ясен): сравнивать между какими именно файлами? Всеми? или сравнивать попарно?

Я напишу общий план решения
Вводная: работать придется в Excel 2007, т.к. в Excel 2003 есть предел: строк около 65000.

Часть I (Сортировка в пределах одного файла)
Цель: удалить повторы номеров договоров в пределах одного файла.
1) выделяем столбец с номерами договоров и сортируем его по возрастанию.
2) в соседнем столбце начиная со второй строки пишем формулу
=ЕСЛИ(A2=A1;1;0) и разгоняем ее на весь столбец.
3) через специальную ставку заменяем формулу на значения.
4) выделяем 2 столбца (номера договоров и значения форумулы) и сортируем по значениям формулы
5) удаляем все строки,в которых значение формулы 1.
6) остались только те номера договоров, у которых значения в форумле было 0. Т.е. неповторяющиеся номера.

Часть II Этот же принцип можно распространить и на поиск одинаковых договоров между файлами.
Надо.
1) дать ответ на вопрос поставленный в самом начале сообщения
2) закодировать файлы. напрмер.
у всех неповторящихся номеров первого файла в соседнем столбце проставить 1
у всех неповторящихся номеров второго файла в соседнем столбце проставить 2
.....
сортировка, формула, втавка значений ...
Сам справишься? или расписывать?

Mushroomer
Ответ: Один файл нужно сравнить с девятью оставшимися. Договора могут повторятся как по файлам, так и внутри одного файла.
Про поиск одинакового номера договора в одном файле (Часть I Вашего описания - понятно), но вот с Частью II - у меня возникли проблемы с пониманием. Не могли бы Вы, если у Вас будет время, расписать подробнее? Так как именно эта Часть более всего и интересует. Заранее спасибо.

kimtan, 1.25691653551135E+31 - непосильная задача. Всем спасибо за участие.

Abysssss
Цитата:

Ответ: Один файл нужно сравнить с девятью оставшимися. Договора могут повторятся как по файлам, так и внутри одного файла.

Номер из первого файла должен быть во всех 9 остальных файлах? только такие договора интересны? Если да, то т.к. повторов договоров в пределах одного файла уже нет, то чтобы нашелся такой договор, то в отсортированной общей таблице должна быть следующая конструкция (2 столбца)
Номер договора | номер файла
111-11 1
111-11 2
111-11 3
111-11 4
111-11 5
111-11 6
111-11 7
111-11 8
111-11 9
111-11 10
распишу отдельно
=И(A1=A2;A2=A3;A3=A4;A4=A5;A5=A6;A6=A7;A7=A8;A8=A9;A9=A10) - это проверка на совпадение номеров договора у 10 последовательных строк
=И(B1+1=B2;B2+1=B3;B3+1=B4;B4+1=B5;B5+1=B6;B6+1=B7;B7+1=B8;B8+1=B9;B9+1=B10) это проверка что номера есть во всех 10 строках. т.к. файлах.

Итоговая формула
=ЕСЛИ(И(И(A1=A2;A2=A3;A3=A4;A4=A5;A5=A6;A6=A7;A7=A8;A8=A9;A9=A10);И(B1+1=B2;B2+1=B3;B3+1=B4;B4+1=B5;B5+1=B6;B6+1=B7;B7+1=B8;B8+1=B9;B9+1=B10));1;0)
Тебе будут интересны только 1.

Abysssss
Можно по второму варианту попробовать такое решение (без сортировок):
Приведу пример на трех книгах, если вариант подойдет, потом сами допишите.
Итак, предположим что книги называются Книга1, Книга2, Книга3. Открываем все книги (благо Excel такую возможность предоставляет). В книгах 2 и 3 выделяем диапазоны с сравниваемыми данными и присваиваем им имена (в данном случае содержащими номера договоров), делается это в 2003 - Вставка/Имя/Присвоить, в 2007 - Формулы/Определенные имена/Присвоить имя. Пусть имя во всех файлах будет "Список". Теперь переходим в книгу 1 и выбираем какой-будь не использующийся в таблице столбец, например L, теперь в ячейке L2 (при условии, что ваши номера договоров находятся в диапазоне A2: и ниже) вводим формулу массива: =И(ИЛИ(Книга2.xls!Список=A2)*1;ИЛИ(Книга3.xls!Список=A2)*1) заканчиваем введение сочетанием клавиш Ctrl+Shift+Enter. Теперь можно закрывать книги 2 и 3, наблюдаем за преображениями формулы. Формулу растягиваем вниз до конца диапазона. Столбец заполнился значениями ИСТИНА и ЛОЖЬ, нас интересует ИСТИНА, которая означает что данный номер договора присутствует во всех книгах. Теперь используя этот столбец можно применять условное форматирование или фильтрацию. Можно также скрыть этот столбец. Удачи!

ломаю голову правда не очень интенсивно по поводу визуализации списков
пример скрин
список 1 - ежемесячный список из скажем сотни наименований, прибавляются элементы тоже
список 2 - скажем 10 наименований и периодически к нему добавляются элементы

список 1 находится с каждым месяцем на своем листе, список 2 ведется тоже на отдельном но в единственном экземпляре

задача в списке 1 выделить желтым то что есть в списке 2

с помощью впр сделал колонку с банальным 1 и 0 напротив нужного элемента но условное форматирование ОТКАЗЫВАЕТСЯ работать т.к. говорит не может рассчитывать формулы с других листов (не дословно но суть такая)

подскажите как лучше сделать, макросом не хотел бы, хочу найти решение стандартное, чуствую что есть
excel 2007

strat, не понял, зачем Вам понадобился дополнительный столбец...
идём на тот лист, где есть данные (Ваш "Список 2")
главное меню: "Вставить" - "Имя" - "Присвоить" задаём любое имя (например SPISOK2)
потом идём в условное форматирование и задаём
формула (пример для ячейки A1):
=ЕСЛИ(ЕНД(ВПР(A1;SPISOK2;1;ЛОЖЬ));0;1)

копируем кнопочкой "формат по образцу" данное форматирование на весь столбец (или на нужную область).
всё.

Есть отчет в Excel. В нем есть колонка Дата и Количество. Строки выглядят примерно так:
1 28.04.09 5 000
2 30.04.09 6 250

Задача вставить строку:

29.04.09 5 000,

т.е. вставить пропущенную дату и значения (число или текст) из предыдущей строки.

Кто знает как?

десятьзаповедей, ну, например через макрос на VBA... (спрашивать лучше здесь
Excel VBA

ltcznmpfgjdtltq
Наверняка кто-нибудь знает, но по моему скромному мнению, приложить свой пример с задачкой, дабы никого не утруждать, было бы хорошим тоном. Ну да ладно - держите, один из многих вариантов решения: http://slil.ru/27648356

ltcznmpfgjdtltq
а в чём сложность-то? Встаёшь курсором на 30-е, вставляешь строку выше. Маркером автозаполнения продолжаешь дату с 28-го на 29-е, и копируешь количество с 28-го

ltcznmpfgjdtltq,
в примере kimtan
E1=ВПР(C1;$A$1:$B$7;2;1)
и тянем вниз

Сводная таблица делается по столбцу с числовыми данными и пропусками для подсчёта частоты вхождения значений. Значение (пусто) в таблице отражается, но количество вхождений не прописывается. Можно ли как-то настроить сводную таблицу, чтобы количество пустых ячеек тоже подсчитывалось? [more=Пример]
Числа

sadmn
пропуски надо чем-то заполнить, пробелами к примеру. И обновить сводную таблицу

crotoff
не могу, т.к. также считаю пустые ячейки для других расчётов

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

Добавлено:
http://gettyfile.ru/303502/

Здравствуйте! У меня такой вопрос. Есть некий диапазон из 100 рандомных значений. Мне необходимо посчитать сколько ячеек содержат значения от 0 до 1, сколько от 1 до 2, сколько от 2 до 3 и т.д. Как это реализовать? Какую функцию нужно использовать и каким образом?

Miriale
Цитата:

Есть некий диапазон из 100 рандомных значений

Из какого диапазона берется рандом? От ответа на этот вопрос будет зависеть итоговая формула.

Miriale
Цитата:

и т.д.

Это как это понять? Сколько таких будет интервалов? Это должно быть конечное число, а не и т.д.

рандом берется с помощью функции СЛЧИС. Правда я уже сама придумала как сделать, спасибо!

Miriale
Цитата:

рандом берется с помощью функции СЛЧИС.

Я не спрашивал с помощью чего берется рандом. функция СЛЧИС даст тебе числа только в диапазоне 0-1. А у тебя
Цитата:

Мне необходимо посчитать сколько ячеек содержат значения от 0 до 1, сколько от 1 до 2, сколько от 2 до 3

Цитата:

равда я уже сама придумала как сделать, спасибо

ну и отлично. Самому решить задачу всегда приятнее.

Вопрос следующий, есть огромная таблица в 2007 excel-e из этой таблицы, мне нужно получить, сколько ячеек имеет, заданное значение... (например, мне нужно узнать сколько ячеек из огромной таблицы содержит цифру 5). Эм... надеюсь понятно выразился. Как это сделать...

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

P.S.: я так понимаю СЧЁТЕСЛИМН - это? Или я не прав, попробовал, вроде похоже на правду. Хотелось бы чтоб подсказали, а то малоли...

TeeHa1F
задаёшь автофильтр по нужному полю с условием "содержит" а внизу функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;{отфильтрованный диапазон})

Добавлено:
счетесли канает если ячейка целиком содержит то что ты ищешь, а если помимо "5" там ещё символы?

Добавлено:
TeeHa1F
приврал тебе слегка насчёт СЧЁТЕСЛИМН, спутал с СЧЁТЕСЛИ из 2003-го экзеля
вобщем можешь задать в условии со звёздочками "*5*", но тогда будет искать только текстовые значения, содержащие 5, а числовые значения игнорирует. И наоборот

На обед решил накидать будущий шкаф-купе. Под рукой оказался только Excel)
Стормозив, начал рисовать его под углом - http://www.rapidshare.ru/1043173
сейчас вопрос - можно ли все ячейки повернуть на 90 градусов?

crotoff
Всё понял, спасибо огромное (за быстрый ответ и разъяснения), а то тут анализ данных делаю...

TeeHa1F
Цитата:

сколько ячеек имеет, заданное значение...

Цитата:

Например, мне нужно узнать сколько ячеек из огромной таблицы содержит цифру 5)

Это вообще-то разные задачи.

Mushroomer
Эм, значит не правильно выразился, хотя...

Ну допустим, заданное значение (это цифра 5), я ищу ячейки, в которых есть только эта цифра (в яцейках только цифры от 0 до 10). Дальше конечно таблицы сложнее( с текстом и цифрами), но впринципе что и как я понял, а там, по нарастающей, надеюсь разберусь. Если что черкну...

P.S.: Mushroomer - а ты как понял, написанное выше мною? (черкни кратко) мало ли

TeeHa1F
Цитата:

а ты как понял, написанное выше мною

Ну так было написано,
Цитата:

Например, мне нужно узнать сколько ячеек из огромной таблицы содержит цифру 5)

т.е. это кокретный пример. то я понял, что это то, что ты хочешь сделать.
Я не умею работать с формулами массива, а в топике были мастера в этом. И я не знаю формулы Excel 2007, поэтому я бы сделал по-старинке.
=ЕСЛИ(ЕОШИБКА(ПОИСК(5;A1;1))=ИСТИНА;0;1)
Ну а потом просто подсчитал сумму.
Эта формула для одного столбца.

TeeHa1F
=СУММПРОИЗВ(--НЕ(ЕОШ(НАЙТИ(5;диапазон))))