» Excel FAQ (часть 4)

Как удалить все скрытые строки на листе?
Может макрос какой есть?
А то сильно мешают, копировать невозможно.

uhty

Цитата:

Как удалить все скрытые строки на листе?
Может макрос какой есть?

Да, действительно такой маккрос есть! Для его запуска, когда не нужные строки скрыты, надо нажать:
Ctrl+A
Ctrl+C
Shift+F11
Ctrl+V

Всё - макрос будет выполнен, скрытые строки удалены ;D

Добавлено:
Pravoved90

Цитата:

Есть уровнение
=H30+R30<3
Как записать, что оно действует только в случае, если H30 или R30 не =2

Цитата:

Суть в том, что если сумма двух сравниваемых чисел меньше "3" - ячейка с результатом выделяется цветом. Но как исключение - вариант 2:0

Правильная постановка задачи звучит так:
Покрасить в красненькое, если выполняются ВСЕ условия:
1. A1+A2<3
2. A1<>2
3. A2<>2

Формула для условного форматирования будет иметь вид:
=И(A1+A2<3;A1<>2;A2<>0)=ИСТИНА

если необходимо что бы двойки не было в большом диапазоне, к примеру чтобы в A1:A100 не было ни одной двойки, то можно будет задать и такое условие.

Mushroomer
Фантастика! Не один час просидел с экселем, искал ответы в гугле, на офф справке и всё без результатно, даже надежду начал терять, что такое возможно в экселе.
С экселем не дружу, пользуюсь им раз в полгода и тут появилась необходимость, а знаний, как оказалось, нет.
Спасибо за помощь, ты уже не раз выручил на форуме

Добрый день. Всем огромное СПАСИБО за квалифицированную помощь. Столкнулся с таким вот и глюком. Не знаю, это везде так или чего- то сделать можно для устранения?
Вобщем есть два сравнительных значения: A и B, которые означают общее количество. Также дальше идет разделение на подкатегории(отдельное количество каждого предмета). Далее для каждой подкатегорий формула вычисляет % от общего количества категории =100/H9*J9, где H9 -общее количесвто,J9 - ячейка подкатегорий.
Конечная формула отнимает получившийся результат соответствующих для сравнения подкатегорий - на сколько % одна больше другой.
=(F10-J10)*(100/МАКС(F10;J10)), где F10;J10 -ячейки сравниваемых подкатегорий.

Проблема заключается в следующем: Если одно из значений A или B = 11, 22 или 44(других пока не нашел) а каждая из сравниваемых подкатегорий = 100%, вместо должного результата =0, выдается такой ужос:
-1,42109E-14
Если это глюк - то как его исправить? С другими числами при идентичном вычеслении все ОК.

Pravoved90
Цитата:

выдается такой ужос: -1,42109E-14

А какой формат у этой ячейки?

Pravoved90 раздвинь широко-широко ячейку, просто число в ячейку не влазит))))).

Цитата:

-1,42109E-14

=-0,0000000000000142109
уменьши разрядность

Добавлено:
и переведи формат из общего на числовой, но тогда если число не влезет вот такая хрень вылезет ####### так что в любом случае надо уменьшить разрядность для всех ячеек, где возможны такие вот неоднозначные значения. А если такого быть вообще не должно, то есть должен получаться точный нуль, тогда проверяй формулы - где то возможно ошибка.

Дело в том, что все другие числа проходят нормально, да и по логике вещей по формуле =(F10-J10)*(100/МАКС(F10;J10)), если F10=J10 должен четкий ноль получаться при любых значениях.
Другое дело, что F10,J10=100 и это 100 появляется от формулы =100/H9*J9, где H9,J9=11.

Цитата:

А какой формат у этой ячейки?

Формат обычный по умолчанию.
А как эту самую разрядность уменьшить?

Цитата:

А как эту самую разрядность уменьшить?

Благодарю за ответы. Уже внедряюсь в мир таблиц))

Добрый день, Господа. Снова небольшое затруднение: Есть ряд таблиц с итоговыми значениями в каждой из них. Мне нужно скопировать все эти итоги в одну общую итоговую таблицу(естественно со всеми параметрами, чтобы менялись автоматом при изменении в каждой таблице). Но при копировании получается примерно следующее. Напр. ячейка с формулой
=ЕСЛИ(МАКС(E11;I11)=0;0;((E11-I11)*(100/МАКС(E11;I11))))
превращается в
=ЕСЛИ(МАКС(#ССЫЛКА!;#ССЫЛКА!)=0;0;((#ССЫЛКА!-#ССЫЛКА!)*(100/МАКС(#ССЫЛКА!;#ССЫЛКА!))))
а в самой ячейке ######
Как ее скопировать со ссылками на предыдущую таблицу? Подозреваю, что дело в $, но как и что сделать - незнаю. Подскажите, будьте любезны.

Pravoved90
Может проще в итоговой все оформить ссылками на "ряд таблиц"?
А это:
Цитата:

=ЕСЛИ(МАКС(#ССЫЛКА!;#ССЫЛКА!)=0;0;((#ССЫЛКА!-#ССЫЛКА!)*(100/МАКС(#ССЫЛКА!;#ССЫЛКА!))))

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

Цитата:

ячейка-переменная уходит за край листа

Так и получилось - потому, что формулу в верх листа ставил, а исходная внизу. Получилось - что формула копируется с пропорциональным изменением ячеек в новую таблицу. А мне нужно, чтобы ячейки были из старой.
Цитата:

проще в итоговой все оформить ссылками на "ряд таблиц"?

А как это сделать? В каждой таблице по несколько итоговых ссылок формулами + с условным форматированием.
Может, есть возможность заменить все формулы на $? (например А14 на $А$14). Тогда по идее должно сработать. Можно ли такое сделать и как? Есть ли в єтом смысл - может есть более простое решение? Спасибо

Pravoved90

Цитата:

А как это сделать?

Если есть исходные таблицы с готовыми значениями просто заполни итоговую ссылками на эти значения Ну, что-то вроде =А1, =В2, =С3

Цитата:

Может, есть возможность заменить все формулы на $? (например А14 на $А$14). Тогда по идее должно сработать.

Сработает и так.

Цитата:

Сработает и так.

Значения действительно копируються, а вот условное форматирование нет(цвет не меняется). К тому же - так только одну формулу скопировать можно, а их у меня в каждой таблице по 10 штук и таблиц еще штук 40. Долговато будет. Уверен, Ексель предусмотрел такой вариант.

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

Pravoved90
Цитата:

Значения действительно копируються, а вот условное форматирование нет(цвет не меняется)

Для задания однообразного условного форматирования (а так же и для не условного - обычного форматирования) рекомендую пользоваться "веником" (формат по образцу):
1. для этого на исходном листе жмем Ctrl+A
2. потом "веник" (формат по образцу)
3. переходим на лист/книгу которым хотим предать одноообразие
4. опять жмем Ctrl+A либо тыкаем мышью в ячейку А1
после чего на лист должны скопироваться все элементы форматирования с исходного листа

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

Добавлено:

Цитата:

а их у меня в каждой таблице по 10 штук и таблиц еще штук 40

если я тебя правильно понял ты хочешь посчитать разное из разных таблиц в итоговой;
тогда вот тебе формула =[Книга5]Лист1!$A$1+[Книга4]Лист1!$A$1+[Книга3]Лист1!$A$1+[Книга2]Лист1!$A$1+[Книга1]Лист1!$A$1, которая выдает сумму значений ячеек А1 из файлов Книга1.xls, Книга2.xls, Книга3.xls, Книга4.xls и Книга5.xls. Причем такая "сложная" формула была создана за 5 кликов мышью и 4 нажатия "+"

Цитата:

если я тебя правильно понял ты хочешь посчитать разное из разных таблиц в итоговой;

Не совсем так. Мне нужно не столько посчитать, сколько видеть в одной таблице все итоговые данные остальных таблиц вместе со всеми форматированиями. То есть - меняющимися цифрами и цветами. Другими словами - скопировать в буквальном смысле ячейки с каждой таблицы в одну.
Цитата:

рекомендую пользоваться "веником" (формат по образцу):

В том то и дело, что это формат по образцу. То есть копируется сама формула и пропорционально - ячейки в новую книгу. Результаты не копируются. Мне же надо, чтобы все форматы ссылались на книгу - первоисточник расчетов. То есть - сам образец со всеми вычислениями.

Добавлено:
Нашел как называется то, что мне нужно без деталей - абсолютная ссылка!(наверное смешно звучит для ГУРУ). Подскажите, как мне ее сделать. Понял, что можно нажатием F4 внутри формулы для отдельной ячейки. Но это безумно долго. Есть ли комплексный вариант? - так сказать, одним движением руки всю область ячеек заменить с обычных формул на абсолютные???

Ни с условным форматированием по условиям из другой книги/листа, ни с заменой относительных ссылок на абсолютные ссылки целого диапазона стандартными и прямыми средствами ничего не выйдет. Причем, мне кажется, даже макросами не очень то просто это будет сделать. Хотя я не ГУРУ и могу чего то не знать. Могу лишь посоветовать "танцы с бубнами":
1. для условного форматирования завести на листе с итогами специальные ячейки с формулами, которые бы из файлов с данными вычисляли нужно ли окрашивать или нет, к примеру если надо, то получаем 1 (истина) - не надо тогда 0 (ложь). После делаем усл. форматирование на ячейках с итогами ссылаясь на только что созданные служебные ячейки, которые естественно можно скрыть, чтоб не мешались.
2. блин была у меня когда то такая же проблема: нужно было чтобы хитрым образом ссылки в формулах то были относительными, то абсолютными. Из положения выходил лишь заменой (Ctrl+H, менял A на $A$) в итоге получилось, но все равно пришлось повозиться.

PS может еще кто что посоветует, но чета я сомневаюсь.....

Pravoved90

Цитата:

Узнал про вариант клацать F4 на каждом значении ячейки в каждой формуле - но єто бесконечно долго. Есть ли комплексный вариант - чтобы так сказать, изменить весь диапазон ячеек одним кликом??

можно через макрос.
вот здесь было обсуждение решения через макрос. (точнее два - быстрый и медленный! - быстрый можно использовать ТОЛЬКО если не используются формулы массива!!!!)
а сами макросы тут - Convert Excel Formulas from Relative to Absolute_ Convert Excel Formulas from Absolute to Relative References

Макросы - это хорошо...когда знаешь как ими пользоваться..К сожалению - это не про меня. Когда-то записывал макрос на уроке информатики в Ворде отдельнными щелчками..И шифров, конечно, не было. Обьясните, будте любезны на пальцах: Как его запустить. Все, что я понял - это скопировал весь текст в Visual Basic в Екселе..А дальше что? Оно даже не сохраняется..И где задать кнопку запуска макроса?..В текст надо чего-то дописывать вроде своих формул или полностью копировать, и что копировать - откуда до куда(поймите правильно, для меня эти шифры - Как экран из "Матрицы")

Добавлено:
А вообще я конечно очень удивлен, что такая мощная программа, как Excel не предусмотрела такой простой функции...

Добавлено:
Потихоньку схожу с ума...Попробывал вручную изменить в формуле A на $A$. В итоге имею следующее:
1. При копировании без $ ячейки формулы копируются пропорционально ее месторасположению на новом месте. На старую книгу НЕ ссылается.
2. При копировании с $ ячейки формулы копируются и ссылаються на указанную в формуле $A$, но в НОВОЙ книге. На старую книгу НЕ ССЫЛАЕТСЯ.
Как же сослаться на старую??? Хоть както....................................................................

Всем привет!

У двух пользователей возникла интересная ситуация:
При неопределённых условиях периодически пропадают значения в ячейках.
Пропадают целыми строками и иногда не полностью.
Значения появляются при выделении ячейки, строки или всего листа (ctrl+a).
Подскажите, пожалуйста, как с этим бороться (копать-то хоть куда)?

Скриншоты:

Внимание на строки 85 и 106 - ячейки в строках между ними, на самом деле заполнены.



Ячейки строк 67-84 заполнены.



Это видно если выделить строки.



P.S. Офис 2007 SP1 лицензия. Все обновления для мастдая и офиса установлены (XP SP3).

Решение проблемы см. на TechNet'е Excel 2007 - глюки или "визуальные эффекты"?

Pravoved90

Цитата:

1. При копировании без $ ячейки формулы копируются пропорционально ее месторасположению на новом месте. На старую книгу НЕ ссылается.
2. При копировании с $ ячейки формулы копируются и ссылаються на указанную в формуле $A$, но в НОВОЙ книге. На старую книгу НЕ ССЫЛАЕТСЯ.
Как же сослаться на старую??? Хоть както....................................................................

Во как! Начинаю понимать, что тебе нужно)))))
тебе нужно не A на $A$ менять, а A на [Книга1]Лист2!$A$, где Книга1 старый файл, Лист2 - лист в старом файле откуда надо брать значения и формула вот такая страшенькая будет =[Книга1]Лист2!$A$2+[Книга1]Лист2!$A$3.
FL0od13
Раздвигание ячеек, смена шрифта и размера шрифта, смена цвета шрифта, смена выравнивания в ячейке, задание формата по образцу из ячеек где все нормально отображается не помогает?
Проблема возникает только у двух пользователей? У остальных пользователей этот же файл отображается корректно?
из банального: может вирус?
а в другой версии екселя все нормально?
Вообщем следует потыркать файло поиграть с настройками отображения, попытаться определить закономерность при которой пропадают значения

Добавлено:
Pravoved90
Блин, всё таки рекомендую тебе подумать о способе консалидации всех данных в одном файле. Сейчас сохранил и закрыл Книгу1 и в файле с итогами формула автоматом сменилась на вот такой страх: ='C:\Documents and Settings\user\Мои документы\[Книга1.xls]Лист2'!$A$2+'C:\Documents and Settings\user\Мои документы\[Книга1.xls]Лист2'!$A$3

всего в екселе считается формула в которой не более 255 символов (примерно, я точно не знаю). У меня такое было - приходилось файлы в корень диска скидывать чтобы знаки на пути к файлам не тратились!!!! В общем будь внимателен и не увлекайся связыванием сложными формулами нескольких файлов!!!

Друзья помогите пожалуйста решить 2 задачки

1. Нужно создать объявление (платежка)
необходим формат 0-00 - сделал
как подсчитать сумму рублей и копеек используя этот формат
234-80
560-80
120-10
в еще одной ячейке должна быть сумма этих трех в формате 0-00.
+
еще есть макрос(сумма прописью) как заставить его считать копейки и рубли, считает только рубли сейчас.


2. Нужно подсчитать сколько человек (в определ диапазоне ячеек) с 12 лет до 45
при использ-и фун-и СЧЕТЕСЛИ можно указать только один критерий, как с диапазоном возрастов решить проблему?

Заранее благодарю!

Ну вот так и сделал...Для нормальных формул все Ок(не считая предверия работы до ночи по замене формул )
А вот условное форматирование решило меня добить надписью -Для условий "условного форматирования" нельзя использовать ссылки на другие листы или книги-...Только один скромный(риторический) вопросик...ПОЧЕМУ??????!!!

Кстати, может можно вообще задать просто цвет ячейки А1 из Книги1 = цвет ячейки A2 из книги2??? Тогда все мучения б сошли на нет????

q1wed
Спасибо за быструю реакцию.

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

В общем без бубна не обойтись...
Мне можно помочь, только если у кого-то что-то подобное уже было.
Сейчас я пересохранил документ в родной xlsx и буду ждать появления эффекта у бухгалтера. Следующим шагом будет "раздвигание" строк и т.д.
Как найду способ решить проблему обязательно отпишу.

FL0od13
А может белый текст набит по белому полю?

Pravoved90 читай внимательнее я уже писал как можно перенести условное форматирование в другой файл:

Цитата:

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

придется тебе этим методом воспользоваться раз уж ты такой упорный)))))

Цитата:

читай внимательнее

Да я читаю, но не понимаю...))перегрев. Можно на примитивном примере?

Пример
Есть формула для условного форматирования =И(A1+A2<3;A1<>2;A2<>0)=ИСТИНА в Книге1, необходимо чтобы это условное форматирование выполнялось Книге2 на основании данных Книги1.
Для этого встаешь в какую-нибудь ненужную ячейку, к примеру Z1, и вставляешь вот такую формулу =И(A1+A2<3;A1<>2;A2<>0) и меняешь в ней A на [Книга1]Лист2!$A$. Результатом этой формулы будет либо ИСТИНА, либо ЛОЖЬ, причем данные она будет брать из Книги1.

Теперь идем в ячейку которая должна окрашиваться и делаем условное форматирование: =Z1=ИСТИНА Ну вот можно считать что мы обманули эксель произведя условное форматирование использовав ссылки на другие листы или книги ;D
Теперь можно скрыть столбец Z чтобы всякая ненужная нам информация не маячила перед глазами: выделяем столбец Z - правая конопка - скрыть.


Добавлено:
mistx

Цитата:

как подсчитать сумму рублей и копеек

http://planetaexcel.ru/tip.php?aid=78

макрос делает только то чему его научил создатель)))
следовательно для прописывания копеек есть три варианта:
1. дописать макрос самостоятельно
2. найти макрос который считает копейки
3. попросить кого-нибудь вот здесь что бы кто-нибудь сделал за тебя 1 или 2 пункт

Цитата:

подсчитать сколько человек (в определ диапазоне ячеек) с 12 лет до 45

легко решаемо если использовать столбец-индикатор. вставляем вот такую формулу =(A1>12)*(A1<45) растягиваем её и потом производим суммирование по этому столбцу
так же при помощи формулы БСЧЕТ - прочти справку по этой функции там как раз подходящий пример приведен.

- это я. НАКОНЕЦ ТО!!!! ЦВЕТ СКОПИРОВАН. ДА ЗДРАСТВУЕТ ЛОГИКА МЫШЛЕНИЯ И ПРЕВОСХОДСТВО ЧЕЛОВЕЧЕСКОГО ИНТИЛЛЕКТА НАД МАШИНОЙ!!! q1wed ОГРОМНОЕ ТЕБЕ СПАСИБО - можно сказать, вытянул за уши))).
Что б самому осознать этот процесс, решил еще своими словами на пальцах описать это действо. Может, пригодиться, таким энтузиастам, как я.
ИТАК:
Есть: Книга1, Книга2
Цель: Заставить меняться цвет в Книге2 по данным из Книги1.
1. В Книге1 Копируем формулу из условного форматирования, отвечающую за необходимый цвет в обычную формулу в ненужную ячейку А1. Получаем значение Истина или ложь(Допустим, наличие цвета - ИСТИНА, отсутствие -ЛОЖЬ)
2. Переходим в Книгу2. Также на ненужной теперь здесь ячейке B1,пишем формулу =Книга1!A1(то есть ссылаемся на указанную выше ячейку в Книгу1)
3. На нужной нам для смены цвета ячейке в Книге2 заходим в Условное форматирование и пишем формулу =B1=ИСТИНА(и нужный цвет) Таким образом при значении ИСТИНА в упомянутых ячейках получаем цвет в нужной ячейке. Ура.
Вот так - это опять я

q1wed - большое спасибо. Ответы в топике отличные! Так держать!

mistx

Цитата:

еще есть макрос(сумма прописью) как заставить его считать копейки и рубли, считает только рубли сейчас.

ещё добавлю к словам q1wed - в шапке есть ссылочка на надстройку "надстройка Excellerator (c) Michael Zemlanukha & Maxim Shemanarev" - крайне рекомендую попробовать. Проста в установке и пишет прописью и рубли и копейки просто замечательно!


Добавлено:

Цитата:

Нужно подсчитать сколько человек (в определ диапазоне ячеек) с 12 лет до 45

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