» Excel FAQ (часть 4)

Kavalergrad
1) Добавляем на ленту вкладку "Разработчик": Главная кнопка-Параметры Excel-Основные-Основные параметры работы с Excel-Показывать вкладку "Разработчик" на ленте/Ставим галку.
2) Размещаем наши номера от 1 до 7 к примеру в диапазоне A1:A7, а фотографии в B1:B7 на Лист1.
3) Создаем именованную формулу: Лента-Формулы-Определенные имена-Присвоить имя
В появившемя окошке в поле "Имя" пишем любое имя, например ZZZ, а в поле "Диапазон" пишем формулу:
=СМЕЩ(Лист1!$B$1;ПОИСКПОЗ(Лист2!$A$1;Лист1!$A$1:$A$7;0)-1;0;1;1)
Кстати, в этой формуле с ячейки Лист2!$A$1 выполняется выбор фотографии, Вы можете указать свою ячейку любого листа.
4) Переходим на второй лист и далее Лента-Разработчик-Элементы управления-Вставить-Элементы ActiveX-Элемент изображение/Вставляем в любое место листа, где бы Вы хотели, чтобы отображался рисунок по выбору.
5) После проделанной операции в строке формул появится что-то типа: =ВНЕДРИТЬ("Forms.Image.1";"") заменяем эту запись на: =ZZZ
6) Теперь в зависимости от числа которое находится в ячейке A1 листа2, будет производиться выбор фотографий с листа 1.
Удачи!

Добавлено:
utwer
Читайте справку по функциям ИНДЕКС() и ПОИСКПОЗ() или ВПР()

Подскажите, пользователи работают в терминалах на одном сервере win2003ent, пользуются можно сказать одной версией office2003 sp3. Бухи используют один файл для оставления в нем служебной информации, но при его открытии периодически выкидывает ошибку
"Обнаружено содержимое, которое не удалось прочитать". Предлагает восстановить, жмем "Да". Файл открывается восстановленным, но не всегда. Иногда восстанавливаются только пустые листы или некоторые значения в них (но не все), приходится восстанавливать документ из бэкапов.
В файле нет ссылок на другие файлы, используются только фильтры и самые простые формулы. Винчестер проверен - бэд-блоков нет, пробовали даже файл в другое место переносить, та же ошибка. Подскажите из-за чего может возникать эта ошибка и как лечить?

Приветствую всех!

Как в Excel в колонтитул вставить данные из ячейки?

Если возможно, то подскажите штатное средство.

AleXPwin
А можно подробнее, зачем это нужно? Ведь если вставляются данные из ячейки, то значит это постоянные данные. Так почему же их просто тупо не вбить в колонтитул?
Насколько я знаю, штатного средства нет. Может быть возможно через макрос.

InSe0F
Это функции категории Финансовые:
NPV (Net Present Value) - ЧПС (Чистая Приведенная Стоимость),
IRR (Internal Rate of Return) - ВСД (Внутренняя Ставка Доходности).

utwer
Формула типа =ВПР(D1;$A$1:$C$10;3;ЛОЖЬ) не подойдёт?

Robertспс за помощь!


Добавлено:
kimtan

Цитата:

Добавлено:
utwer
ТЕКСТ(A1/B1;"0,00")

мне нужно округлять цифры до сотых, которые берутся из ячеек (В360, В369 и т.д.) в такой формуле: ="" & (B369) & " ∙ [1 + (" & B360 & "/100) + (" & B398 & "/100)] + " & B437 & " ="

как это реализовать ?

Robert

Цитата:

NPV (Net Present Value) - ЧПС (Чистая Приведенная Стоимость)

спасиб,
вот ща проверил, странно но результат получился разным, с расчетом по формуле написанной в учебнике... не на много, но отличается
IRR получилась та же..

Привет.
Есть один файл(лист) с данными,

где есть столбец -параметр, встречается например 20 раз подряд, и против него список данных вперемешку, но тоже есть дублирующиеся данные.

есть другой файл(лист),

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

Да простят меня присутствующие в этом топике граждане, но сил уже нет искать.

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

Для более полного понимания, что мне требуется: http://www.youtube.com/watch?v=wEgo0pqRx08.

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

Заранее благодарен за помощь.

utwer
Вот и меняйте в своей формуле ссылки на ячейки на функцию ТЕКСТ()
Например, вместо B360 нужно вводить: ТЕКСТ(B360;"0,00")
chel78
СЧЁТЕСЛИ()
r0g3r
По предоставленной Вами ссылке, ничего нет кроме видео на ютубе.

В смысле, видео как раз показывает, как этот списочек должен работать.

Но вот как его сделать?

kimtan

Цитата:

chel78
СЧЁТЕСЛИ()

- не подходит, ибо просто подсчитает сколько значений против параметра, а значения дублируются.. Мне нужно колличество уникальных, неповторяющихся значений, против параметра.

chel78
Без небольшого файла-примера с заполнеными вручную желаемыми данными, мне задача не понятна, возможно Вам поможет более прозорливый человек.
r0g3r
По этой ссылке http://www.planetaexcel.ru/tip.php?aid=64 Вы найдете достойное описание, также есть возможность скачать файл-пример для более углубленного изучения.

2kimtan:
Я уже вдоль и поперёк этот сайт облазил. Этот пример нормально работает только под 2003-м экселем. в 2007-м, судя по всему, что-то поменялось.

r0g3r
Вот, сделал в 2007:
http://webfile.ru/4043019

chel78
глянь решение тут

2kimtan:
Ы-ы-ы-ы! Дошло в чём дело. Тысячи тонн нефти Вам.
А можно ещё спросить? А если название некоего пункта в списке состоит из нескольких слов? Например, "Предоставляемые документы" а ниже на каждой строчке этот список перечисляется. Как решить проблему с именованным диапазоном? Ведь в имени оного пробелов быть не должно.

Цитата:

chel78
глянь решение тут

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

пример
http://webfile.ru/4043629

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

Добавлено:
ЗЫ хотел построить сводную таблицу на основе сводной с функцией "Количество чисел", но она почему-то не фурычит - по-прежнему подсчитывает количество...

Цитата:

удалить несущественные столбцы, оставив только Shipment и Material,

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

chel78
http://webfile.ru/4044113 вроде получилось, правда через ж... 3 сводные таблицы
(изменил несколько последних значений Shipment для проверки)


Добавлено:
для проверки добавляешь в исходную таблицу значения и поочерёдно обновляешь 3 сводные таблицы

Всё таки это извращение, щас проверил - на некоторых исходных данных появляются пустые значения... Проще хранить данные в одной базе (csv например), из которой извлекать их на лист с помощью запросов Query (сразу можно извлекать только требуемые поля и уникальные записи) - а затем результаты запроса обработать сводной таблицей

Цитата:

Проще хранить данные в одной базе

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

chel78
Цитата:

вытаскиваю из SAP

Абаперов нельзя попросить это запрограммировать?

Цитата:

Абаперов нельзя попросить это запрограммировать?

Это где? если у нас в конторе, то пустой номер.

chel78
можно ж данные выгружать из SAP в csv, а из книги Excel импортировать запросами. Вот пример http://webfile.ru/4045406 (в sample.xls 2 запроса - первый вытаскивает только 2 нужных поля и уникальные записи, сводная таблица вычисляет 24 уникальных материала в 6120678-м Shipmentе; второй запрос извлекает все данные из csv. Чтоб работало - закинь все файлы по адресу C:\tmp\temp или отредактируй dqy-файлы)

chel78
быть может осилить SAP Query (тр SQ01, SQ02)- и сразу готовые данные вытягивать (готовый отчет)

Цитата:

быть может осилить SAP Query (тр SQ01, SQ02)

не имею доступа, так как я обычный юзверь...

r0g3r
http://webfile.ru/4047329
chel78
Может так подойдет:
http://webfile.ru/4047955

Друзья, подскажите, если я не ошибаюсь, то в одной формуле можно использовать не более 7-ми (вложенных ?) операторов ЕСЛИ. А мне нужно больше, примерно 12. Как обойти это ограничение?
Если что не так написал, сорри. Я совсем чайник в этом деле, могу не правильно формулировать.

PolSmit
Масса вариантов.
ВЫБОР()
ПОИСКПОЗ()
ВПР()
ИНДЕКС()
ПРОСМОТР()
В зависимости от задачи.
На Вашем примере могу показать.