» Excel FAQ (часть 4)

ghosty

Цитата:

слова расположены в разном порядке

Если возможно, то имеет смысл их отсортировать. При этом решение задачи становится очевидным.

q1wed
Спасибо за помощь, вопрос решился, наши IT дали наводку на "сливалку" merge-excel))

ghosty
слова на одном листе могут повторяться?

Добавлено:
а список слов большой?
а листов много?
нашел решение если на каждом листе просуммировать строки

q1wed

Цитата:

слова на одном листе могут повторяться?

Да, могут.

Цитата:

а список слов большой?

Около 470-ти.
Цитата:

а листов много?

Два листа с одним и тем же набором слов, но порядок следования слов разный.

Цитата:

Если возможно, то имеет смысл их отсортировать.

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

В начале августа завершил первую часть автоматизации. Рабочего времени потрачено - 120 часов чистого из-за тормозов с ноутом. Файл весит 370 Мб. Ноут с оперативкой 256МБ не тянет. Потянул офисный комп с 512МБ оперативки. На входе закачиваю 20 таблиц с оборудованием- на выходе получаю отсортированную таблицу с заданной информацией без использования макросов. В сентябре приступил ко второй части автоматизации.
Информация к размышлению. Поставлена такая задача. Даны 2 массива состоящих из N и M "слов" соответственно. "Слово"- выборка R ( количество "букв" R в "словах" не превышает P)элементов из базового набора S элементов (формат элементов задан в ТЗ: пробелы, буквы, цифры, знаки). Базовый набор S элементов отсортирован по определенному логическому признаку ( алфавиту, величине, графике и т.д.), т.е.каждый элемент имеет свой порядковый номер. В 2 массивах встречаются одинаковые "слова". Требуется вывести 2 группы "слов" из 2 массивов, удовлетворяющих условиям ограничения. "Слова" в каждой группе должны быть упорядочены аналогично последовательности S элементов базового набора ( типа слов в словаре по алфавиту).
От макроса сортирующего слова по алфавиту я отказался (условие полной автоматизации: кнопка нажимается один раз для вывода всех результатов).
Самое первое решение I уровень: прямое - каждый элемент уже имеет свой ранг-порядковый номер в базовом наборе. Осталось все "слова" привести к общей длине, добавив нулевые элементы, а далее сравнивать по 2 "слова", порядковые номера: первые "буквы", вторые "буквы" и т.д. до "букв" с превышением номера или с последним номером в "слове", если они равны. В результате сравнения- вывод о взаиморасположении 2 "слов"- до или после. Недостаток: большое количество комбинаций сравнения.
Второе решение II уровня сложности. Как известно любое натуральное число представимо записью из последовательности цифр для различных систем исчисления по основанию 2, 3, 10 и т.д.( двоичная, троичная, десятиричная). Цифры каждого разряда для систем с разными основаниями не превышают его ( для двоичной-0,1; для десятиричной- 0,1,2,3...9 и т.д.). Цифры расчитываются по алгоритму остатка от деления на основание системы. Причем среди 2 чисел большим по величине будет число с большим старшим разрядом. Величина числа не превосходит К ( основание системы) в степени L, где L- (разрядность числа +1).
Таким образом для S элементов базового набора выбираем основание- S+1. Результат: каждое "слово" в массиве можно записать одним числом по основанию S+1. Достоинство: одному "слову" соответствует одно число. Недостаток: огромная величина числа, например для базового набора из 33 букв и 10 цифр: основание 33+10+1=44. Если "слово" имеет 12 "букв"( разряды- 0,1,2...11) , то имеем 44 в степени 11 или в десятичной системе: ~ 10 в 18 степени или число с восемнадцатью нулями, количество слов в 2 массивах- N+M, возможно и не скажется на времени обработки результатов компа.
Существует и решение III уровня сложности (приводить здесь не буду ввиду ограниченности длины сообщения). Вкратце- это инженерная задача оптимизации. Уменьшается основание системы до минимального 2, вместе с тем увеличивается количество чисел определяющих "слово" до минимально возможного ( для моего базового набора) до 6. Достоинство: относительно небольшие числа.Недостаток: увеличение числа комбинаций сравнения.

ghosty
Если делать без макросов - вижу такой вариант:
Отсортировать строки с одинаковыми названия в каждом листе. Выделить каждый вид и получить ячейку "СРЗНАЧ" по каждому виду.
Потом в третем листе в нужной ячейке написать формулу типа =СРЗНАЧ(A1лист1+A1лист2),
где A1лист1 - ячейка с резульатом среднеого значения по конкретному слову.
Потом отсортировать свои строки обратно, при єтом сохранив ссылку срзнач на нужные строки.
Правда, как "заставить" результат бегать за строками его составляющих - не в курсе, но думаю - это просто, поспрашивайте спецов.

Коллеги, подскажите пожалуйста, как реализовать:

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

Делаю через Данные -> Проверка -> Список

В качестве источника Именованный диапазон, т.е. =Имя_диапазаона.

Проблема в том, что для множества файлов необходимо сделать один источник.

А функция "Проверка" не позволяет в качестве источника указать Именованный диапазон из другого файла.

Как это можно обойти?

Может можно, как вариант, как-то "подключить" лист из другой книги, по типу Связанных таблиц в Access?

Или еще есть какие-то способы?

Как найти 3 максимальных значения из строки данных?

van23
=НАИБОЛЬШИЙ(F4:F9;1)
=НАИБОЛЬШИЙ(F4:F9;2)
=НАИБОЛЬШИЙ(F4:F9;3)

dmention
Как вариант: Скопировать(или через формулу "=")Значения из другой книги в какой нибудь скрытый лист, задать те же имена диапазонов и черпать из него.
Конечно, если книг безумное множество, придеться повозиться...

ghosty

Если количество цифр известно и размеры диапазонов одинаковы, то:

=(СУММПРОИЗВ(СУММ((Лист1!$B$2:$B$5=B2)*(Лист1!$C$2:$F$5)))+СУММПРОИЗВ(СУММ((Лист2!$B$2:$B$5=B2)*(Лист2!$C$2:$F$5))))/8

в $B$2:$B$5 листа Лист1 и Лист2 находятся кошки, собаки, белки и пр.
в $C$2:$F$5 листа Лист1 и Лист2 - цифры

/8 - заменить на любое нужное количество цифр


Добавлено:
в B2, соответственно, "Кошка" и т.д.

Добавлено:
в B2 и ниже (чтобы тянуть)

Цитата:

Есть два листа. На каждом листе набор слов и неких численных значений для этих слов. Наборы слов одинаковы, но слова расположены в разном порядке:

Лист1:
Собака 1 2 4 2 6 7
Кошка 2 3 4 5 6 7
Корова 3 4 5 6 7 4
Белка 4 5 6 7 4 3
и т.п.

Лист2
Кошка 2 3 4 5 3 2
Белка 4 5 6 3 3 2
Корова 7 5 4 3 4 5
Собака 3 4 5 6 7 4
и т.п.

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

ghosty, я ведь, в принципе, занимаюсь похожей по структуре задачей.
Таблица 1.(Список оборудования объединенных логическим признаком №1).
Объект 1. Оборудование А, С, G, K
Объект 4. Оборудование А, В, К, L, V
Объект 8. Оборудование B, G, K, L
и т.п.

Таблица 2.(Список оборудования объединенных логическим признаком №2).
Объект 1. Оборудование А, С, L, V
Объект 2. Оборудование В, К, V
Объект 8. Оборудование A, K, M, P
Объект 20. Оборудование С, G, L, V
и т.п.

Моя задача составить таблицу- результат объединения 2 заданных со структурой:
Объект 1. Оборудование A(т1), A(т2), С(т1), С(т2), G, K, L, V,
где A(т1) и A(т2)- суть оборудование принадлежащее к общей группе А в таблиц. 1 и 2
Объект 2. Оборудование В, К, V
Объект 4. Оборудование А, В, К, L, V
Объект 8. Оборудование A, B, G, K(т1), K(т2), L, M, P
Объект 20. Оборудование С, G, L, V
и т.п.

Т.е. объекты в результирующей таблице должны быть ранжированы согласно порядковому номеру в базового наборе, а также: группы и оборудование в каждой группе. Похожа на задачу расположения элементов массива в порядке возрастания
(убывания). Задача действительно достаточно сложная, ссылаясь на собственный опыт решения с использованием фильтрации и ограничения с количеством таблиц
больших 1.
Вынужден был заняться полной автоматизацией- надоело копировать из диагностической программы таблицы, форматировать в Word и тратить на один отчет по объекту от 1 до 2 часов, в зависимости от количества оборудования, тем более, что количество объектов только увеличивается.

Добрый день, Господа. Подскажите, как в макросе записать примерно такое:
Если А1 = "слово", к цифре в А2 прибавляется 1. Спасибо

Сделал временный вариант:
Например, надо увеличить на 1 цифру в ячейке A1. Создаю макрос: Значение A1 копировать в B1. В A1 пишу формулу B1+1. Тогда при выполнении макроса получаю результат.
Но мне этот вариант кажеться немного безумным)). подскажите, если знаете лучший способ.

Pravoved90

А нельзя чтоли в макросе сразу написать А1 = А1 + 1 ? Или через переменную, зачем через B1 ?


Добавлено:
Pravoved90

Цитата:

Как вариант: Скопировать(или через формулу "=")Значения из другой книги в какой нибудь скрытый лист, задать те же имена диапазонов и черпать из него.
Конечно, если книг безумное множество, придеться повозиться...

1. Как скрыть лист?

2. Файлов порядка 80.

Добрый день.

В ячейках A1:B1, A2:B2, A3:B3 и т. д. указаны диапазоны. Также на листе есть таблица с числами. Числа, попадающие в любой из диапазонов, нужно выделить цветом. Какую формулу для этого нужно вписать в «условном форматировании»? См. скриншот.

Вариант «значение между A1 и В1, а также значение между A2 и В2» не подходит: диапазонов несколько десятков, а условий для форматирования только три.

dmention
Выпадающий список с данными из другого файла

Цитата:

А нельзя чтоли в макросе сразу написать А1 = А1 + 1

Я так и хотел вначале сделать - пишет: "циклическое действо". Может, конечно, есть способ это записать иначе, но у меня так..
Жду ваших предложений..

GMM
Пишите макрос, и ставьте туда диапозонов хоть миллион. По вопросам написания обращайтесь суда http://forum.ru-board.com/topic.cgi?forum=33&topic=8273&start=2460

GMM

этот макрос строго завязан на размерах/адресах исходных диапазонов, но при небольшом изменении макрос сам будет определять, где заканчиваются диапазоны

Добавлено:
или можно самому задать размеры диапазонов если динамическое определение границ не столь важно

Добавлено:
ghosty
вот составил формулку для тебя:

Код: =ЕСЛИ(ЕОШИБКА(СРЗНАЧ(ЕСЛИ(ЕОШИБКА(СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5));СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5);СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5));ЕСЛИ(ЕОШИБКА(СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5));СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5);СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5))));0;СРЗНАЧ(ЕСЛИ(ЕОШИБКА(СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5));СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5);СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5));ЕСЛИ(ЕОШИБКА(СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5));СРЗНАЧЕСЛИ(Лист1!$A$2:$A$5;Лист3!A2;Лист1!$H$2:$H$5);СРЗНАЧЕСЛИ(Лист2!$A$2:$A$5;Лист3!A2;Лист2!$H$2:$H$5))))

Помогите пожалуйста с такой проблемой - есть порядка 1000 ячеек с числами от -130 до 1000, нужно получить отношение суммы положительных чисел к сумме отрицательных, я только не могу понять как это в эксель посчитать...

Подскажите, как сделать большое количество копий листов. У меня в Excel набран текст с таблицей, по формуле он автоматически заполняет нужные ячейки(которые меняются, все остальное остается неизменным) и чтобы не печатать один акт на другом, мне нужно сохранять каждый на отдельном листе, причем листов этих много( больше 100). Как мне быстрее создать копии этих листов(одинаковых )?
Вручную делать очень долго.

40rt
Это просто
Допустим Ваши числа в диапазоне B2:B1001
формула, которая считает то, что Вам надо
=СУММЕСЛИ(B2:B1001;"<0";B2:B1001)/СУММЕСЛИ(B2:B1001;">0";B2:B1001)

первая часть формулы СУММЕСЛИ(B2:B1001;"<0";B2:B1001) суммирует все отрицательные числа, а вторая, соответственно все положительные, ну, знак деления ("/"), я думаю, знают все )


Добавлено:
Afalina10
Вариант выделить все листы и вставить их копии не подходит?
Кликните на любом листе правой кнопкой мыши, выберите из контекстного меню "Выделить все листы", потом снова пкм - "Переместить/скопировать", потом укажите перед каким листом вставлять, поставьте птичку в "Создавать копию" и нажмите "Ок"

40rt
если числа в диапазоне A1 - A1000
сумма положительных: =СУММЕСЛИ(A1:A1000;">0")
сумма отрицательных: =СУММЕСЛИ(A1:A1000;"<0")
отношение сами напишете? ;-))

---

Afalina10
а чуть поподробнее - есть один лист, нужно его скопировать на 100 листов?

Извините за ламерский вопрос. Туплю. Делаю калькулятор.
Как в Excel создать ячейку со стрелочкой-треугольничком и ниспадающем меню выбора значения. То есть чтобы пользующийся калькулятором менеджер (пользователь) мог бы выбрать только одно из заданых значений. Для примера: 100, 200, 300. Как? Видел во многих документах такое, а как это делается не могу найти... И как это называется заодно скажите, чтобы я справочник больше не насиловал

Добавлено:
И есть подскажете совместима ли будет эта функция с OpenOffice буду рад. Потому как не уверен чем именно будут открывать файл потом.

hyppopotam
http://www.planetaexcel.ru/tip.php?aid=34&PHPSESSID=0ecb754cc9ad2c85470a7c3c85c0347b

hyppopotam
Цитата:

Как в Excel создать ячейку со стрелочкой-треугольничком и ниспадающем меню выбора значения.

Меню Данные -> Проверка -> Параметры -> Тип данных = Список.
Нажми в этом диалоге F1 (помощь) там можно почитать об этом: Проверка вводимых в ячейку данных -> Выбор вводимых данных из списка

Mushroomer Спасибо.

Bredun Спасибо. Хороший сайт, не знал. Сохранил в Избранном.

Pravoved90
Я думал, это можно сделать встроенными функциями Excel.

q1wed
О! Огромное спасибо!

AuthorR, SERGE_BLIZNUK, проблема в том, что ячейки у меня разбросаны по всему листу попарно, например:

Код:
P15:Q15;D26:E26;J37:K37;D49:E49;D59:E59;M70:N70;D81:E81;G92:H92;P103:Q103;D114:E114;G125:H125;M136:N136;D147:E147;G158:H158;P169:Q169

40rt

Цитата:

ячейки у меня разбросаны по всему листу

Вставка - Имя - Присвоить
Присвой своему диапазону имя и потом используй его в формуле.
к примеру =СУММЕСЛИ(имя_диапазона;">0")