» Excel FAQ (часть 4)

q1wed

Цитата:

по условному форматированию:

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

Добрый день, народ помогите!!!
Случай не очень тяжелый, но может кто поможет.
Есть таблица, на рисунки видно два стоблца ФИО и МЕСТО РАБОТЫ, всегда постоянны. Задача из столбца "МЕСТО РАБОТЫ" вытащить слово и вставить его в столбец "ПОИСК" а в столбце "№" подсчитать сколько раз именно такого слово встерчалась в столбце "МЕСТО РАБОТЫ". НО главная цель не искать и сравнивать ячейки, а именно слова из ячеек. кто поможет? очень надо..

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

примечания.
1) четвёртый столбец лучше назвать не "№" - а "Количество вхождений"
2) для слова "Компания" количество вхождения должно быть 2

Всем добрый день. Подскажите, как сделать следующее: Есть столбик ячеек А1:A30. В ячейках цифровые значения. Некоторые из них залиты в красный цвет. Необходимо найти Среднее значение по всем ячейкам, но чтобы закрашенные ячейки в подсчете не участвовали. Можно ли это сделать? Благодарю.

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

Код: Public Function ЦветЗаливка(Ячейка As Object) As Integer
Application.Volatile True
ЦветЗаливка = Ячейка.Interior.ColorIndex
End Function

Troll
Вариант на элегантность не претендует, но...
Только формулами и в Excel 2007
http://webfile.ru/3781973

Приветствую всех!

Опыта работы в Excel 2003 практически не имею (приходилось програмировать в Dbase4). Получил две базы данных (прайса) и потребовалось их сличить. Структура:
№    Код    Артикул    Номенклатура    Ед.изм.    Признак продвижения    Наличие    Оптовая цена - (база 1)
N    Код    Артику    Номенклатура    Ед.    Цена -(база 2)

Разное количество записей (строк) и различный порядок из следования не позволяют просто скопировать колонку "Цена" из одной базы в другую.

Предполагаю что требуется:
1. скопировать лист базы 2 в книгу базы 1
2. соединить базы по колонке "Код" путем переноса колонки "Цена" из второй базы в первую...
3. сделать пометку в базе 2, мол, запись скопирована в базу 1 (чтоб выявить те, что нескопированы)...

Можно ли это реализовать без применения VBA? Если да, то - как это сделать? Если нет, то - что лучше (перенесли все в dbf или изучить VBA)?

C уважением, Александр.

AleXPwin
Что есть уникальное значение? Код или артикул? Таблицы отсортированы по этому уникальному значению? Пункт 1 возможно лишний, если функция ВПР сможет работать с внешним файлом.
Читай описание функции ВПР и особо обрати на послений ее параметр. Он завязан на, есть ли или нет сортировка в таблице.

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

Здравствуйте.
У меня есть вопрос: есть прайс в нём колонка с ценой. Как можно цены к данной колонке увеличить например на 20%. Использую Microsoft Office Exel 2007. Заранее благодарен за помощь.

Orphen
Цену умножить на 1,2.

Цитата:

Цену умножить на 1,2.

А не могли бы вы мне написать формулу. И как её использовать. С Excel практически незнаком.

Orphen
в ячейке B находятся цены.
1) В соседней колонке написать С1=B1*1,2
2) затем заменить формулу на значения (Меню Правка -> Специальная вставка -> Значения.
3) Затем переместить числа из столбца C в столбец B.

Как это будет выглядеть в 2007 Excel я не знаю.

Orphen
к сожалению, не работал с Office2007, поэтому буду писать для 2003-го.
главное, идею схватить, а где уже меню в Office 2007 Вы уже сами поищите...

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

шаг 2. выделяем эту ячейку и нажимаем Ctrl-Ins (вокруг ячейки должна появится анимированная рамочка)

шаг 3. выделяете диапазон с ценами. (анимированная рамочка должна остаться на вашей ячейке с 1.2 )

шаг 4. правой кнопкой мышки на выделенном диапазоне и ищите пункт "Специальная вставка". нашли выбираете его.

шаг 5. в открывшемся окошке ставим птичку на "значения" и операция "умножить"
желательно выбрать так же пункт "пропускать пустые ячейки"
нажимайте ОК.

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

Mushroomer
SERGE_BLIZNUK
Спасибо. Помогло.

Mushroomer
SERGE_BLIZNUK

Благодарю за отзывчивость и оперативность. Судя по всему, уникальное значение - Код. Хотя неплохо было бы проверить эту догадку (если конечно Excel позволяет это сделать штатными средствами). Сортировка меняется, то по "Номенклатура", то по №.
Буду пробовать, методом научного тыка функцию ВПР().


Цитата:

(можно и в первой показывать данные из второй таблице, можно и во второй показывать данные из первой)

Да... думается так даже интересней получится. Еще бы понять как это реализовать...

С уважением, Александр.

PS В таблицах около 16000 и 12000 записей.

Цитата:

Судя по всему, уникальное значение - Код. Хотя неплохо было бы проверить эту догадку (если конечно Excel позволяет это сделать штатными средствами).

Вы даже не представляете, как многое можно добиться в Excel "штатными средствами"!!!
Особенно, если к ним отнести функции/макросы встроенном VBA... :)

ладно. конкретно. проверить уникальность поля Код можно

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

но я рекомендую Вам способ b)
b) можно воспользоваться условным форматированием и подсветить неуникальные записи. для этого становимся на первую запись в столбце Код
нажимаем меню Формат -> Условное форматирование - формула
=СЧЁТЕСЛИ($A$2:$A$200;A2)>1
(здесь A2 - это текущая ячейка с кодом. A2:A200 - это ПОЛНЫЙ диапазон с кодами)нажимаем кнопочку формат. выбираем вид и цвет заливки (например, красный).
ОК.


Становимся на ячейку A2, дальше нажимаем кнопочку "формат по образцу" и выделяем
весь диапазон (в нашем случае A2:A200).
всё. неуникальные будут подсвечены красным.

SERGE_BLIZNUK

Цитата:

Вы даже не представляете, как многое можно добиться в Excel "штатными средствами"!!!
Особенно, если к ним отнести функции/макросы встроенном VBA...

Действительно


Цитата:

... но я рекомендую Вам способ b) ...

Подтвердилось предположение об уникальности данных в колонке "Код" (если не просмотрел конечно, т.к. пришлось листать ооочень долго одну из баз).

TDSheet (Чел) - база 1, TDSheet (Пермь) - база 2. Колонки Код в обеих базах переставил в крайний левый ряд.
К2=ВПР(A2;'TDSheet (Пермь)'!$A$2:$F$11628;6;ЛОЖЬ) - не работает. Возможно чтото не так понимаю? Поправьте пожалуйста.

С уважением, Александр.

PS Есть ли кнопочка подобная "формат по образцу", только для копирования формулы?
Обнаружил однократное дублирование некоторых строк в одной из баз (включая код), с одним лишь изменением - порядковый номер (№) стоит следующим (остальные колонки содержат идентичные данные с предыдущей строкой). Как удалить дубликаты?

Добрый день всем.

У меня два вопроса.

1. Как снять общий доступ к книге. Открываю Сервис - Доступ к книге... В окне Управление доступом к файлу на вкладке Правка стоит галочка "Разрешить изменять файл..." , но она не активна.

2. В книге1 на листе3 есть формула, которая суммирует ячейки А1 на листе1 и листе2. Если копировать эту формулу в книгу2, то копируется полный путь, т.е.
='[книга1.xls]лист1'!A1+'[книга1.xls]лист2'!A1
Можно ли как-то избежать этого, т.е. чтобы копировалась только формула.

Добавлено:
Дополнение.

Имеется в виду: =лист1!A1+лист2!A1

Смешно, но про сумму прописью.
Нужна на эстонском.
Эстонского не знаю ваааще. Не только граматики (тысяча, тысячи, тысяч),
даже ОДИН не знаю как. Документ срисовал один в один, а с суммой никак
Может у кого есть готовое ?

Цитата:

1. Как снять общий доступ к книге. Открываю Сервис - Доступ к книге... В окне Управление доступом к файлу на вкладке Правка стоит галочка "Разрешить изменять файл..." , но она не активна.

C этим я разобрался - надо было сначала снять защиту с общей книги, тогда все получается.

А вот второй вопрос по-прежнему актуален. Для простоты я привел пример с двумя листами. В реальной ситуации ячеек и листов гораздо больше. И удалять ссылки на исходную книгу в каждой ячейке очень муторное дело.

Помогите, пожалуйста.

Mitjusha
можно выделить весь диапазон и через меню Поиск-Замена удалить лишнее (заменить часть формулы [книга1.xls]), а можно изменить связи (меню Правка/связи - найти на диске текущую книгу.

Цитата:

можно выделить весь диапазон и через меню Поиск-Замена удалить лишнее (заменить часть формулы [книга1.xls])

Это понятно, но это корректировка после копирования. А чтоб сразу обрубались связи нельзя?

SERGE_BLIZNUK

Цитата:

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

Код:
Public Function ЦветЗаливка(Ячейка As Object) As Integer
Application.Volatile True
ЦветЗаливка = Ячейка.Interior.ColorIndex
End Function

и тогда в можно использовать это в формулах, например:
=ЕСЛИ(ЦветЗаливка(A1)=6;1;0)

Большое спасибо за ответ..Цвет задается условным форматированием. Только не понял, куда нужно записать эту формулу в моем случае - в конечную ячейку, или в каждую составляющую диапазона?..

Pravoved90

Если цвет задаётся условным форматированием, то в своих расчётах Вам надо
учитывать именно условия из условного форматирования!! функция возрата цвета ячейки в данном случае Вам никак не поможет.

ru-boаrd с утра был недоступен, пришлось продублировать сообщение на дружественном форуме, но все-таки: Подскажите, пожалуйста, есть необходимость сделать таблицу по задолженностям клиентов с учетом отсрочки. Ума не приложу, как это сделать и с какой стороны подойти. Может, у кого-нибудь есть наработки по данному вопросу или даже готовый шаблон? Был бы очень признателен за любую помощь по данной теме.

1nasty1
чтобы понять что нужно сделать, нужен пример или хотя бы разъяснить понятие отсрочки и показать какие есть параметры по датам у задолженностей.

Mushroomer
Спасибо.

Сложно, конечно, даже объяснить, честно... Вроде смысл ясен, но в екселе с чего начать... непонятно. Смысл такой: человек начинает брать товар, и только по истечении какого-то срока (например, 15 дней) считается, что он должен за него рассчитаться.
Если в промежутке списывались какие-либо оплаты, они отнимаются от суммы, полученной на момент отсрочки. Например, у меня отсрочка 5 дней: 1.07 взял 400, 02.07 - 200, 03.07 - 300 взял, 400 оплатил, 05.07 - оплатил еще 100, 06.07 - взял 300... По итогу на 10.07 моя задолженность составляет (400+200+300-400 (опл.)-100 (опл.) = 400, которую я должен погасить. 06.07 не считаем, т.к. срок не подошел, а вот уже 11.07 уже сумма долга увеличится на 300. В таблице графы: "клиент", "общая задолженность", "задолженность по отсрочке" и графы "приход" - "расход" с числами месяца". Надо как-то извернуться, чтобы формула считала только нужное количество дней. Например, сегодня 23.07, значит с учетом 5-дн. отсрочки нужно считать задолженность до 18.07, но учитывая оплаты уже до 23.07 (т.к. человек рассчитывается до последнего за предыдущее). Короче, если переварите все, что я написал, может появится представление. Сделать непросто без наработок, хотя вещь очень нужная в торговле. Можно делать и в 1С (договор по оплатам), но ОЧЕНЬ нужна наглядность, а впоследствии будет модифицироваться еще. Очень непростая задача.


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

Добавлено:
такая вот задачка...

1nasty1
Я задачу понял, но решать не возьмусь. Не мой профиль. Сначала надо решить как отображать дни закупи и оплаты. Это будут отдельные строки с той же фамилией поставщика?

задача сводится к подсчёту суммы задолжности за определённый период. В разрезе должников. Удобно это сделать сводной таблицей. Взятые и погашенные суммы по-любому будет забивать в комп оператор. Почему бы ему не забить и суммы штрафов за просроченные погашения? Сделать ему подсказки в ввиде условного форматирования по датам. И будет он загонять 3 поля данных: ФИО; дата; сумма (положительная - взял, отрицательная - вернул. Начисленный штраф - тоже положительный). А нагляднее сводной таблицы по-моему в ёкселе ещё ничего не придумали