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.
We can't forget 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.
Let's not forget, she also legitimately got into Harvard law based on her academic record and testing.
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.
Actually, in the first film, the chief believes Riggs is faking it to get an early pension, Catch 22 style, only the psychiatrist and later Murtaugh believe he's crazy.
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 has one last chance to show that his unconventional leadership (not to mention his penis tattoo) shouldn't disqualify him from commanding a nuclear submarine, because despite his flaws, he is an effective commander. He is given a crew of misfits that the Navy doesn't want, but by the end he decides he couldn't possibly live without.
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.
In The Lone Ranger Tonto is seen as this by his fellow Comanches due to his strange costume and dead-crow headdress, which have no basis in historical Comanche clothing.