Сообщество - MS, Libreoffice & Google docs

MS, Libreoffice & Google docs

762 поста 14 934 подписчика

Популярные теги в сообществе:

11

Прошу помощи с Excel. Последнее значение в столбце по критериям

Уважаемые пикабушники! В очередной раз обращаюсь с просьбой о помощи с Excel.
Вопрос следующий. Есть 2 столбца. В столбце 1 увеличиваясь показано время в часах, во втором - время в минутах к этим часам.То есть, в столбце 1 увеличиваются часы, во втором циклически от 0 до 59 бегут минуты. Примерно так:
1-01
1-02
1-03
...
1-59
2-00
2-01
Формат столбцов - числовой, не время. В столбце 1 часов не может быть больше 5000 и меньше 0. В последних строках таблицы может быть "мусор", поэтому критерий 0><5000 важен. Подскажите пожалуйста, как забрать в ячейку последнее (именно последнее) значение из столбца "часы", а в соседнюю ячейку - значение минут из ячейки столбца "минуты" в той же строке, из которой взяли значение часа.
Значения часов и минут через строку идут с пробелами. Я пробовал ПРОСМОТР - в нем нельзя задать условие от 0 до 5000. С ИНДЕКС тоже ничего не получилось.
Еще способы втыкаются в наличие в столбцах пустых ячеек.
Подскажите способ как это сделать? Любой коммент плюсую, комменты для минусов оставляю и заранее благодарю.

Показать полностью
9

Результат на основе двух совпадений (решение найдено. Cann1bal красавчик)

Ребята, всем доброго дня.  Понимаю, что для вас, профессионалов, эта задача будет проще паренной репы, но всю голову себе сломал. Помогите с формулой для великого и могучего Exel.

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

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

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

Куски листов для понимания. Всем заранее спасибо.

Показать полностью 2
12

Подскажите, как обогатить данными столбец данными другого

Задача заключается, в том что необходимо данные (дата) обогатить данными другого столбца, при этом, столбец 1 и 2 находятся на разных листах, пример:


Столбец 1

10.02.22

10.02.22

10.02.22

12.02.22

15.02.22

17.02.22

17.02.22

01.12.22


Столбец 2

11.02.22

16.06.22

16.06.22

16.06.22

11.11.22


Столбец 3 (должен выглядеть так)

10.02.22

10.02.22

10.02.22

11.02.22

12.02.22

15.02.22

17.02.22

17.02.22

16.06.22

16.06.22

16.06.22

11.11.22

01.12.22


Здесь видим, что взят за основу Столбец 1 со всеми повторами и обогащен данными из Столбца 2, при этом, если дата в Столбце 2 присутствует в Столбце 1, то она не добавляется.


Применять формулу буду в Google Таблицах.

Показать полностью
93

Весёлые маркеры графиков

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

Установка рисунков в качестве маркеров позволяет разнообразить внешний вид документации, сделав её нагляднее. Установка смайлов (© http://www.kolobok.us/ ) сделана в качестве примера (помните про Aiwan то? Или забыли...).

Для гармоничного отображения требуется проредить количество маркеров, в противном случае произойдёт наложение рисунков друг на друга. О прореживании писал ранее.

Заменить маркеры на рисунки, в данном случае они представлены смайлами, можно при помощи не сложного макроса


Sub Markers_Smiles()

ActiveSheet.ChartObjects("Диаграмма 1").Activate

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(1).Points(icell.Row - 1).Select

' Убираю рамки вокруг маркеров

Selection.MarkerForegroundColorIndex = xlNone

' Установка типа маркера «Рисунок»

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture "D:\4.gif"

If icell.Value = 0 Then Selection.Format.Fill.UserPicture "D:\1.gif"

If icell.Value = 1 Then Selection.Format.Fill.UserPicture "D:\2.gif"

If icell.Value = 2 Then Selection.Format.Fill.UserPicture "D:\3.gif"

Next

End Sub


Где

[C2:C102] - столбец с признаками маркера. Число элементов равно числу данных (Х или Y). Может как заполняться вручную, так и быть расчётным (см.рисунок ниже).

D:\1.gif ... D:\4.gif - пути к рисункам.


Аналогично производится заполнение рисунками нескольких графиков на диаграмме

Прореживаем


Sub Прореживание_маркеров()

' Активируем диаграмму

ActiveSheet.ChartObjects("Диаграмма 1").Activate

' Перебор по всем графикам диаграммы

For k = 1 To ActiveChart.FullSeriesCollection.Count

' Удаляем все маркеры на линии

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count

ActiveChart.FullSeriesCollection(k).Points(i).Select

Selection.MarkerStyle = -4142

Next i

' Выставляем маркеры с требуемым шагом.

For i = 1 To ActiveChart.SeriesCollection(k).Points.Count Step 4

ActiveChart.FullSeriesCollection(k).Points(i).Select

With Selection

.MarkerStyle = 8

.MarkerSize = 15

End With

Next i

Next k

End Sub


Проставляем рисунки


Public Sub color_graph()

ActiveSheet.ChartObjects("Диаграмма 1").Activate

For k = 1 To ActiveChart.FullSeriesCollection.Count ' Перебор по всем графикам

For Each icell In [C2:C102]

ActiveChart.FullSeriesCollection(k).Points(icell.Row - 1).Select

Selection.MarkerStyle = -4147

Selection.Format.Fill.UserPicture "D:\4.gif"

If icell.Value = 0 Then Selection.Format.Fill.UserPicture "D:\1.gif"

If icell.Value = 1 Then Selection.Format.Fill.UserPicture "D:\2.gif"

If icell.Value = 2 Then Selection.Format.Fill.UserPicture "D:\3.gif"

Next

Next k

End Sub


Аналогично разным графикам одной диаграммы можно присвоить уникальные маркеры

Sub Markers()

ActiveSheet.ChartObjects("Диаграмма 1").Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ' Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.MarkerForegroundColorIndex = xlNone

Selection.MarkerStyle = -4147

If i = 1 Then Selection.Format.Fill.UserPicture "D:\1.gif"

If i = 2 Then Selection.Format.Fill.UserPicture "D:\2.gif"

If i = 3 Then Selection.Format.Fill.UserPicture "D:\3.gif"

If i = 4 Then Selection.Format.Fill.UserPicture "D:\4.gif"

If i = 5 Then Selection.Format.Fill.UserPicture "D:\5.gif"

Next i

End Sub


Ну или просто разными штатными маркерами разные графики. Но в автоматическом режиме - очень сокращает время подготовки документации. Полезно при подготовке к печати в чёрно-белом варианте.

Sub Установка_разных_маркеров()

ActiveSheet.ChartObjects("Диаграмма 1").Activate

For i = 1 To ActiveChart.FullSeriesCollection.Count ' Перебор по всем графикам

ActiveChart.FullSeriesCollection(i).Select

Selection.Format.Line.ForeColor.RGB = RGB(0, 0, 0) ' Цвета линий и маркера

Selection.Format.Line.Weight = 0.75 ' Установка толщины линии

Selection.MarkerStyle = i ' Установка типа маркера

Selection.MarkerSize = 4 ' Установка размера маркера

Selection.Format.Fill.ForeColor.RGB = RGB(255, 255, 255) ' Установка заливки маркера

Next i

End Sub


Можно ли это сделать без макросов? Несомненно. Долго и нудно кликать кнопочки.

Но как по мне - проще скопировать и немного поправить код простого макроса. А в остальном - ваш выбор.

Показать полностью 5
20

Вопрос по функциям Excel

Добрый день, уважаемые эксельЕ!
Провожу собеседования на вакансию, основное требование - хорошее знание Excel.
В процессе собеседования предлагаю соискателю решить следующую задачку:

Вопрос по функциям Excel

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

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

Показать полностью
19

Excel. Копирование диапазона в другой файл

Всем привет!

Нужна ваша помощь.


Есть такая задача:

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


Sub ExportRangetoExcel()
'Update 20130916
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
Dim address As String
Dim defult As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
defult = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = defult
WorkRng.Copy
wb.Worksheets(1).Paste
address = Replace(WorkRng.address, ":", "-")
address = Replace(address, "$", "")
address = Replace(address, ".", "")
saveFile = Application.GetSaveAsFilename(InitialFileName:=address, fileFilter:="Excel Workbooks (*.xlsx),*.xlsx")
wb.SaveAs Filename:=saveFile
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Он делает именно то, что нужно, но не сохраняет форматироание.

Нашёл метод Range.PasteSpecial, но как его здесь правильно применить так и не разобрался.


Вопрос к знатокам VBA: что сделать, чтобы макрос вставлял выделенный диапазон с сохранением форматирования?

Показать полностью
73

Обновление страницы в Эксель

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


ФОТО:

Это визуальный баг. Забагованы 7 столбцов, почему-то съехали. Если опустить  скролл ниже забагованных ячеек, что бы они пропали из зоны видимости и вернуться назад, всё восстанавливается.

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


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

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

Upd: решено спустя 5 минут после опубликования. Спасибо @robokot0 за оперативность и рабочий код!

Показать полностью 3
355

Ответ на пост «Нужен макрос для массовой печати документов из Word 2010 по порядку»1

Итак, получилось сделать скрипт на PowerShell для массовой печати файлов.

Сразу отвечу комментаторам, задававшим вопросы по типу: "Зачем это надо?"

Я работаю в госучреждении, мы печатаем уйму документов по различным вопросам (Да, эта бумажная волокита в 21 веке напрягает, но не во всех сферах у нас введен электронный документооборот) В одной папке может быть до 500 штук документов по типу: Заявление1, Расшифровка1, Заявление2, Расшифровка2 и так далее. Если попробовать печатать через контекстное меню Проводника, то:

1. Они печатаются вперемешку. И потом нужно к каждому заявлению найти расшифровку. Когда у тебя 500 документов, это просто адский геморрой. А запускать печать по одному файлу тоже влечет за собой затраты времени

2. Максимальное ограничение при печати из контекстного меню - 15 файлов (да, я знаю, что можно отредактировать значение в реестре, но см. пункт 1).

3. В организации запрещены сторонние программы, поэтому PrintConductor не получится установить, к великому сожалению. Насчет гугла и всего прочего немного непонятно, так как тут не все программисты и даже простой скрипт PowerShell у меня вызвал определенные трудности, но в итоге все получилось. Иногда задачу нужно решить как можно быстрее и нет времени учиться писать макросы, скрипты и все прочее и изучать профильные сайты. Но было интересно, хоть и с примерами, но пробовать что-то новое. В общем, вот скрипт:


Add-Type -AssemblyName System.Windows.Forms | Out-Null

$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog

$OpenFileDialog.InitialDirectory = "C:\"

$OpenFileDialog.Multiselect = $True

$OpenFileDialog.Filter = "Все файлы (*.*)|*.*"

$OpenFileDialog.ShowHelp = $true

$OpenFileDialog.ShowDialog() | Out-Null

$FilesToPrint = $OpenFileDialog.FileNames | Sort-Object

If (!($FilesToPrint)) {

Break

}

ForEach ($FullFileName in $FilesToPrint) {

Write-Output "Печать файла `"$FullFileName`""

Start-Process -FilePath $FullFileName -Verb Print -Wait

}


1. Сохраняем его в текстовый файл, меняем расширение у текстового файла на .ps1 и пробуем запустить.

2. Скорее всего он не запустится из-за ограничений в Windows, связанных с запуском скрипта.

Для решения создаем ярлык на файл скрипта и в Свойствах ярлыка в Поле Объект пишем:

powershell -ExecutionPolicy Bypass -file "Путь до скрипта.ps1"

3. После этого кликаем по ярлыку и скрипт запросит папку для печати.

4. Открываем папку, выделяем нужные файлы (у меня .rtf и .docx) и отправляем на печать.

5. Скрипт печатает их в алфавитном порядке.

Спасибо всем за идею и наводки на решение! Надеюсь, кому-нибудь данный скрипт будет полезен.

Показать полностью
Отличная работа, все прочитано!