» Excel FAQ (часть 5)

CemKey

Примените к ячейке формат Числовой
ПКМ - Формат ячеек... - Число

yuk1958, но получившееся число всеравно не соответствует исходному.

CemKey

К сожалению немного погорячился. Существуют ограничения на число разрядов в ячейке, их всего 15

yuk1958, CemKey
И не будет соответствовать. Вам же Mushroomer подсказал про ограничения...

Из хелпа.Технические характеристики и ограничения Microsoft Excel

Цитата:

Спецификации вычислений
Точность представления чисел 15 разрядов
Наибольшее число, которое можно ввести в ячейку 9,99999999999999E307
Наибольшее положительное число 1,79769313486231E308
Наименьшее отрицательное число -2,2250738585072E-308
Наименьшее положительное число 2,229E-308

CemKey, Вы так и не ответили - зачем Вам шашечки?
Если ехать - то надо огород городить ("Они в дальнейшем должны обрабатываться как числа? "). А если нет - используйте текстовое представление числа ("тип ячейки, куда это вставляется должен быть текстовым").

И еще нюанс. Такое ведь число не может появиться в недрах Экселя... Вы ведь его откуда-то берете? Так все необходимые преобразования нужно предусмотреть на возможно раннней стадии получения его.

asbo

Мне Mushroomer и не отвечап

Для того и обсуждаем, чтобы до истины докопаться

Цитата:

Мне Mushroomer и не отвечап

А два ответа до Вашего никак не прочитать? Они к делу не относятся, так, сотрясение воздуха? Что тут копать-то? Да какой истины?
- Нужно знать, куда нажимать!...
- А... ерунда, можно.
Истина в официально декларированных ограничениях. А как их обходить (и надо ли вообще!) - так автор пока молчит пристально...

Как заменить фрагмент имени файла в ссылке на ссылку на значение этого фрагмента в ячейке?
=ГПР(A$1;'C:\документы\2011\export\[export_4.XML]Sheet1'!$A:$CM;СТРОКА('C:\документы\2011\export\[export_4.XML]Sheet1'!A356);0)
Требуется вместо export_4.XML чтоб было export_5.XML например. То есть чтоб 4 на 5 сменить в одной ячейке - и все формулы разом сослались на export_5.XML
Сейчас меняю с помощью "поиск и замена"

Excel 2007 если что

crotoff, тут ведь заморочка возникает с преобразованием строки в ссылку. Я бы разделил задачу на две части:

1. Формирование имени файла, т.к. оно используется два раза.

A1 = "C:\документы\2011\export\[export_"
A2 = 4
A3 = "'" & $A$1 & A2 & ".XML]" & "Sheet1" & "'" & "!"

2. Собственно формула:
=ГПР(A$1; ДВССЫЛ(A3 & "$A:$CM"); ДВССЫЛ(A3 & "A356"); 0)

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

asbo
спасибо за идею, покумекал над формулой - всё получилось!

такая проблема: когда захожу в microsoft excel 2010, пишет, что пока выполняется загрузка необходимого компонента программа может не реагировать на действия, происходит это очень часто, при этом бегут килобайты и очень тупит комп, уже реально достало, можно ка-нибудь от этой хрени избавиться?
короч виснет совсем и не даёт отредактировать ни одного документа

Добавлено:
нажимаю сохранить-намертво зависает

Добавлено:
после последнего зависания решил перезагрузить, появился синий экран, потом после входа в систему появилось окно: Сигнатура проблемы:
Имя события проблемы:    BlueScreen
Версия ОС:    6.1.7600.2.0.0.768.3
Код языка:    1049

Дополнительные сведения об этой проблеме:
BCCode:    22
BCP1:    00000000BEF00C64
BCP2:    0000000000000001
BCP3:    FFFFF8800BE3C768
BCP4:    FFFFF8800BE3BFD0
OS Version:    6_1_7600
Service Pack:    0_0
Product:    768_1

Файлы, содержащие сведения об этой проблеме:
C:\Windows\Minidump\050411-17472-01.dmp
C:\Users\Якут\AppData\Local\Temp\WER-50684-0.sysdata.xml

Ознакомьтесь с заявлением о конфиденциальности в Интернете:
http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0419

Если заявление о конфиденциальности в Интернете недоступно, ознакомьтесь с его локальным вариантом:
C:\Windows\system32\ru-RU\erofflps.txt
а это что такое?

Цитата:

а это что такое?

Винда глючит. К офису претензий нет.

Приветствую уважаемые!
Искал на форуме ответ на интересующий меня вопрос но (Excel FAQ (часть 1...5) страниц под полтысчи) но не нашел.
Помогите:
1. функция дес.в.дв. в экселе - можно преобразовать все числа от -512 до +511 в двоичную 10-разрядную, где первый (старший) бит знак. А можно ли все числа от 0 до 65535 преобразовать в двоичную 16-разрядную.
Пример:
0 ---> 0000000000000000
1 ---> 0000000000000001
.....
65535 ---> 1111111111111111

2. результат (т.е. двоичное число) записан в отдельную ячейку. Можно ли каждый бит результата записать в отдельную ячейку.
Пример: Было |1| ---> |0000000000000001|
Стало: |1| ---> |0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|

3. Как можно инвертировать результат?
Пример: Было |1| ---> |0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|
Стало: |1| ---> |1|1|1|1|1|1|1|1|1|1|1|1|1|1|1|0|

Я бы и дальше рылся в инете, но честно, моя задача не изучения глубин экселя которым я пользуюсь раз в год. а по-быстрому сделать эту таблицу перевод десятичных чисел в двоичную систему с 16 разрядами.
Если достаточно трудно объяснить то укажите направление поиска. Например какими функциями можно это сделать. Дальше я сам.
Спасибо за ранее.

mazurik80, надо макрос писать. У встроенной функции ограничения... Думаю готовых решений полно, надо только найти. Вот и вот. Разжевывание алгоритма

Яндекс
Вики

С макросом - сюда. И здесь могут помочь быстро и эффективно.

как в формуле округления ( =ОКРУГЛ((ПРОИЗВЕД(СУММ(E6:E12)*1%));2) ) сделать так, что бы получаемое число например 38,475 при округлении до сотых стало 38,47 а не 38,48... то есть 1,2,3,4,5 округлялось в меньшую сторону 6,7,8,9 в большую...

Кто подскажет, почему может не срабатывать автосохранение в Excel 2003 (11.6560.6568) SP2?
В Сервис-Параметры-Сохранение-Параметры выставлено
"Сохранять данные для автовосстановления каждые" - 1 минут
Каталог данных для автовосстановления -
C:\Documents and Settings\Администратор\Application Data\Microsoft\Excel\
Однако, сколь бы долго изменённый документ ни был открыт, всё равно при закрытии выдаёт диалоговое окно с запросом о сохранении изменений!

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

Добавлено:
I love Opera, а автосохранение и автовосстановление не одно и то же. Автосохранения в 2003 (я так понял) нету. Только автовосстановление. MS сам запутался и других запутал в хэлпе, если внимательно посмотреть материалы поиском по "автосохранение"

Цитата:

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

очень жаль... а где можно найти код? есть ссылки какие нибудь где смогут помочь по этому вопросу?

el_diablos

Цитата:

то есть 1,2,3,4,5 округлялось в меньшую сторону 6,7,8,9 в большую...

Попробуй так:
=ОКРУГЛ((ПРОИЗВЕД(СУММ(E6:E12)*1%))-0,001;2)

el_diablos, методов "правильного" округления великое множество. Это весьма обширная тема. Если хочется копаться в этом, то хотя бы с этого начать. Очень много подводных камней...

LaCastet, но это же будет сугубо-сугубо частный случай :( ...

=ОКРУГЛ((ПРОИЗВЕД(СУММ(E6:E12)*1%))-0,001;2) Должно подойти при любых раскладах если прописать во все ячейки... 38,475 - 0,001 при округлении даст 38,47... (38,476 38,477 38,478 38,479 38,480) - 0,001 при округлении даст 38,48... (38,474 38,473 38,472 38,471 38,470) - 0,001 при округлении даст 38,47 я прав или ошибаюсь? помойму прав...

el_diablos, я же и не говорил, что неправильно :) Математика-то сходится. В данном, конкретном случае. Мы не знаем, какой точности и с каким знаком будет результат (СУММ(E6:E12)) * 1%). Бзв, а нафига ПРОИЗВЕД с одним аргументом?

Если с точностью еще можно поступить так: ОКРУГЛ(СУММ(E6:E12)) * 1%;3), то со знаком сложнее. Как будем интерпретировать больше-меньше? В какую сторону? Ну, хрен с ним, задействуем ЗНАК в лоб. Двойной пересчет будет. Получим:
=ОКРУГЛ(ОКРУГЛ(СУММ(E6:E12) * 1%); 3) - ЗНАК(СУММ(E6:E12)) * 0,001; 2)

Честно говоря - я в рот компот такой огород :(

2 asbo результат только положительный во всех ячейках, вбил - 0,001 что бы проверить во все ячейки в двух из них итог получился не тот... например =ОКРУГЛ((ПРОИЗВЕД(E6;2%))-0,001;2) итог 8,24 =ОКРУГЛ((ПРОИЗВЕД(СУММ(E6:E12)*1%))-0,001;2) итог 10,72 в этих ячейках ничего не изменилось все как и должно быть, а вот =ОКРУГЛ((ПРОИЗВЕД((E15-E13)*3%))-0,001;2) вместо 33,70 дал итог 33,69 =ОКРУГЛ((ПРОИЗВЕД((E15-E13)*14%))-0,001;2) вместо 157,25 дал итог 157,24... что не так?

1. Я прежде всего рекомендую писать по одной формуле на строчку. Иначе вопринимать крайне неудобно. И не надо их переносить.

2. Какой смысл писать хотелки без исходных данных? Ну, хотите Вы 33.70, а он дает 69. И что? Как это проверить?

3. Я Вам задал вопрос про ПРОИЗВ? Задал. Вы ответили? Нет. Кто знает, сколько у Вас там еще такой лажи? Нафига он в каждой формуле торчит? Чтобы вообще не прочитать? Вам нравится считать скобки? Мне тоже нет...

4. Я ведь порекомендовал округлять промежуточный результат. Вы сделали? Нет.

5. Вы в последнем посте привели четыре разных формулы. Давайте говорить все таки про одну. Все модификации делайте по аналогу и оставьте у себя в проекте.
=ОКРУГЛ((ПРОИЗВЕД(E6;2%))-0,001;2)
=ОКРУГЛ((ПРОИЗВЕД(СУММ(E6:E12)*1%))-0,001;2)
=ОКРУГЛ((ПРОИЗВЕД((E15-E13)*3%))-0,001;2)
=ОКРУГЛ((ПРОИЗВЕД((E15-E13)*14%))-0,001;2)

2. сорри держу в руках 2 жиловки разных людей присланных из одной и той же бухгалтерии (ориентировался на них) создал прогу в excel для подсчета зарплаты, в итоге проблема в этих самих жиловках (пересчитал все на калькуляторе)... в одной например 38,475 округлили как 38,47 в другой жиловке 38,48... формула с - 0,001 идеально подходит при положительном резульате итог не сходится лишь из за того что считали по разному (комуто плюс копейку кому то минус копейку) исходя из этого точную формулу не создашь хрен знает как бухгалтерия подсчитает на следующий раз... в итоге буду прописывать формулы без -0.001 и буду получать результат с погрешностью в пару копеек, другого выхода нет...
3. насчет произвед все убрал формулы упростил (сорри в excell не ас читал про формулы из справки в самом excell, а там очень ограниченная информация вот сейчас начинаю понимать что к чему)
=ОКРУГЛ(E6*2%-0,001;2)
=ОКРУГЛ(СУММ(E6:E12)*1%-0,001;2) и тд

asbo i LaCastet spasibo!

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

По п.3 - рекомендую вынести промежуточные вычисления сумм и процентов в отдельную ячейку (не считать все строкой) - тогда видней и наглядней будет - какое "округление" применять.

И обязательно введите промежуточное округление до третьего знака!

Чтобы все однообразно было и можно было формулы растягивать сделайте так:
=ОКРУГЛ(A2 - A4 * 0,001; 2), где
A4: 0 или 1, в зависимости от того, как округлять, и
A2: =ОКРУГЛ(СУММ(E6:E12) * A3, 3), где A3: проценты

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

4rm1nVan6uuren, не устанавливал ни разу. Могу лишь предположить, что установка офиса была в лоб и многие компоненты были установлены с опцией по первому требованию. Я бы переустановил или с опциями "не устанавливать вообще", или устанавливать все на HDD (как-то так).

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

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

* около миллиона строк
- Да больше, больше! :)

Можно, написать формулу, но на миллионе строк она получится очень задумчивая. Можно скопировать в Word - там замену сделать намного проще.