Essentially everyone in the film "Real Genius", which could almost be the trope namer.
The Man With Two Brains. Dr. Hfuhruhurr literally wears them going into surgery. It's a practical joke, but it's a Visual Pun as you discover that is exactly the kind of doctor he is. C'mon, folks - screw-top brain surgery?
Kuryu, the main character of Japanese movie Hero (based on the TV series, below) is almost literally a Bunny Ears Lawyer. He constantly wears almost-aggressively casual clothes while his contemporaries wear suits, he indiscriminately buys random items from the shopping channel, and spends the whole movie trying to learn Spanish simply because he inadvertently ordered a book in that language. His quirks are overlooked however, partly because he is a cunning and successful lawyer, but mostly because his co-workers are all subtly quirky too.
Glen Whitman, in Transformers, is a brilliant computer hacker, who may have ADHD and exhibits extremely erratic, perhaps sociopathic behavior. Apart from a lack of basic manners and some sort of unspecified paranoia, his great passions in life appear to be video games and getting into places he does not belong. Which could honestly be said of 90% of computer hackers. Hacking is, after all, all about getting into places you don't belong.
Agent Seymour Simmons, who the TF Wiki describes as "just a smidge off his nut", but who is very good at what he does. In a bizarre subversion of this trope, his Bunny Ears-ness is implied in the novel The Veiled Threat to be the reason he isn't working for NEST.
Ace Ventura: Pet Detective is downright certifiably insane (manic episodes and schizophrenia at the least). He is also very good with animals, a highly observant and capable investigator, a decent actor, ace driver, love-machine par excellence and no slouch in a fight.
In Legally Blonde 2: Blonde Hardernote not its real title, her awesome knowledge of fashion allows her to root out political shenanigans on Capitol Hill.
Stéphane from The Science of Sleep is completely... mad, possibly... due to his confusing dreams that keep melding with reality (that he's not entirely about to cope with). However he is a technical genius. He makes a toy horse gallop with robotic parts - rather realistically and possibly a one-second time machine. His "Disaterology" also becomes a big success.
Dr. Strangelove is a brilliant former Nazi with a severe case of alien hand syndrome - his right hand gives the Seig Heil salute without his control, and takes extreme effort to force back into his lap, and occasionally attempts to strangle him. Among other things.
This quirk of his is so well-known that alien hand syndrome is also known as Dr. Strangelove syndrome.
Martin Riggs from the Lethal Weapon series is suicidal in the first film, and then just plain crazy after he gets over it. He deliberately plays havoc with the department's psychiatrist (and starts to make her snap), for bonus points. His near unstoppability when dealing with thugs is likely the only reason he is left on the force.
Sherlock Holmes is a brilliant detective, but he is also quite eccentric as well as having a recurring cocaine dependency.
John Mason (Sean Connery) from The Rock is a good example. Despite being considered one of the most dangerous men alive, the US government is essentially ready to give him anything he wants in order to get him to work, because he escaped Alcatraz and lived to tell about it.
The pilots in Air America are completely insane, but really good pilots.
Subverted in Smokin' Aces. There is an eccentricly sleazy lawyer. For his first scene, there is literally a costume rabbit head showing in the background. However, he proves to be completely incompetent.
The detective in Laura always plays with a handheld maze game.
Vinny in My Cousin Vinny is an acerbic Brooklyn stereotype with a mouth like a stevedore(being played by Joe Pesci, this is to be expected), but turns out to be a brilliant and tenacious opponent in the courtroom. Also, his girlfriend Lisa, an aspiring hairdresser who knows enough about cars that her testimony ends up proving the defendants' innocence.
Willie Beamen in Any Given Sunday had the habit of throwing-up at least once every match. However, he's a reasonably competent quarterback that he took over as the starting QB when the previous one was injured.
In the 2011 film Warrior: Frank Campana is known for his unusual training methods involving classical music, but damn if he gets results.
Lampshaded early in Top Gun when Air Boss Johnson chews out Maverick and Goose for the umpteenth time. Maverick's lost his qualifications as section leader several times, and has a habit of making high-speed passes by control towers and atan admiral's daughter. About the only reason he hasn't been kicked out of the Navy altogether is that he's a damn good pilot.
Jimmy from Timecop is lucky he knows how to use a computer for more than just watching virtual reality porn. An alternate timeline shows us a much more serious Jimmy with no such vice.
Ahnuld's buddy Hank in The 6th Day is a goofball who lives with a robotic prostitute, but he takes his job as a helicopter pilot seriously and is more than happy to cover for Ahnuld on his birthday. He gets killed for his troubles.
Daniel Rafferty in the film Laws Of Attraction. When we first see him, he's dozing in court, is scruffy, unkempt, and as we later see, works out of a ramshackle office in a rundown area of Chinatown, despite the fact that he could clearly afford better, as evidenced by his very nice apartment. But within two seconds of meeting uber-uptight attorney Audrey Woods, he wipes the floor with her and it's mentioned that he's never lost a case.
Down Periscope is all about figuring out that this trope applies even in the navy.
Commander Dodge is famous throughout the Navy for his cavalier attitude to command and his immature antics (not to mention his penis tattoo) but he is also a prime example of immature not meaning irresponsible. His goofball antics never affect his performance on the job in spite of being less than professional, he is well-liked by his subordinates, and he manages to whip a World War Two-era rustbucket of a submarine and a Rag Tag Bunch Of Misfits into an effective fighting force over the course of a Montage.
Sonar, the Sonar technician has hearing so good that he's been considered a security threat. He can count money just by hearing it drop on the floor. Also, he's studying the language of whales. And he's a bit off. Nonetheless, he saves his crew from peril with all those abilities.
Nitro, the electrician and radio operator, is more than a bit off because he's absorbed so much electricity. Nonetheless, he's probably the only one capable of making the electrics on the Stingray actually work.
Even the Stingray, a World War II relic diesel submarine could count, because it's the only sub that could manage to sneak into Norfolk.
"Oddball" from Kelly's Heroes has a bazillion and nine strange quirks, not least of which is an obsession with positive thinking, but still manages to be a highly skilled tank commander.
Detectives Gavilan and Calden in Hollywood Homicide. Gavilan is a Deadpan Snarker who moonlights as a real estate broker and is being investigated for financial improprieties by Internal Affairs. Calden is an aspiring actor who fancies himself a Warrior Poet. In the film's climax, they manage to solve their case and catch the bad guys in a wild Chase Scene, while indulging their side interests at the same time.
In Ra One, Shekar, who is quite good at his job of game designing, but incredibly spacey—to the extent that no one seems to notice when he is replaced with a robotic double incapable of comprehending sarcasm.
Dr. Newton 'Newt' Geiszler from Pacific Rim. He's loud and eccentric to say the least. If he wasn't such a competent Kaiju biologist, he'd probably get checked into a mental hospital due to his borderline-obsession with the things.