» Excel FAQ (часть 4)

Просьба к посетителям топика дополнять шапку наиболее часто задаваемыми вопросами со ссылками на ответы

в обязательном порядке

Смена представления имен столбцов с буквенного на цифровое и обратно

---

Сумма прописью
Надстройка "PROP" (сумма прописью)
надстройка Excellerator (c) Michael Zemlanukha & Maxim Shemanarev
макрос Num2String

---

Список соответствия имен функций в английской и русской версиях Excel
Описание Microsoft Excel File Format (eng.)
Горячие клавиши в Excel (табличка)

Предыдущие ветки топика: Часть 1 | Часть 2 | Часть 3

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

При необходимости выложить скриншот, пользуйтесь сервисом ImageShack® (ограничение на размер файла <1.5 Mb)

Книга Джон Уокенбах "Подробное руководство по созданию формул в Microsoft Office Excel 2002" PDF 34МБ Файлы к книге

Привет всем! Кто знает где в Excel хранятся настройки по-умолчанию для полей для печати? Где подправить чтобы при открытии нового дукумента поля были поменьше? Чтобы каждый раз не лезть в Файл-Параметры страницы-Поля?

На машине WinXP+Office2003
есть файл.xls размером 400МБ. с кучей данных и формул.
при добавлении больше 100 строк в excel изменяется формула в некоторых ячейках, но не во всех.

=ЕСЛИ(E32228="B";0;ЕСЛИ(L32228="ММВБ";0;ЕСЛИ(BL32228="БФГ-Кредит";0;ЕСЛИ(BL32228="РТС";0;ЕСЛИ(BL32228="ВТБ";0;ЕСЛИ(BL32228="БКС";0;ЕСЛИ(E32228 ="DU";0;BG32228)))))))
на
=ЕСЛИ(E32228="B";0;ЕСЛИ(L32228="ММВБ";0;ЕСЛИ(BL32228="БФГ-Кредит";0;ЕСЛИ(BL32228="РТС";0;ЕСЛИ(BL32228="ВТБ";0;ЕСЛИ(BL32228="БКС";0;ЕСЛИ(E64322="DU";0;BG32228)))))))

Добрый день. У меня следующие вопросы: 1. Можно ли сделать в екселе так, что бы при вводе значения цифр изменялись данные по формуле не только в пределах одной книги, но и менялись значения в других книгах(возможно и другом документе?) Если да, то как?
2. Можно ли создать в екселе строчки-шаблоны, как в Worde(при нажатии на строчку выпадают несколько заранее введенных значений). Очень нужно узнать. Заранее благодарю за ответы.

Pravoved90
Цитата:

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

Конечно можно. Это называется ссылка между файлами. Правда, я такие ссылки не люблю. У меня в далеком 1996 году поехали ссылки, после какого-то добавления строк.
А делается это так:
открываешь две файла: файл1 и файл2. В файле1 в ячейке пишешь = и через меню окно переходишь в файл2. Там тыкаешь в ячейку, откуда ты хочешь. чтобы брались данные. Когда закончишь набирать формулу, нажми Enter.
Когда будешь открывать файл, который содержит внешние ссылки (в данном примере это файл1), Excel спросит что-то типа "файл содержит внешние ссылки. Обновить их?". Надо ответить да, чтобы были актуальные данные.

Благодарю за ответ. Что-то получилось)). А как насчет шаблонов: Можно ли их сделать, и если да, то чтобы заданные в шаблоны значения также изменялись от вводимых в некоторые поля цифры?

Добавлено:
И еще: Как задать формулу примерно такого содержания: Если значение в поле А =1 или меньше значения в поле В на 1, значение в поле С становится красного цвета?

Pravoved90
Цитата:

Как задать формулу примерно такого содержания: Если значение в поле А =1 или меньше значения в поле В на 1, значение в поле С становится красного цвета?

Копай в сторону условного форматирования из меню Формат.

miha7411

Сохранение параметров книги, которые требуется использовать при каждом запуске Microsoft Excel с шаблоном книги (book.xlt)

Сохраняемые параметры

Форматирование
Форматы ячеек и листов. Устанавливаются с помощью команд меню Формат.

Форматы страниц и параметры области печати для каждого листа.

Стили ячеек.

Число и тип листов в книге.

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

Текст, данные, рисунки и формулы
Повторяемый текст, например заголовки страниц, подписи строк и столбцов.

Данные, формулы, графика, диаграммы и другие данные.

Параметры проверки данных.

Панели инструментов и параметры настройки
Пользовательские панели инструментов, макросы, гиперссылки и элементы ActiveX в формах. Чтобы пользовательская панель инструментов была доступна, следует присоединить ее к шаблону.

Параметры вычислений и просмотра, установленные с помощью команды Параметры (меню Сервис).

Создание шаблона

Определите тип требуемого шаблона:

Шаблон книги

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

Шаблон листа

Создайте книгу, содержащую один лист. Поместите на этот лист элементы форматирования, стили, текст и другие данные, которые должны появляться на всех новых листах того же типа.

Чтобы просматривать рисунок первой страницы шаблона в поле Просмотр диалогового окна Шаблоны (область задач Общие шаблоны, Новая книга ), в меню Файл выберите команду Свойства , откройте вкладку Документ, а затем установите флажок Создать рисунок для предварительного просмотра .

В меню Файл выберите команду Сохранить как.

В окне Тип файла выберите пункт Шаблон.

В списке Папка укажите папку, в которой должен быть сохранен шаблон.

Чтобы создать шаблон книги для использования по умолчанию (Используемый по умолчанию шаблон книги. Шаблон Книга.xlt, создаваемый для переопределения используемого по умолчанию формата новых книг Microsoft Excel. Именно этот шаблон будет использоваться Microsoft Excel для создания пустой книги при запуске или при создании книги без указания шаблона.) или шаблон листа для использования по умолчанию (Шаблон листа по умолчанию. Шаблон Лист.xlt, создаваемый для переопределения используемого по умолчанию формата вновь создаваемого пустого листа Microsoft Excel. Именно этот шаблон будет использоваться Microsoft Excel для создания пустого листа при добавлении листа в книгу.), выберите папку XLStart или альтернативную папку автозагрузки. Папка XLStart обычно расположена по следующему пути:

C:\Program Files\Microsoft Office\Office11\XLStart

Чтобы создать специальный шаблон книги или шаблона, выберите папку «Шаблоны», которая обычно расположена по следующему пути:

C:\Documents and Settings\имя_пользователя\Application Data\Microsoft\Шаблоны

Введите имя макроса в поле Имя файла . Выполните одно из следующих действий.

Шаблон книги

Чтобы создать шаблон книги для использования по умолчанию, введите книга .

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

Шаблон листа

Чтобы создать шаблон книги для использования по умолчанию, введите лист

Чтобы создать специальный шаблон листа, введите любое допустимое имя файла.

В меню Файл выберите команду Сохранить, а затем — Закрыть.

Не могли бы помочь с такой закавыкой.
Дано: Excel 2003 и таблица со следующими столбцами:
№ п/п, Наименование, Кол-во, Сумма. Строк более 4000 штук.
Нужно посчитать общие количества и суммы по каждому одноимённому товару (коих дублей множество и вручную неинтересно), поместить суммарные значения в одной из одинаковых строк, а сами строки с лишними дублями удалить (кроме одной разумеется, там где суммарные значения). И таковых групп одноимённых товаров множество (шприцы, перчатки etc.). Хотелось бы также объединить и тождественные по сути(но не по названию) товары, но это, наверное, недостижимо средствами Excel? Заранее признателен.

дано:
Список 1 вида XXXXXXXXXX
Список 2 вида XX-XXXXXXXX 9то есть, отличаются наличием "тире"

Нужно сравнить два списка, выделив недостающие.
Как это можно сделать - Excel 2003 English

Спасибо

renreg
Цитата:

Нужно сравнить два списка, выделив недостающие.

Недостающие где? Нужно найти элементы Списка1, которых нет в Списке2 или найти элементы Списка2, которых нет в Списке1? Какой список состоит из большего числа элементов? Тире всегда находится в третьем знаке? Я правильно понимаю, что XXXXXXXXXX и XX-XXXXXXXX считаются одинаковыми и в результирующий список не попадут?

PegasusUnicorn

Цитата:

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

С A2 начинается список с повторяющимися наименованиями товаров
В столбце B идут значения для позиций столбца А
тогда в С2 вставляем формулу =ЕСЛИ(СЧЁТЕСЛИ(A$2:A2;A2)=1;СУММЕСЛИ(A$2:A$4000;A2;B$2:B$4000);"") и растягиваем её до конца списка.
Делаем автофильтр и по столбцу С делаем фильтрацию по непустым ячейкам.
4000 - количество строк в списке

Что такое тождественные?


Добавлено:
Satyr

Цитата:

На машине WinXP+Office2003
есть файл.xls размером 400МБ. с кучей данных и формул.
при добавлении больше 100 строк в excel изменяется формула в некоторых ячейках, но не во всех.

=ЕСЛИ(E32228="B";0;ЕСЛИ(L32228="ММВБ";0;ЕСЛИ(BL32228="БФГ-Кредит";0;ЕСЛИ(BL32228="РТС";0;ЕСЛИ(BL32228="ВТБ";0;ЕСЛИ(BL32228="БКС";0;ЕСЛИ(E32228 ="DU";0;BG32228)))))))
на
=ЕСЛИ(E32228="B";0;ЕСЛИ(L32228="ММВБ";0;ЕСЛИ(BL32228="БФГ-Кредит";0;ЕСЛИ(BL32228="РТС";0;ЕСЛИ(BL32228="ВТБ";0;ЕСЛИ(BL32228="БКС";0;ЕСЛИ(E64322="DU";0;BG32228)))))))

Почему так происходит не знаю, но для решения предлагаю заменить (Ctrl+H)
ЕСЛИ(E64322="DU";0;BG32228) на ЕСЛИ(E32228="DU";0;BG32228)
идея: выделить часть которая не будет где то ошибочно повторяться и заменить на правильную.

renreg
http://slil.ru/26109157

Добрый день, Господа. Помогите, будьте любезны, составить формулу примерно такого содержания. "X= 100\большее значение*меньшее значение". Суть в том, чтобы автоматом формула распознавала, какие из двух заданых ранее значений цифр больше\меньше одна относительно другой. Возможно ли такое в целом?Спасибо

Эхх))Сам пью, сам гуляю. Уже придумал. Думаю, будет полезно отписаться, раз никто не знает. =100/МАКС(K46:L46)
, где K46:L46 - диапозон значений для определения большего значения.

Pravoved90 =100/(МАКС(A1:B5)*МИН(A1:B5)), A1:B5 - диапазон значений.

Цитата:

Думаю, будет полезно отписаться, раз никто не знает.

Дык это слишком просто чтобы спрашивать. К тому же форум у меня вчера весь день не открывался.

Цитата:

=СУММ((100/МАКС(K46:L46))

- ну и что ты тут суммировать собрался?)))))) СУММ убрать!

Может кому и просто, а мне как уверенному гуманитарию, еще и впервые за Екселем - не очень.
"СУММ убрать!" )) эт я на автомате - там продолжение еще было, но не по теме..))

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

по первому вопросу:
1. Если вписать в ячейки "яблоко", "апельсин", "лимон", и если в следующую ячейку вписать букву "я", "а" или "л" то ексель автоматом предложит выше введенные слова.
2. Можно сделать выпадающий список. Чтоб не писать самому даю сслыку где это уже описано: http://planetaexcel.ru/tip.php?aid=34

по второму вопросу:
=ЕСЛИ(МАКС($A$2:$A$3)<3;МАКС($A$2:$A$3)-МИН($A$2:$A$3)=1)
A2:A3 соответственно диапазон где смотрются макс и мин.
Учись студент Гуманитарий! ;D

Благодарю за ответ. Пошел копаться))..

Большущее спасибо за помощь!
Изящное решение. Впредь буду использовать. Под тождественными по сути (но не по названию) я имел ввиду такие разные и в то же время одинаковые вещи скажем как Анальгин №10 таб. и Анальгии №10 табл.. И т.д. и всяко вариативно и, конечно, не так очевидно как в данном примере. В глобальных отчётах такие нюансы никого не волнуют и хотелось бы свалить в кучу. Но это собственно Excel видимо не по силам?

Цитата:

Но это собственно Excel видимо не по силам?

Ну почему же?
способ 1 (ручной): Делаем многочисленные замены (Ctrl+H), меняем .. на . меняем [пробел][пробел] на [пробел]. Для того чтобы убрать левые пробелы, точки и другие знаки, операцию замены следует повторять до тех пор, пока количество замен не станет раным нулю. Так же можно убрать все запятые или точки заменив их на [ничто]. Если есть №10 и № 10, то пробел можно убрать, заменив №[пробел] на №. Можно заменить Табл на таб и тд
Этот способ так же относится и к ворду, там даже больше возможностей для замен.

способ 2 (формулы): Многого, думаю, можно добиться одной лишь формулой =ПОДСТАВИТЬ(), как ей пользоваться изложил в примере на первой странице ZORRO2005. Вот эта формула =ПСТР(A1;1;НАЙТИ(" ";A1)) поможет выделить первое слово, а в таком варианте =ПСТР(A1;1;1) первую букву и тд. В экселе целый блок формул которые работают с текстом - посмотри их, возможностей много.
Из полученных значений можно получать группы, к примеру, создать группу таблеток, можно создать группу номеров или группы по алфавиту, а потом фильтроваться по нужным группам через автофильтр и получать нужное.

Вопчем изобретательность спасет отца русской демакратии фармакологиии ;D

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

Pravoved90 все дело в $

Понял. Доллары - долой)). Благодарю

Добавлено:
Снова здравствуйте. Продолжаю познавать "прелести" Екселя)). Вобщем такая проблема:
Есть формула:
=((E41-I41)*(100/МАКС(E41;I41)))
Мне нужно сделать так, чтобы в случае, если МАКС(E41;I41)=0, значение равнялось 0

Pravoved90
Пишу без Excel, но думаю, что ошибок в формуле не будет.
=ЕСЛИ(МАКС(E41;I41)=0;0;(E41-I41)*100/МАКС(E41;I41))

Благодарю. Так и есть

Добавлено:
Добрый вечер. Я уже сам как формула)) Есть уровнение
=H30+R30<3
Как записать, что оно действует только в случае, если H30 или R30 не =2
Заранее благодарю за ответ.


Добавлено:
Нашел.Вроде так:
=ЕСЛИ(D39+N39<3;МАКС(D39;N39)<>2)

Pravoved90
В Excel нет в формулах уравнений.

Цитата:

=ЕСЛИ(D39+N39<3;МАКС(D39;N39)<>2)

Эта формула говорит следующее.
Если D39+N39<3, то
вернуть истину, если МАКС(D39;N39)<>2, и
вернуть ЛОЖЬ, если МАКС(D39;N39)=2
Если D39+N39>=3, то вообще неопределено, что возвращать.
Если тебя такая формула устраивает, то это твоя формула.

Нужна помощь с двумя элементарными действиями в экселе
есть 3 столбца, нужно первый разделить на второй, и чтобы результат деления вышел в третьем, а дальше упорядочить все строки по третьему столбцу по убыванию числового результата.
Т.е. к примеру нужно поделить ячейку B1 на C1, и результат этого деления в D1, дальше B2, C2 и D2 тоже самое соответственно и так дальше, потом по колонке D сделать упорядочивание.
Это вообще можно сделать в экселе?

Lem
Цитата:

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

Да. Действительно элементарные действия, что даже непривычно и писать про такое.
1) В ячейке D1 набираешь = затем тыкаешь мышкой в B1, набираешь / затем тыкаешь мышкой в С1 Нашимаешь Enter.
В результате в у тебя D1=B1/C1
2) Затем тыкаешь мышкой в D1. У тебя в правом нижнем углу прямоуголника маленький черный квадратик. Подводишь в нему мышку до появления крестика. Удерживая крестик левой кнопкой мыши идешь вниз. Формула автоматически разгонится на нижестоящие ячейки.
3) Выделяешь все три столбца или просто все свои ячейки с цифрами.
Меню: Данные -> Сортировка -> По столбцу D

Цитата:

Если тебя такая формула устраивает, то это твоя формула.

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