» Excel FAQ (часть 5)

Futurism

Цитата:

подскажите как протянуть эту формулу

Ответ на Ваш вопрос зависит от того, в какую сторону (вверх, вниз, вправо, влево) Вы собираетесь "тянуть" свою формулу.
Погуглите или почитайте справку Excel об абсолютных и относительных ссылках, также полезно для данного случая почитать о функциях СМЕЩ, СТРОКА(), СТОЛБЕЦ()

VictorKos
мне вправо

Futurism
Допустим, Ваша формула находится в ячейке E5

Код: =E2+СМЕЩ($B$5;СТОЛБЕЦ()-5;)

там формула стоит на ячейке U2
складываем g2+b7
должно получаться 6, а получается 4
=G2+СМЕЩ($B$7;СТОЛБЕЦ()-1;)

Если формула в U2, тогда формула такая:

Код: =E2+СМЕЩ($B$5;СТОЛБЕЦ()-21;)

VictorKos
скажите, а можно ли этот процесс упростить. Есть матрица b2:b13;m2:m13 её надо сложить. т.е. транспонировать на саму себя. Я поэтому эти числа складывал
вот файл
http://rghost.ru/56048518
тут симметричная матрица. Надо складывать наблюдения например 1 человек второму поставил единицу и второй человек поставил первому единицу. отсюда и сложнение в матрицу справа с2+b3 и так далее.
Нет ли функции который такой процесс пересложения всей матрицы упрощает?

Futurism
Выделите мышью в нужном месте диапазон ячеек 12Х12 (куда должны попасть результаты вычислений), затем нажмите клавишу F2, введите в активную ячейку формулу:

Код: =B2:M13+ТРАНСП(B2:M13)

VictorKos
ага спасибо получилось

добрый день, есть столбцы
А B C D E F и.т.д.
2 8 3 1 2 4

Подскажите формулу чтобы она слаживала по 2 столбца которые рядом
А B C
10 4 6

denisAzef
Допустим, Ваши данные (2;8;3;1;2;4) находятся в первой строке (A1;B1;C1 и т.д.)
Поместите формулу:

Код: =СУММ(СМЕЩ($A$1;;СТОЛБЕЦ(A1)*2-2;;2))

VictorKos
Формула в W7 Будет

Где можно найти подробное описание и алгоритм работы надстройки "Поиск решения".
А то есть сомнения в правильности нахождения результата.

Скажите, а можно ли оптимизировать такую процедуру?
есть массив с большим кол-вом наблюдений
Вот допустим мне надо составить отчет, например фильтрую так: по году, фирме и коду
год фирма код
2009 рога и копыта 13
2009 фейк фирма 13
2009 дружные алкаши 13 и так далее

затем этот 2009 год и эти фирмы надо просмотреть по коду 14, 15,......

после 2009 года все это надо также просмотреть по 2008 , 2007 году

вручную перетыкивать фильтр это убийство. Можно ли как то задачу упросить.
для примера приложил файл
http://www.datafilehost.com/d/5bbf9696
sic это и есть кд, тут нужны первые 2 цифры.
фирма это аудитор неим

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

Futurism
Может, Вам лучше не фильтровать, а отсортировать таблицу по нескольким критериям? (Или фильтровать после сортировки)
Но! У Вас столбец AUDITOR_NAME отделён от основной таблицы пустым столбцом - это нехорошо, при сортировке данных могут возникнуть проблемы, так как Excel в таком случае посчитает, что эти данные никак между собой не связаны и может нарушиться соответствие записей по строкам. (В данном случае этот пустой столбец включен в автофильтр, поэтому все данные считаются одной таблицей, но если автофильтр временно выключить, а затем опять включить, тогда пустой столбец может не попасть в таблицу и отделит от основной таблицы столбец AUDITOR_NAME)
Второй вариант - попробовать организовать сводную таблицу. Но тут нужно хорошо представлять, какие именно данные нужно анализировать.

Futurism -> Insert Pivot Table ->..-> Pivot Table Tools -> Analyze -> Insert Slicer

VictorKos
vikkiv
сейчас попробую.
Цитата:

Insert Pivot Table ->..-> Pivot Table Tools -> Analyze -> Insert Slicer

как это может на русском звучать? а то путаюсь. эксель 2013

Futurism - Копируешь свои (нужные а не всё подряд) данные на отдельный лист, выделяешь диапазон, нажимаешь Ctrl+T, потом выбераешь опцию "срез", там выбераешь нужные поля (год и аудитор). А в данных у тебя бардак, сильное расхождение по высотам колонн, случайно нагенерировал поиздеваться?

Друзья, подскажите, скачал я один файл, там есть расчет критерия Фишера, но я не понимаю где там формулы, их нигде. Макросов тоже не вижу.
Где посмотреть как эксель высчитывает этот коэффицент
http://rghost.ru/56256867

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

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

Есть книга с довольно не сложной таблицей значений отражающих какие то финансовые данные за месяц. Вводится она вручную - никакого копирования и ли импорта со сторонних программ.
Единственный нюанс, что некоторые ячейки содержат информацию не просто числом, а формулой такого вида:
=107,45+1000+56,6
=СУММ(107,45;1000;56,6)
Версия Excell 2007

Если верить пользователю - с определенного момента появился следующий косяк - во всех формулах которые попадают на такого плана исходные данные глючит правильность подсчета в области копеек. Ранее говорит такого не было. Открытие файла с глюками на других компьютерах, в OpenOffice и тд отображает те же неточности (значит сам глюк присутствует в сохраненном файле, а не в работе).
При этом если выделяешь диапазон - то функции быстрого вычисления суммы в статусной строке считают правильно - вот так и выявлено несоответствие.

Проблема может быть решена двумя способами:
- пройтись по диапазону и перередактировать ячейки
- погонять диапазон в точность без зпт, а потом в нужную с зпт (например 2 знака после зпт)

Все ячейки установлены в числовой формат. Автоматический пересчет формул включен.

Если кто сталкивался - подскажите куда копать.

Цитата:

если выделяешь диапазон - то функции быстрого вычисления суммы в статусной строке считают правильно

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

Маловеротно конечно,
но возможно выдруг что =5.5+5.51 даст другой результат чем =5.51+5.5

Я рискну предположить что более вероятная причина что среди чисел есть те которые просто являются буквами, а выглядат как числа. Например:
5, 5
5 ,5
5. 5
и т.д.

SFC
в принципе оно конечно могло бы быть правдой - но тогда формула сразу входит в Error
а не пытается вычислять что то.

К стати для теста это я преобразовывал вариант "+" в вариант "СУММ"

У меня подозрение, что все таки какая то фишка в количестве знаков после запятой.
Потому Excell как-то считает не с тем округлением в разных режимах...

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

Самый простой вариант - выделить всю область и увеличить разрядность, до например 5 знаков после запятой, и посмотреть где есть артефакты

SFC в том и дело
увеличить разрядность после зпт не помогает (может изменить немного но не до правильного результата), а помогает только операция: точность в 0 после зпт, а потом увеличиваешь к-во знаков после зпт.
К стати - после данной операции уже нет разницы последующие изменения точности - все уже считает правильно.

Такое подозрение - что Excell где то у себя внутри файла сохранил неправильность с точностью копеек, а после исправления сохраняет по новому и ок.

Главное что потом после правок надо опять смотреть не глюкануло они где то.

Существует ли способ объединить несколько таблиц в одну, каждую как вкладка, не делая этого руками?

Цитата:

как вкладка

Если речь идет об объединении файлов ексель в один, то есть несколько макросов в сети, каждый из которых лучше работает в зависимости от исходных файлов - с одним листом или несколькими исходные файлы

SFC

Цитата:

есть несколько макросов в сети, каждый из которых лучше работает в зависимости от исходных файлов - с одним листом или несколькими исходные файлы

Спасибо за помощь! Какой макрос работает наилучшим образом в Офисе 2010 для однолистовых файлов с двумя пустыми вкладками (которые по-умолчанию создаются), которые желательно чтобы игнорировались при объединении?

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

Замечена такая проблема:
Excel 2010. Если "заморожены панели", когда включаю фильтр лист "улетает" в крайнее правое положение.
Кто-либо встречал подобный глюк? Если да, то как с этим боролся?

Как найти номер строки в колонне содержащий первый ноль
Например в таком наборе (ниже) нужна восьмёрка (восьмая строка) т.к. она содержит первое искомое значение - первый ноль (с верху в низ):
/ А
1: 55
2: 63
3: 48
4: 35
5: 46
6: 72
7: 91
8: 0 <---- искомый адрес
9: 0