» Excel FAQ (часть 2)

Vladimir54
Цитата:

Каким образом возможно округлить отрицательное и положительное число в одной ячейке

с положительными числами все ясно, а вот как округляются отрицательные числа? вот в чем вопрос. -4,5 должно округляться до -4 или -5? Из двух отрицательных чисел больше то, модуль которого меньше (с) школьный курс математики.

Mushroomer

Я же говорю, что получается большое выражение. Есть альтернатива?

Looking

Ексель всё правильно округляет и 0,01 означает округление до сотых долей в большую сторону по модулю со знаком минус. Вопрос не в этом, а в том, чтобы сократить запись-формулу

Vladimir54
=Округл(RC[-1];RC[-1]/Abs(RC[-1])/100) Я не совсем уверен, т.к. с разрядностью 0,01 не сталкивался, но идея понятна?

Mushroomer

Да, твоя формула тоже работает, тут уже всё зависит от фантазии и мышления
Если сможешь написать в ячейке формулу, которая будет формировать числа в столбце как указано выше, без всяких фильтров и т.д., цены твоим знаниям не будет
Как насчёт убрать "#ДЕЛ/0!" ты сталкивался с такой фишкой?

Цитата:

Как насчёт убрать "#ДЕЛ/0!" ты сталкивался с такой фишкой

Ну самый простой вариант тебе уже сказали: =Если(A1=0;""; B1/A1)

Цитата:

Если сможешь написать в ячейке формулу, которая будет формировать числа в столбце как

Тебе надо без пустых строк или без пустых строк и 0?

Mushroomer

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

Vladimir54
Если нужно отфильтровать нулевые значения, можно использовать вот такой макрос:

Код: Sub AdvFilt()
Sheets("Sheet1").Range("A1:A13").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("A15:A16"), _
CopyToRange:=Sheets("Sheet2").Range("B5"), _
Unique:=False
End Sub

Vladimir54
Выложи куда-нибудь фрагмент примера. Хочу понять, почему у тебя по формулам сортировка не работает. Одними формулами тут далеко не уедешь. Тут либо фильтр, либо сортировка должны отработать.

Mushroomer

Цитата:

Ну самый простой вариант тебе уже сказали: =Если(A1=0;""; B1/A1)

Ясно, а если допустим нужно проверить на нули несколько ячеек; ведь например проверяя 2 ячейки выражение принимает такой огромный вид, а мне допустим нужно проверить сразу 8 ячеек, зная правила не трудно догадаться, что максимальное число вложений ЕСЛИ не может превышать 7 раз. Вы случайно не знаете как в условии приравнять сразу несколько - 8 ячеек к нулю?
"=ЕСЛИ(R[-2]C=0;"";ЕСЛИ(R[-2]C[1]=0;"";R[-2]C/R[-2]C[1]))" - это для двух ячеек.

Vladimir54
Тогда надо использовать комбинацию функций Если и Еошибка. =ЕСЛИ(ЕОШИБКА(A3/B3);"";A3/B3) Идея понятна?

Mushroomer

Цитата:

Хочу понять, почему у тебя по формулам сортировка не работает

Сортировка не работает, но работает через фильтр.
Yuk

Спасибо, с твоим кодом разобрался. Если будут вопросы, то напишу сюда на форум, а вообще мне очень нравится ексель

Vladimir54
Цитата:

Сортировка не работает

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

Mushroomer

Да, понятно, огромное вам спасибо.

Цитата:

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

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

Vladimir54
Цитата:

Ексель всё правильно округляет и 0,01 означает округление до сотых долей в большую сторону по модулю со знаком минус.

Что-то ты путаешь с функцией. Вот из помощи по ней:
ОКРУГЛ(число;число_разрядов)

Число — округляемое число.

Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.

Заметки

Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой.
Если число_разрядов равно 0, то число округляется до ближайшего целого.
Если число_разрядов меньше 0, то число округляется слева от десятичной запятой.

Пример:
=ОКРУГЛ(2,15; 1) Округляет число 2,15 до одного десятичного разряда (2,2)
=ОКРУГЛ(2,149; 1) Округляет число 2,149 до одного десятичного разряда (2,1)
=ОКРУГЛ(-1,475; 2) Округляет число -1,475 до двух десятичных разрядов (-1,48)
=ОКРУГЛ(21,5; -1) Округляет число 21,5 на один разряд влево от десятичной запятой (20)
Твои 0,01 воспринимаются как 0 и идет округление до целых

Looking

Ну немножкоопечатался, вот так она пишется =ОКРУГЛТ(C7;0,01) И всё будет путём и округляться будет до сотых

Vladimir54
Mushroomer
Насчет сортировки формул. Вы все ячейки, которые используются в формулах включаете в сортировку? Если да, то вроде все должно нормально сортироваться (естественно, если не происходит перемешивания ссылок). А если нет, сколько формулы не сортируй, ничего не изменится.

Yuk

Нет конечно, не все. Так как нужные для сортировки ячейки содержат формулы с другой страницы.

Vladimir54
Цитата:

вот так она пишется =ОКРУГЛТ(C7;0,01)

Слушай, где ты такую Excel нашел? Моя из Off 2003 на такую запись вообще дает ошибку


Добавлено:
Напиши, что ты хочешь получить с отрицательными числами. Пример число и результат округления.

Mushroomer:

Например в исходном файле есть такие столбцы и в такой последовательности:
Кол-во комнат
Планировка
Цена
Район
Улица
Общая площадь
Жилая площадь
Кухня
Этаж
Этажность
А в конечном файле требуется скопировать данные таких столбцов и в такой последовательности :
Комнат
Планировка
Район
Улица
Этаж
Этажность
Жилая площадь

И такую процедуру требуется повторять каждый день с файлом со свежими данными. Как это реализовать програмно? Через VBA? Я его не знаю, к сожалению. Может какой-то текст программы набросаете?

avk06
По поводу VBA это к Yuk и другим специалистам. Я по стандартному Excel (формулы) специализируюсь. Если не связываться с VBA, то могу предложить следующее: создаешь второй лист и в него формулами (типа =Лист1!A1) перетягиваешь данные с первого листа в нужном порядке. И тебе останется только заполнять первый лист данными и либо удалять лишние формулы (если новых данных меньше чем старых) или разогнать формулы на втором листе (если новых данных больше чем старых). Если не понял, то выложи пример. А я его преобразую.

YUK :

Подскажите,плиз.

Например в исходном файле есть такие столбцы и в такой последовательности:
Кол-во комнат
Планировка
Цена
Район
Улица
Общая площадь
Жилая площадь
Кухня
Этаж
Этажность
А в конечный файл требуется скопировать данные таких столбцов и в такой последовательности :
Комнат
Планировка
Район
Улица
Этаж
Этажность
Жилая площадь

И такую процедуру требуется повторять каждый день с файлом со свежими данными. Как это реализовать програмно? Через VBA? Я его не знаю, к сожалению. Может какой-то текст программы набросаете?

Looking

Цитата:

Слушай, где ты такую Excel нашел?

Excel 2003 (11.5612.5606)
Нормальный ексель, понимает все записи Надеюсь у тебя не суперкалка?;)


Цитата:

Напиши, что ты хочешь получить с отрицательными числами. Пример число и результат округления.

Задачей было округлить любое число до сотых долей, будь то число отрицательное, будь оно положительное. Задача решена

avk06
Совсем не обязательно было пост дублировать, тем более на одной странице.
VBA обсуждаем здесь.

Пока дам совет. Попробуй записать макрос через Tools-Macro-Record New Macro. После начала записи аккуратненько проделай все действия, которые должен выполнять макрос, и останови запись. Затем открой VBA редактор (Alt-F11) или через Tools-Macro-Edit и попробуй понять код и возможно внести исправления. Сразу скажу, сырой код на 100% работает не всегда. Вобщем, если будут проблемы, спрашивай в топике по VBA.

ПС. Видел, ты уже там. Возможно помогу, но чуть позже.

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

Для меня трудная задача:
На 1-листе в 25 строках(А2:А27) текст наименования химреагента
далее столбец (В2:В27) цифры с наличием этого химреагента, шт.

Надо на 2-листе автоматически через формулу(Сравнивая, есть ли в наличии шт. в столбце B2:B27, то переносится текст на 2-лист) получить текст в ячейках(А2:А17), без пропусков по строке.
Очень хотелось бы это видеть в формуле или в макросе. Сам могу построить лог. цепочку только по 5-6 строкам, с таким же условием как описал выше.

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

pikvvik
Вряд ли это зависит от объема - я пробовал в разных системах, на разных компьютерах,
от 128мбт до 512 мбт - результат везде одинаков.

2 alexandr_gl
Посмотрел у себя, таки да, до 1000...

alexandr_gl
pikvvik
Вот что MS пишет по этому поводу (англ.):
http://support.microsoft.com/kb/q295971/
Смешной там workaround.

Добавлено:
BSASergey
Подобную задачу обсуждали на предыдущей странице.
В частности, такой макрос не устроит (с небольшой переделкой)?
http://forum.ru-board.com/topic.cgi?forum=5&topic=19106&start=200#19

BSASergey
Не очень имхо и трудная: отсортируй по количеству в убывающем порядке таблицу на первом листе. А потом переноси через формулу Если данные на второй лист.
Если тебе важен порядок строк на втором листе (чтобы они шли как в первом), то решение будет длинее на один шаг: надо на первом листе добавить еще один столбец с порядковыми номерами. Это делается для того, чтобы во втором листе восстановить исходный порядок. Если что не понял, то спрашивай. Удачи.